The identity column, the insert and the linked server.
4June 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 ( 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.
[…] Kenneth Fisher points out an oddity when inserting data across a linked server into a table with an …: […]
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.
Thank YOU! This saved me hours of work.
Glad I could help 🙂