SQL IDENTITY INSERT Issue

Question

Can you please help.

I am running the following query:

set identity_insert sites_dynamic ON 

insert into sites_dynamic
    select * 
    from gpbatt_archive.dbo.sites_dynamic 
    where site_serial_number = 49955

set identity_insert sites_dynamic OFF 

set identity_insert sites_static ON 

insert into sites_static
    select * 
    from gpbatt_archive.dbo.sites_static 
    where static_site_id in (select static_site_id 
                             from gpbatt_archive.dbo.sites_dynamic 
                             where site_serial_number = 49955)

set identity_insert sites_static OFF 

Error returned is:

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

Can anyone help me? I am new to this

Answer 1

When using set identity_insert <tablename> on your insert statement must include the columns list.

Here is the correct way to do it:

set identity_insert targetTable on

insert into targetTable (col1, col2 [, coln])
select col1, col2 [, coln]
from sourceTable

set identity_insert targetTable off

As commented by Nick.McDermaid - It's best practice to always include the columns list.