IDENTITY_INSERT and SELECT *
-
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
- delete all the data in the local table
- set the IDENTITY_INSERT to OFF for the local table
- 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.
-
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.
-
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.
-
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] -
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)
-
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?
-
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply