May 28, 2014 at 9:54 am
Hello all. I mentioned in a post from a couple of days ago that I was in the process of loading data into dozens of tables in a DEV database from a PROD database. Essentially just getting fresh data. Dropping the DEV database and restoring it from a fresh copy of the PROD database is not an option. Therefore, I've been working on a Stored Proc that does all this magic. For the most part, it mostly works great.
I am running into one problem and my solution is a hack. Many tables have the PK as a AutoNumber. It simply increments by 1 (Identity). Through out the life of the table, records could be deleted (not my design). So the PKs in the Table will have numeric gaps. For example:
SOURCE TBL
IDDescription
1Thing 1
2Thing 2
5Thing 5
6Thing 6
7Thing 7
8Thing 8
9Thing 9
10Thing 10
Notice that records 3&4 were deleted at some point in time. If I try to insert these into a mirror table/database, it then looks like this:
DESTINATION TBL
IDDescription
1Thing 1
2Thing 2
3Thing 5
4Thing 6
5Thing 7
6Thing 8
7Thing 9
8Thing 10
Ruh Roh Scooby. They don't match. Surely this is a common scenario. My hack is to change the design manually of the DESTINATION Table(s) and remove that IDENTITY setting. Just have the IDs fields as ints & PKs. Once the inserting is all over, manually change them back.
Perhaps there is better solution but my ideal solution was to, in my Stored Proc, remove the IDENTITY setting/definitions in the DESTINATION TBL, do the insert, and the add the IDENTITY settings back when finished. Make sense?
So in my TSQL, change this:
[ID] [int] IDENTITY(1,1) NOT NULL,
to this:
[ID] [int] NOT NULL
...and then do my Insert. When finished, change it back.
Make sense? I was hoping someone could provide a sample of code that does this.
Thanks for the input!
May 28, 2014 at 10:03 am
Instead of removing the identity specs, take a look at: SET IDENTITY_INSERT tablename OFF
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 28, 2014 at 10:05 am
You could use "SET IDENTITY_INSERT <table_name> ON" to insert identity values into a table. Just remember to switch it off afterwards.
😎
May 28, 2014 at 10:06 am
Thanks! Will give it a shot.
May 28, 2014 at 10:56 am
...almost there. Almost. Any idea as to why I can only do this once in my Stored Prod?
For example:
SET IDENTITY_INSERT pnd_Product ON
SET IDENTITY_INSERT pnd_Customer ON
Msg 8107, Level 16, State 1, Procedure sp_TDL_RefreshTaradelStgTables, Line 164
IDENTITY_INSERT is already ON for table 'xxxx.dbo.pnd_Product'. Cannot perform SET operation for table 'pnd_Customer'.
????
Checked many references. Can't see anything wrong..but most online examples only so one example. I need to do this for about 6 tables.
May 28, 2014 at 10:58 am
Nevermind. Found answer:
"At any time, only one table in a session can have the IDENTITY_INSERT property set to ON..."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply