The identity column, the insert and the linked server.


June 14, 2018 by Kenneth Fisher

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 (
	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.

4 thoughts on “The identity column, the insert and the linked server.

  1. […] Kenneth Fisher points out an oddity when inserting data across a linked server into a table with an …: […]

  2. Fernando Paez Becker says:

    Well, the problem is when “id” field is a fk for another table… In this case the only way to do the work is working on initial “linked” server “target”, create a linked server to “source” , and execute the insert select from there.

  3. THarris says:

    Thank YOU! This saved me hours of work.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: