0

Good Day,

I am currently having trouble with IDENTITY_INSERT. I have a linked server setup with the required permissions needed.

I have tested that I can use IDENTITY_INSERT with a simple insert query, but it does not work if I use the following code.

SET IDENTITY_INSERT table_name ON
INSERT INTO table_name SELECT * FROM OPENQUERY([server_name], 'SELECT * FROM ##Tmp2');

Using the above method I receive the following error: An explicit value for the identity column in table 'table_name' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can anyone kindly help me regarding this request, I would like to select data from a Tmp table from a different server and I can't ignore the IDENTITY Column due to it is required or link to other tables.

Thank you in advance.

Wimpie Norman
  • 27
  • 5
  • You're not providing an explicit column list. `insert into table_name (aColumn, anotherColumn, ...) select aColumn, anotherColumn, ... from ...` – allmhuran Jul 08 '20 at 08:40
  • @allmhuran - Thank you so much, I just tried it and it worked, you are a life safer. I appreciate the help. – Wimpie Norman Jul 08 '20 at 09:00
  • You're welcome. Get into the habit of using explicit column lists everywhere, it will save you eventually, because one day someone will add a column to a table, and `select *` or `insert mytable select ...` will hurt :) – allmhuran Jul 08 '20 at 09:26

1 Answers1

0

Answer was provided by @allhuran.

@allmHuran - ' You're not providing an explicit column list.

insert into table_name (aColumn, anotherColumn, ...) select aColumn, anotherColumn, ... from ... '

Wimpie Norman
  • 27
  • 5