Are these statements valid?
UPDATE Table1
FROM (SELECT * FROM Table2)
INSERT INTO Table1
(SELECT * FROM Table2)
views:
171answers:
2
+4
A:
Your update statement needs a Set for each field you want to update, so no. Correct syntax:
UPDATE table1
SET table1.field1=table2.field1, table1.field2=table2.field2
FROM table1
INNER JOIN table2 ON table1.keyfield = table2.keyfield
Your insert statement will work provided that Table1 and Table2 have the same columns in the same order.
Edit: If you're looking for an example of Update/Insert (Upsert), check out this blog post (which works on SQL 2008--not sure about 2005 but doubt it).
Another option is an Update and then an insert. Example from this blog post :
UPDATE CustomersA
SET CustomerName = B.CustomerName
FROM CustomersA A (NoLock)
INNER JOIN CustomersB B (NoLock) ON A.CustomerId = B.CustomerId
And later run the Insert command
INSERT INTO CustomersA (
CustomerId,
CustomerName
)
SELECT
Id,
Name
FROM CustomersB (NoLock)
WHERE
Id NOT IN (
SELECT CustomerId FROM CustomersA (NoLock)
)
C-Pound Guru
2009-09-16 17:50:32
A small caveat is that the Insert will fail if there are any identity fields present.
CodeByMoonlight
2009-09-16 17:53:08
You should always specify the columns you want to work with. "Select *" will probably stop working when columns are added, dropped, or reordered.
Philip Kelley
2009-09-16 17:54:18
There's always MERGE in SQL Server 2008 to perform your UPSERT (UPDATE/INSERT)
Russ Cam
2009-09-16 17:56:30
Actually I was trying to do an UPSERT with SQL 2005. There are 2 TSQl additions INTERSECT and EXCEPT. In one of the blogs(http://209.34.241.68/mat_stephen/archive/2005/08/31/410022.aspx) it is said Update Table1 from (Select tab1 Intersect Destination) and Insert into Tab1 from(select tab1 EXCEPT tab2).
Ranjit
2009-09-16 18:01:12
To all, thanks for your inputs.@Russ: It is SQL 2005, I dont have the Merge functionality. @C-Pound: I do have the Update and Insert statements that you mentioned. The idea of using the EXCEPT and INTERSECT is to off-set these joins. Also they9MS) claim performance wise these new T-SQL are better than Joins.
Ranjit
2009-09-16 19:50:25
+1
A:
INSERT INTO Table1
(SELECT * FROM Table2)
That could work - if you're lucky.
I'd recommend using a more strict syntax:
INSERT INTO dbo.Table1 (Field1, Field2, ..., FieldN)
SELECT Field1, Field2, ..., FieldN
FROM dbo.Table2
I would always and explicitly specify the schema (dbo) and fields for both my INSERT and my SELECT statements. That way, you can
- eliminate columns that are e.g. computed, IDENTITY and other potential "problem" cases
- your SQL statement won't break when you add another column to either table
Marc
marc_s
2009-09-16 18:09:04