Solved

Script to Reseed tables in SQL

Posted on 2013-10-22
8
354 Views
Last Modified: 2013-12-17
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.
0
Comment
Question by:cshetler
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39591889
You can't have two sets of incriments in an identity field, so 'You can't do that' is the answer based on your requirements.

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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39591916
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.
0
 

Author Comment

by:cshetler
ID: 39591960
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39591968
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 200 total points
ID: 39593134
Well, while I also agree totally with Jim, I would propose the possibility of using a technique used before identity and auto-increment fields were introduced.

Firstly, however, you must accept that you must have some sort of flag somewhere in the system that you can set when you wish to use the system in "home office mode". Setting that flag (and un-setting it after use) is the responsibility of the home office people or of software there!

You will need an insert trigger on the table.

Here's an example of the kind of trigger you'll need to write: please excuse any/all syntax errors and typos - I don't have a SQL Server machine here to test it with!

Basically, you get the id value that just got inserted.
Then, if you're in Home Office Mode,
and if the new id is over a million
then you re-write the new id as being one more than the largest sub-million id.

CREATE TRIGGER trUpdateTableName
ON TableName
FOR INSERT
AS
    declare @intHomeOfficeFlag   int,
                @intJustSetID    int
    set @intJustSetID = inserted.id
    set @intHomeOfficeFlag = (select HOF from dbo.Flags)
    if @intHomeOfficeFlag = 0    --let's say 0 = True
    begin
        if @intJustSetID > 1000000
        begin
            update TableName
            set id = (select max(id) from TableName where id < 1000000) + 1
            where id = @intJustSetID
        end
    end

Open in new window


If you end up doing something like this, document it all over the place, as nobody'll ever find it otherwise!!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39594729
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" :-) .
0
 

Accepted Solution

by:
cshetler earned 0 total points
ID: 39715187
We created a stored procedure to accomplish this task.

It takes a parameter to reseed the table "high" or "low".

/****** Object:  StoredProcedure [dbo].[sp_reseed_tables]    Script Date: 12/12/2013 12:55:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_reseed_tables] 
	-- Add the parameters for the stored procedure here
	@Param1 As varchar(10) = NULL
AS
BEGIN

		DECLARE @Table TABLE(
		table_id int NOT NULL,
		name varchar(150) NOT NULL,
		id_col varchar(150) NOT NULL
		);

		/*populate temp table - This is the list of tables that we want to reseed either "high" or "low" */
		insert into @Table values (1,'Affiliate',(SELECT Name as IdentityColumn FROM syscolumns WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id) = 'Affiliate'))
		insert into @Table values (2,'Section',(SELECT Name as IdentityColumn FROM syscolumns WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id) = 'Section'))

		Declare @TableName varchar(150)
		Declare @IDENTCOL varchar(150)
		Declare @TABLEID int
		Declare @MAXID int
		DECLARE @Query VARCHAR(5000)

		select @TABLEID = min( table_id ) from @Table

		/*loop through temp table*/
		while @TABLEID is not null
			begin
				/*fetch table name and identity column from temp table one row at a time*/
				select @TableName = name, @IDENTCOL = id_col from @Table where table_id = @TABLEID
				
				/*build query to execute*/
				set @Query = 'declare @maxid as int;'
				
				IF (@Param1 IS NULL)
					BEGIN
						GOTO BranchError1;
					END				
				ELSE IF (@Param1 = 'init') OR (@Param1 = 'INIT')
					BEGIN
						/*ONLY for resseding for the first time as million*/
						set @Query = @Query + 'DBCC CHECKIDENT (''' + @TableName + ''',RESEED,1000000);'		
					END
				ELSE IF (@Param1 = 'high') OR (@Param1 = 'HIGH')
					BEGIN
						/*find next seed number under 10 million*/
						set @Query = @Query + 'select @maxid = MAX(' + @IDENTCOL + ') from ' + @TableName + ' where ' + @IDENTCOL + ' < 2000000;'
						set @Query = @Query + 'DBCC CHECKIDENT (''' + @TableName + ''',RESEED,@maxid);'		
					END
				ELSE IF (@Param1 = 'low') OR (@Param1 = 'low')
					BEGIN
						/*find next seed number under 1 million*/
						set @Query = @Query + 'select @maxid = MAX(' + @IDENTCOL + ') from ' + @TableName + ' where ' + @IDENTCOL + ' < 1000000;'
						set @Query = @Query + 'IF @maxid IS NULL SET @maxid = 1';
						set @Query = @Query + 'DBCC CHECKIDENT (''' + @TableName + ''',RESEED,@maxid);'			
					END
				ELSE
					BEGIN
						GOTO BranchError2;
					END					
					
				/*execute query!!*/
				EXEC(@Query);
				
				/**next temp table id**/
				select @TABLEID = min( table_id ) from @Table where table_id > @TABLEID  
			end
			GOTO BranchEnd;

			BranchError1:
				Print 'Error: No parameter  - usage: EXEC sp_reseed_tables ''init/high/low'''
				GOTO BranchEnd;
			BranchError2:
				Print 'Error: Invalid parameter  - usage: EXEC sp_reseed_tables ''init/high/low'''
				GOTO BranchEnd;
			BranchEnd:
				/*End of Program*/

END

Open in new window

0
 

Author Closing Comment

by:cshetler
ID: 39723495
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now