2

I am having problems with my query. Basically, what I am trying to do is empty out a table and copy the records from the same table in another database.

I did use the SET IDENTITY_INSERT code to make sure that the identity column is turned off before I perform my insert. But somehow, it still throws me the error message:

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.UI_PAGE' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Below is my query:

DELETE FROM [DB1].[dbo].[MY_TABLE] 

SET IDENTITY_INSERT [DB1].[dbo].[MY_TABLE] ON

INSERT INTO [DB1].[dbo].[MY_TABLE]
   SELECT *
   FROM  [DB2].[dbo].[MY_TABLE]

SET  IDENTITY_INSERT [DB1].[dbo].[MY_TABLE] OFF

Can someone point me as to which step I am doing wrong?

Thanks a lot!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Smiley
  • 3,207
  • 13
  • 49
  • 66

2 Answers2

5

You have to specify all the column names when inserting with IDENTITY INSERT ON when using INSERT INTO

INSERT INTO  [DB1].[dbo].[MY_TABLE](TabelID,Field1,Field2,Field3...)
SELECT * FROM  [DB2].[dbo].[MY_TABLE]

In case you did not know there is a nifty little trick in ssms. If select a table and expand its' nodes you ctrl-c copy on the Columns node and that will place a comma-delimited list of the field names on your clipboards text buffer.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • 2
    And I would even recommend to **always** explicitly list the columns in your all of your `INSERT` AND `SELECT` statements. It's a bit more typing up front, but it makes for a lot of avoided, hard to find issues and errors! – marc_s Jun 28 '14 at 07:40
1

Addition to the first answer given by Ross Bush, If your table has many columns then to get those columns name by using this command.

SELECT column_name + ','
FROM   information_schema.columns 
WHERE  table_name = 'TableName'
for xml path('')

(after removing the last comma(',')) Just copy past columns name.

Rousonur Jaman
  • 1,183
  • 14
  • 19