Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 IDENTITY_INSERT

Author  Topic 

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-21 : 16:42:41
I have a HUGE proc that takes table data from 172 different SQL tables and puts the data into the same 172 tables in a different db.

Example:
INSERT INTO [ServerName1].DBName1.dbo.TableName
SELECT * FROM [ServerName2].DBName2.dbo.TableName WHERE StoreID = 123

I get this error:
An explicit value for the identity column in table 'DFW05-QA01SQL83.qa01.dbo.AKConfigCourse' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Is there any resolution to this outside of specifically naming all fields (except the Identity column) in all 172 of my tables?

And If there is not an alternative and I do indeed have to name all fields for all tables, do I also need to explicately name them again for the values in my "SELECT * from "?

Thanks for any possibly suggestions.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 16:50:12
You need to list out the column names for both the insert and the select when there is an identity column. Alternatively, you can use the IDENTITY_INSERT option if you need to keep the same value. This requires elevated permissions. See BOL for details.

You may want to look into Transactional Replication.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-21 : 17:23:44
I do not need to keep the same values, in fact the values may conflict with some already on Server2.dbname2. I was hoping there was a way to say:

"Insert *all of the columns EXCEPT the Identity columns*
into servername1.dbname1.tableName
select *all of the columns except Identity* from servername2.dbname2.tablename"

As table structures change for any of these 172 tables, we now must remember to update this proc. Plus, I must add the structure and fields for all 172 tables (insert whining face here).

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 18:13:12
There isn't a way around this. You can script it though. I'd probably use the INFORMATION_SCHEMA.COLUMNS view to help generate it. You can also use SSMS to generate it, but you'd still have to click click click for each table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -