SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 IDENTITY_INSERT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAdauto
Posting Yak Master

USA
147 Posts

Posted - 08/21/2014 :  16:42:41  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 08/21/2014 :  16:50:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 08/21/2014 16:50:51
Go to Top of Page

JAdauto
Posting Yak Master

USA
147 Posts

Posted - 08/21/2014 :  17:23:44  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 08/21/2014 :  18:13:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000