How to do Insert/Update by comparison

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to do Insert/Update by comparison

Author

Topic

abhwhiz
Starting Member

37 Posts

Posted - 2007-08-22 : 08:19:44

I have two tables.

CREATE TABLE TEMP
(
KEYNAME VARCHAR(256),
LOCALE VARCHAR (20),
VALUE ntext
)
go

CREATE TABLE keys_1
(
KEYNAME VARCHAR(256),
LOCALE VARCHAR (20),
VALUE ntext
)
go

What I need is i need to update the second table with the freshly loaded data in the first table. but if the data is already present in second table i only need to update the existing rows, if it is not there i have to insert the rows. i am not able to think of anything. Can someone help? i used except and minus, i am able to see the difference. But after that i am not able to do anything..

thnx in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 08:37:42

General approach

Update t2
set t2.col1=t1.col1,....
from table1 t1 inner join table2 t2
on t1.keycol=t2.keycol

Insert into table2(columns)
select columns from table1 t1
where not exists(select * from table2 where keycol=t1.keycol)

Madhivanan

Failing to plan is Planning to fail

abhwhiz
Starting Member

37 Posts

Posted - 2007-08-22 : 11:04:10

that was gr8 madhi,
yeah, it suddenly looked surpringly simple. i modified the first script for my need (and i was successful too) , second one worked straightaway. i am learning a lot nowadays and thnx for that. i guess the issue was i was thinking about the answer too much.
once again thnx...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources