tl;dr; When doing an insert across a linked server you have to include the list of fields to be inserted into if there is an identity column involved.

A couple of years back I did a list of things I’d learned in my 15 years of experience with identity columns. It’s a pretty good list if I do say so myself. Well, this week I’ve learned something new.

If you’ve used the command INSERT INTO to insert data into a table you’ve probably done it both with and without specifying columns.

CREATE TABLE dbo.IdentTest (
Id INT NOT NULL IDENTITY(1,1),
Col1 CHAR(4),
Col2 CHAR(4)
);
-- With column list
INSERT INTO dbo.IdentTest (Col1, Col2) VALUES ('Col1','Col2');
-- Without column list
INSERT INTO dbo.IdentTest VALUES ('Col1','Col2');

If you’ve done this with identity columns then you probably also know that if you are going to do it with IDENTITY_INSERT you’re going to have to specify the list of columns. And if you don’t specify the list then it’s going to assume you mean all of the columns except the identity column.

Quick note. Including the list of columns is considered a best practice. If for no other reason than because this way if the structure of the table changes, (say a column is added, or one removed, or heck, the order of the columns changed) then your code won’t break.

So far so good. Now let’s throw in a twist. Let’s call it through a linked server.

INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest 
VALUES ('Col1','Col2');

Msg 213, Level 16, State 1, Line 4

Column name or number of supplied values does not match table definition.

Well that’s a bit odd, right? I mean I used that exact command in the previous test. Turns out that when you do an insert across a linked server that identity column is not ignored. Which means we just need to include the identity value right? Nope.

INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest 
VALUES (1,'Col1','Col2');

Msg 7344, Level 16, State 1, Line 4

The OLE DB provider “SQLNCLI11” for linked server “(local)\sql2014cs” could not INSERT INTO table “[(local)\sql2014cs].[Test].[dbo].[IdentTest]” because of column “Id”. The user did not have permission to write to the column.

Besides, the whole point of the identity column is to get an auto incrementing id right? So now we try it with the column list. Yea, it was the obvious solution but I wanted to explore the options

INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest (Col1, Col2) 
VALUES ('Col1','Col2');

And it worked!

Please note, I’ve done this with the driver SQLNCLI11 and SQLNCLI10 and between several different versions of SQL. I can’t promise that it will happen with every version (I didn’t test 2017) and with every driver. Or even other types of remote connections.