Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY_INSERT and SELECT * Expand / Collapse
Author
Message
Posted Tuesday, May 22, 2007 3:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2007 10:12 AM
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.

Post #368113
Posted Tuesday, May 22, 2007 4:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 20,799, Visits: 32,718

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.




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)
Post #368117
Posted Tuesday, May 22, 2007 4:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:29 PM
Points: 3,840, Visits: 3,852

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
Post #368128
Posted Wednesday, May 23, 2007 7:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, 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.
Post #368257
Posted Wednesday, May 23, 2007 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2007 10:12 AM
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)

 

Post #368274
Posted Wednesday, May 23, 2007 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2007 10:12 AM
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?

 

Post #368284
Posted Wednesday, May 23, 2007 8:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 20,799, Visits: 32,718

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




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)
Post #368288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse