INSERT or UPDATE table from another table with com

问题:

I am looking for the correct syntax and way to do the following directly from SQL: insert or update (if data already exists inside) TableMain from data contained in TableA with both having 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 filled.

TableMain as the same definition but will contain many more rows of data and what I need is to insert never seen values from TableA into TableMain, and update already existing rows.

I used to do this kind of insert but I do not know how to handle update 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] 
       )

回答1:

Here is a script you might use to upsert 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 might rewrite not exists() as left join ... where TableMain.TID0 is null if performance is not satisfactory.

回答2:

you should use a merge statment

something like this:

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>

回答3:

you can use the Merge command from SQLServer 2008. It allows you to merge data from another data source into your main data source and define specific behavior when there is a key match (and you'll probably like to update your table) or there is not match and you'll like to insert the 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 few code samples.