Insert/Update between 2 tables

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 2000 Forums

SQL Server Development (2000)

Insert/Update between 2 tables

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-06-06 : 02:47:04

I have 2 identical, structure, tables. I need to insert the data from the one intp the other. If that record already exists in the one table then it must be updated, if not then just insert a new record.

How do I do this?
Thanks
Scott

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-06 : 06:26:27

You will need to post your table structures for a more specific answer, but generally you cannot do this in one statement. You will have to update the exisiting records in one statement and then insert using another statement.

Update existing records:
UPDATE tableA
SET colA1 = colB1,
colA2 = colB2,
colA3 = colB3
FROM tableA INNER JOIN tableB
ON tableA.ID = tableB.ID

Inserting New records: EDITED
INSERT INTO TableB
SELECT * FROM TableA WHERE ID NOT IN
(SELECT ID FROM TableB)
INSERT INTO TableA
SELECT * FROM TableB WHERE ID NOT IN
(SELECT ID FROM TableA)

Owais

Edited by - mohdowais on 06/10/2003 03:49:17

Scott
Posting Yak Master

145 Posts

Posted - 2003-06-09 : 10:48:00

For this question use any table structure eg TableA, ColID, Descript. TableB the same.
Individual insert and update statements are fine, what I want is how to link the 2 conditionally. ie: If record exists in TableA the update it else insert the record.

Scott

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-10 : 03:47:47

quote:
what I want is how to link the 2 conditionally. ie: If record exists in TableA the update it else insert the record

Scott, this is what the script in my previous post did. Assuming that the source data is in TableB and you need to update TableA: The first statement will update only those rows in TableA that exist in both the tables.

UPDATE tableA
SET colA1 = colB1,
colA2 = colB2,
colA3 = colB3
FROM tableA INNER JOIN tableB
ON tableA.ID = tableB.ID

The second script will insert all rows into TableA that exist only in TableB, and not in TableA.

INSERT INTO TableA
SELECT * FROM TableB WHERE ID NOT IN
(SELECT ID FROM TableA)

I got the table names mixed up in my earlier post, sorry

Running both these statements in the same batch would be the logical equivalent of saying (if you could write SQL like this):

UPDATE TableA SET Columns = TableB.Columns
WHERE TableA.ID = TableB.ID
ELSE
INSERT INTO TableA New Rows

You will probably want to nest both these statements inside BEGIN TRAN...COMMIT TRAN block to ensure that both or none statements are executed.

Hope this solves your problem.

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources