asked
Have Update script, want to insert rows if no match..
Hello, I have a simple update script like this (and I'm quite new.. so this wasn't exactly so simple for me) UPDATE Table2 SET Table2.Field = Table1.FieldA + Table1.FieldB FROM Table2, Table1 WHERE Table1.Field1 = Table2.Field1, ..., Table1.Field3 = Table2.Field3 So it works great, but the problem is that if Table2 doesn't match on all these criteria, the field from table1 doesn't come over. I need it to insert a row if it doesn't match anyway as I need my total of this column to match the other table. I hope this is clear, could anyone give me a hand.. it'd be appreciated. Thanks! Sheep
On SQL Server 2008 you can use MERGE command: MERGE Table2 USING Table1 ON Table1.Field1 = Table2.Field1, ..., Table1.Field3 = Table2.Field3 WHEN MATCHED THEN UPDATE Table2 SET Field = Table1.FieldA + Table1.FieldB WHEN NOT MATCHED BY TARGET THEN INSERT Table2(Field, Field1, ... Field3) VALUES (Table1.FieldA + Table1.FieldB, Table1.Field1,..., Table1.Field3); For details see [MERGE (Transact-SQL) on MSDN][1]. On older version of SQL Server you will have to split it into separate update and insert. For example first you execute your update command and next you can do insert. INSERT INTO Table2(Field, Field1, ... Field3) SELECT Table1.FieldA + Table1.FieldB, Table1.Field1,..., Table1.Field3 FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1, ..., Table1.Field3 = Table2.Field3 WHERE Table2.Field1 IS NULL --better Table2.PrimaryKey IS NULL [1]:
http://msdn.microsoft.com/en-us/library/bb510625.aspx
