INSERT or UPDATE from another table with a composite primary key

Question

I am looking for the correct syntax and way to do the following directly from SQL: insert or update (if the data already exists inside) the TableMain from the data contained in TableA , both of which have the same composite primary key.

Both tables are defined as:

 CREATE TABLE TableA ( [TID0] [int] NOT NULL, [TID1] [int] NOT NULL, [language] [nvarchar](2) NOT NULL, [TID2] [nvarchar](200) NOT NULL, [text] [nvarchar](max) NULL, [updatedOn] [datetime] NOT NULL DEFAULT (getdate()) PRIMARY KEY ( [TID0], [TID1], [language], [TID2], ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

TableA will be periodically deleted and populated.

TableMain is the same definition, but it will contain much more rows of data, and I need to insert the values ​​I have never seen from TableA into TableMain and update the existing rows.

I used this insert, but I do not know how to handle updated and composite primary keys:

 INSERT INTO TableMain SELECT * FROM TableA 

EDIT: I am using SQL Server 9.00.5000

EDIT: another way inspired by MERGE and mimick it

 DECLARE @updatedIDs TABLE( [TID0] [int], [TID1] [int], [language] [nvarchar](2), [TID2] [nvarchar](200), PRIMARY KEY ([TID0], [TID1], [language], [TID2]) -- as stated by Nikola Markovinović above, thanks ); -- First update records update TableMain set [text] = source.[text], [updatedOn] = source.[updatedOn] OUTPUT inserted.[TID0] inserted.[TID1] inserted.[language] inserted.[TID2] INTO @updatedIDs from TableMain AS main , TableA AS source WHERE TableMain.[TID0] = source.[TID0] and TableMain.[TID1] = source.[TID1] and TableMain.[language] = source.[language] and TableMain.[TID2] = source.[TID2] -- And then insert insert into TableMain select * from TableA AS source where not exists ( select 1 from @updatedIDs AS i where i.[TID0] = source.[TID0] and i.[TID1] = source.[TID1] and i.[language] = source.[language] and i.[TID2] = source.[TID2] ) 

Answer 1

Here is a script you could use to boost your data:

 -- On error transaction is automatically rolled back set xact_abort on begin transaction -- First update records update TableMain set [text] = source.[text], [updatedOn] = source.[updatedOn] from TableMain inner join TableA source on TableMain.[TID0] = source.[TID0] and TableMain.[TID1] = source.[TID1] and TableMain.[language] = source.[language] and TableMain.[TID2] = source.[TID2] -- And then insert insert into TableMain ([TID0], [TID1], [language], [TID2], [text], [updatedOn]) select [TID0], [TID1], [language], [TID2], [text], [updatedOn] from TableA source where not exists ( select * from TableMain where TableMain.[TID0] = source.[TID0] and TableMain.[TID1] = source.[TID1] and TableMain.[language] = source.[language] and TableMain.[TID2] = source.[TID2] ) commit transaction 

You can rewrite not exists () as a left join ... where TableMain.TID0 is null if the performance is not satisfactory.

Answer 2

you should use merge statment

something like that:

 merge TableMain AS target using TableA as source ON <join tables here> WHEN MATCHED THEN <update> WHEN NOT MATCHED BY TARGET <Insert> WHEN NOT MATCHED BY SOURCE <delete> 

Answer 3

you can use the Merge command from SQLServer 2008. It allows you to combine data from another data source into your main data source and determine when there is a key match (and you probably like updating the table), or there is no match, and you want to insert a new record .

http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/

You can visit this link to get some sample code.