Regarding: Problem setting IDENTITY_INSERT ON when trying to insert data

Question

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.

Answer 1

Answer was provided by @allhuran.

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

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