CODEWITHSUNDEEP
sqlsql-servert-sql
Biswa
Biswa

Reputation: 331

SQL INSERT with select

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

Upvotes: 0

Views: 79

Answers (3)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 2

Maadesh Sivakumar
Maadesh Sivakumar

Reputation: 367

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

Upvotes: 1

Safaa Abd El Hamid
Safaa Abd El Hamid

Reputation: 114

--You can use insert based on select 

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

Upvotes: 1

Related Questions