Posted by Plamen Ratchev on 06/04/07 20:54

The syntax to use INSERT INTO is like this:

INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'

A few brief notes:
- the <column_list> will list your columns (like COL1, COL2, COL3, etc.)
- the <column_list> must contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_list> in the INSERT INTO clause (as you
did in your sample query), then the <column_list> in SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)

If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]