Sql-server – insert into OPENROWSET / SET IDENTITY_INSERT

sql server

INSERT INTO OPENROWSET('SQLOLEDB', 'Provider=SQLOLEDB.1;server=server;uid=ssa;pwd=xxx;database=eDemo;', 'Select ID,firstname,lastname,CNP,Phone, allowAmbSelection,Title,parafa  from n_staff ')
       SELECT id, 
              UPPER(firstname) firstname, 
              UPPER(lastname) lastname, 
              0 allowAmbSelection, 
       FROM n_staff
       WHERE ID <> 1;

my problem is that Column ID is Identity and i would like to use SET IDENTITY_INSERT N_STAFF ON for the insert table…i don't know how to do that..if possible…

Best Answer

You don't have to SET IDENTITY_INSERT, you should use the "KEEPIDENTITY" table hint.

See Documentation

And see on this page multiple examples on how to use the table hint.