SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The identity column, the insert and the linked server.

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...