IDENTITY_INSERT and SELECT *

Question

IDENTITY_INSERT and SELECT *

Lindsay Ewing

Old Hand

Points: 344
More actions
May 22, 2007 at 3:56 pm

#124156
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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

John Rowan SSC Guru Points: 56440 More actions · Answer 2

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[/url] - by Jeff Moden

JacekO SSCertifiable Points: 6610 More actions · Answer 3

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.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Lindsay Ewing Old Hand Points: 344 More actions · Answer 4

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 Old Hand Points: 344 More actions · Answer 5

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 SSC Guru Points: 442467 More actions · Answer 6

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