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

Question

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.

Solution 1

Try this:

SET IDENTITY_INSERT dbo.Table2 ON

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

SET IDENTITY_INSERT dbo.Table2 OFF

Solution 2

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