Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IDENTITY_INSERT and SELECT *


IDENTITY_INSERT and SELECT *

Author
Message
Lindsay Ewing
Lindsay Ewing
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1

This is driving me crazy and makes no sense and wanted to see if anyone else has come across this and can help me - or if i can just share the pain.

i have a stored procedure that takes a table name (it exists in 2 databases on the same SQL server) and will

  1. delete all the data in the local table
  2. set the IDENTITY_INSERT to OFF for the local table
  3. copy all the data from the table in the other database to the local table

easy stuff. this works for pretty much every table until i get to a couple. then i get :

An explicit value for the identity column in table 'table name' can only be specified when a column list is used and IDENTITY_INSERT is ON.

no idea why it happens for these few tables. there is nothing different about them that i can see (but i may not be looking in the right places)

Code :

SELECT @sql = 'DELETE ' + @tablename
EXEC (@sql)
SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
EXEC (@sql)
SELECT @sql = 'INSERT INTO ' + @tablename + ' SELECT * FROM ' + @DB + '..' + @tablename
EXEC (@sql)

@tablename is the tablename, @DB is the other database, the stored procedure is run from the local database.

any pointers/advice/ideas even would be appreciated.


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950

This says it all: An explicit value for the identity column in table 'table name' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Looks like some of the tables your are trying to insert data into have identity columns. If you want those to have the same value as the source tables, you need to turn IDENTITY_INSERT ON for those tables and, yes, you need to include a list of the columns as well. If you want the identity field to have new values, then you need to list all the columns you want transfered in both theinsert into and the select from.

Sorry, but that's the way it is.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511

In addition to what Lynn has posted:

It looks like you are performing this action on a number of tables hence the dynamic SQL. Don't forget to set the IDENTITY_INSERT back to OFF after turning it ON as you can only have it ON for one table at a time during your connection. If you are looping through table names and performing the insert action and forget to turn it back OFF for those tables containing an identity column, SQL Server will give you errors.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606

I don't know if it was apparent from the previous posts but you should be fine if you change this

SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

to this

SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' ON'

in your code.

Actually you should also include this

SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
EXEC (@sql)

into your script after you perform the insert to set the setting back the way it was.

The reason some tables work and some don't is that the tables that work do not have identity columns. The others do and the query blows up.



---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Lindsay Ewing
Lindsay Ewing
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1

thanks everyone for your help. i was able to work through it.

apart from the major brain freeze of getting my IDENTITY_INSERT OFF and ONs mixed up (and who hasn't done that before ) it was as i had feared - i needed to specify the column names - SELECT * just couldn't handle it.

so i wrote a small function that built a comma separated list of all the column names for the table name and used that in the SELECT statement (i included it below). and i alos modified the code to use the correct ON and OFF.

i also never realised that you could only have one ON per session so the OFF after the INSERT was added.

thanks to everyone.

working code:

ALTER FUNCTION dbo.arc_udfGetTableColumns
(
@tablename VARCHAR(200)
)
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE @rc VARCHAR(5000)
DECLARE @colName VARCHAR(200)
DECLARE @comma VARCHAR(2)
SELECT @rc = ''
SELECT @comma = ''
DECLARE curColumns CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT [name] FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [NAME] = @tablename)
ORDER BY colorder
OPEN curColumns
FETCH NEXT FROM curColumns INTO @colName
WHILE (@@FETCH_STATUS >= 0)
BEGIN
IF @@FETCH_STATUS < 0
BREAK
SELECT @rc = @rc + @comma + @colName
SELECT @comma = ', '
FETCH NEXT FROM curColumns INTO @colName
END
CLOSE curColumns
DEALLOCATE curColumns
RETURN @rc
END

so the original INSERT code above becomes :

SELECT @cols = dbo.arc_udfGetTableColumns(@tablename)
SELECT @sql = 'DELETE ' + @tablename
EXEC (@sql)
SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' ON'
EXEC (@sql)
SELECT @sql = 'INSERT INTO ' + @tablename + '(' + @cols + ') ' +
'SELECT ' + @cols + ' FROM ' + @DB + '..' + @tablename
EXEC (@sql)
SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
EXEC (@sql)


Lindsay Ewing
Lindsay Ewing
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1

JacekO - Thanks for this but it needed more (see my post about result)

I did find out though why it worked on some tables and you were right - the client had REMOVED the IDENTITY value from some tables of the destination database!!! go figure!

All our tables have an identity column so this was not going to be an issue (or it would be an issue for all of them) so that is why i could not understand why it was not being consistent.

Who would guess a client would do something so crazy huh?


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950

You can also eliminate your cursor to build you column list using the following code:

declare @columns varchar(5000)
SET @columns = ''
SELECT @columns = @columns + [name] + ', ' FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [NAME] = @tablename)
ORDER BY colorder
SET @columns = substring(@columns, 1, len(@columns) - 1)

select @columns



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search