cshetler
asked on
Script to Reseed tables in SQL
Here's the situation. I have a series of tables that basically need to use two series of seeded values in the identity column
The master entries in the tables that we create will start off with identities like below...
table1
1
2
3
4
When we deploy the database to a remote site we will need to reseed the those tables starting at 1000000 so after entries are made the identity field data will look like below.
table1
1
2
3
4
1000000
1000001
1000002
Now here's the catch
If we make additional entries here at the home office we need to run a script that identifies the seed value less than 1000000 and set the identity to that value so the next value in this scenario will cause the table to look like below.
table1
1
2
3
4
5
1000000
1000001
1000002
So a script that will effectively move my identity values between a low end and high end value.
The master entries in the tables that we create will start off with identities like below...
table1
1
2
3
4
When we deploy the database to a remote site we will need to reseed the those tables starting at 1000000 so after entries are made the identity field data will look like below.
table1
1
2
3
4
1000000
1000001
1000002
Now here's the catch
If we make additional entries here at the home office we need to run a script that identifies the seed value less than 1000000 and set the identity to that value so the next value in this scenario will cause the table to look like below.
table1
1
2
3
4
5
1000000
1000001
1000002
So a script that will effectively move my identity values between a low end and high end value.
This will also break when the 'home office' hits 1000000 rows. Google 'Y2K'.
Another possibility would be to have a two-column key, where one column is the location (home office, remote site 1, remote site 2, Elbonia, etc) and the other column is a incrimenting number unique to that site. This is not abundantly recommended as this forces every JOIN on this table to be two columns, but if it's attractive to the client it's a possibility.
Another possibility would be to have a two-column key, where one column is the location (home office, remote site 1, remote site 2, Elbonia, etc) and the other column is a incrimenting number unique to that site. This is not abundantly recommended as this forces every JOIN on this table to be two columns, but if it's attractive to the client it's a possibility.
ASKER
Hello Jim:
I understand your points, trust me. Your comments are not acceptable solutions to the problem presented. Regardless how bad a design this may be it's what I've been left to deal with. Changes such as adding columns to the schema to manage the issue will not work with this implementation.
I understand your points, trust me. Your comments are not acceptable solutions to the problem presented. Regardless how bad a design this may be it's what I've been left to deal with. Changes such as adding columns to the schema to manage the issue will not work with this implementation.
Ok. In that case I declare dibs on 'You can't do that' as the correct answer, and will gracefully back away from the question to encourage other experts to respond, in case someone can prove otherwise.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You just need to reseed the identity values on the remote table after they are initially loaded.
--query runs on remote_server
--initial load of table using existing identity values
SET IDENTITY_INSERT dbo.table1 ON
INSERT INTO dbo.table1 ( ident, ... )
-- if you need to, you can even generate your own identity value to be inserted
-- based on rows currently existing in the remote server's table
SELECT ident, ...
FROM master_server.dbo.table1
WHERE
ident < 1000000
SET IDENTITY_INSERT dbo.table1 OFF
DBCC CHECKIDENT ( 'dbo.table1', RESEED )
Then when you need to insert rows from the local/master server again, you specify "SET IDENTITY_INSERT ON" again so those values can be 5/6/whatever the local/master ident number is; if you want, you can reaffirm the local seeding by running RESEED again but w/o specifying a reseed value :
--query runs on remote_server
SET IDENTITY_INSERT dbo.table1 ON
INSERT INTO dbo.table1 ( ident, ... )
SELECT ident, ...
FROM master_server.dbo.table1
WHERE
ident >= <last_ident_sync'd> AND
ident < 1000000
SET IDENTITY_INSERT dbo.table1 OFF
DBCC CHECKIDENT ( 'dbo.table1', RESEED )
P.S. I lay first claim to the answer "it probably can be done" :-) .
--query runs on remote_server
--initial load of table using existing identity values
SET IDENTITY_INSERT dbo.table1 ON
INSERT INTO dbo.table1 ( ident, ... )
-- if you need to, you can even generate your own identity value to be inserted
-- based on rows currently existing in the remote server's table
SELECT ident, ...
FROM master_server.dbo.table1
WHERE
ident < 1000000
SET IDENTITY_INSERT dbo.table1 OFF
DBCC CHECKIDENT ( 'dbo.table1', RESEED )
Then when you need to insert rows from the local/master server again, you specify "SET IDENTITY_INSERT ON" again so those values can be 5/6/whatever the local/master ident number is; if you want, you can reaffirm the local seeding by running RESEED again but w/o specifying a reseed value :
--query runs on remote_server
SET IDENTITY_INSERT dbo.table1 ON
INSERT INTO dbo.table1 ( ident, ... )
SELECT ident, ...
FROM master_server.dbo.table1
WHERE
ident >= <last_ident_sync'd> AND
ident < 1000000
SET IDENTITY_INSERT dbo.table1 OFF
DBCC CHECKIDENT ( 'dbo.table1', RESEED )
P.S. I lay first claim to the answer "it probably can be done" :-) .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The key to this solution is that it works exactly as we needed it to. It solves the problem that we had. The key in the solution we created was being able to dynamically find the correct new identity whether we were searching for the high value or low value.
We gave partial credit to DcpKing due his code snippet helping point us in the right direction.
We gave partial credit to DcpKing due his code snippet helping point us in the right direction.
As an aside, if a key value has any business meaning to the user, I HIGHLY recommend keeping the id field as an identity, and build some logic to populate a 'business key' column that will be what the business will refer to as the primary key. That way, you can handle any goofy-riffic logic they throw at you, and keep the integrity of the primary key.