SQL INSERT with select

Question

I have two tables t1 and t2 basically, t1 contains code 3299 against ind_ref 86034 where t2 is missing that code. I want to do an insert in t2 and get the code 3299 from t1 .

here is t1

IND_REF Code
86034   3299

here is t2

IND_REF Code
86034   1212

I want to see the output as

IND_REF Code
86034   1212
86034   3299 <-- as insert from t1

How can i achieve this,

Here is my query, but it doesnt update.

INSERT INTO test.DBO.ATTRIBUTE (ATTR_CODE_REF)


select ((SELECT att.ATTR_CODE_REF 
                                FROM individual ind
                                join contact c on c.individual_ref=ind.individual_ref
                                join organisation org on org.organisation_Ref=c.ORGANISATION_REF and c.main_organisation='y' and c.valid_to is null  --contact_ref
                                join attribute att on att.organisation_ref=org.organisation_ref and att.code_type=3299
                             where iND.individual_ref=86034))--@indref) 
                              from ATTRIBUTE                                                        
            WHERE ATTRIBUTE.INDIVIDUAL_REF=86034

So, can you describe more detail in your current table structure and also paste some sample data and your expect output? — Frank AK
– Frank AK, Commented Oct 23, 2018 at 9:46

D-Shih · Accepted Answer · 2018-10-23 09:57:09Z

2

You can try to use insert into .... select with NOT exists.

insert into data in t2, which missing Code from t1.

insert into t2 (IND_REF,Code) 
SELECT IND_REF,Code 
FROM t1 
WHERE NOT exists
(
    SELECT IND_REF,Code
    FROM t2
    where t1.Code = t2.Code
)

sqlfiddle

answered Oct 23, 2018 at 9:57

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2018-10-23 09:48:14Z

1

--You can use insert based on select

INSERT INTO t2 (IND_REF ,Code)
SELECT IND_REF ,Code
FROM t2 
WHERE IND_REF = 86034

edited Oct 23, 2018 at 9:48

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

answered Oct 23, 2018 at 9:47

Safaa Abd El Hamid

1146 bronze badges

Comments

Maadesh Sivakumar · Accepted Answer · 2018-10-23 09:49:57Z

1

Use Union All Operator to vertically combine two tables of same no of columns:

SELECT [IND_REF],[CODE] FROM t1 WHERE [IND_REF]=86034
UNION ALL
SELECT [IND_REF],[CODE] FROM t2 WHERE [IND_REF]=86034

edited Oct 23, 2018 at 9:49

answered Oct 23, 2018 at 9:44

Maadesh Sivakumar

3676 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL INSERT with select

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related