CODEWITHSUNDEEP
sqlsql-serverselectinsert
jpo
jpo

Reputation: 4059

SQL: use SELECT to INSERT data into table with extra column

I have a table Table1 with 59 columns. Table2 is a copy of Table1 with one extra column, COLUMN 60 at the end. Hence table2 has 60 columns.

I am trying to copy the values from table1 to table2 and set value of the extra column in table2 to "value"

Something like this

INSERT INTO Table2
   SELECT * FROM Table1, 'value' AS 'COLUMN 60'

How can I do this? Using the code above give me an error:

An explicit value for the identity column in table 'TableLocation' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I do not want to specify column names because there are too many of those.

Upvotes: 3

Views: 16216

Answers (3)

SQLMenace
SQLMenace

Reputation: 135011

You know that you can just drag the column names from SSMS right? Navigate to the table and then drag the columns folder into the query window, it will list all the columns for you

Now if you want to preserve the identity values then use 

SET IDENTITY_INSERT dbo.Table2 ON

--INSERT HERE
-- make sure to list all the columns, it is required

SET IDENTITY_INSERT dbo.Table2 OFF

Upvotes: 3

nullrevolution
nullrevolution

Reputation: 4127

Does it really need to be done in one statement?

INSERT INTO Table2
SELECT * FROM Table1

ALTER TABLE Table2
ADD Column60 varchar

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7761

Try this:

SET IDENTITY_INSERT dbo.Table2 ON

INSERT INTO Table2
SELECT *, 'value' AS 'COLUMN 60' FROM Table1

SET IDENTITY_INSERT dbo.Table2 OFF

Upvotes: 8

Related Questions