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.
| 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.TableNameSELECT * FROM [ServerName2].DBName2.dbo.TableName WHERE StoreID = 123I 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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). |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
IDENTITY_INSERT