[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How do I dynamically clone a table, update the identity column (add 200,000 to it), finally  reseed the identity column

Posted on 2008-11-14
Medium Priority
Last Modified: 2013-11-30
Unfortunately I have discovered that a previously accepted solution does not actually solve my problem.
The issue is the word "clone"
And a further unstated requirement is "Dynamic"
My attempts at a solution involved "select * into TableClone from TableOriginal"
followed by "update TableClone set  ident-id = ident-id + 200000"
The big problem I have just discovered is that "select into" does not copy keys and defaults  ie it is not a "clone"
so I really seem to have 2 problems
1) how do I dynamically clone any given table?
2) how can I drop the Identity constraint, update the erstwhile identity column & then reseed it?
Question by:santa-clara
  • 6
  • 3
  • 3
  • +2
LVL 15

Expert Comment

by:Faiga Diegel
ID: 22963925
You have to manually script it.

--create constraint
ALTER TABLE dbo.CloneTable

--drop constraint
ALTER TABLE dbo. CloneTable

--reseed indentity
DBCC CHECKIDENT('[dbo].[IDV_CloneTable]', RESEED, 200000)

Author Comment

ID: 22964190
so you are saying that it is not possible to dynamically clone a table?

Plus I couldn't figure out how to add the identity attribute without dropping the column first

Accepted Solution

Ernariash earned 300 total points
ID: 22964223
You manually need to recreate the indexes, see code below

CREATE TABLE dbo.TableClone
	IdentityNum int NOT NULL IDENTITY (20000, 1),
	Column1 uniqueidentifier NULL,
	Column2 datetime NULL,
	Column3 nvarchar(50) NULL
INSERT INTO dbo.TableClone (IdentityNum, Column1, Column2, Column3)
SELECT IdentityNum+20000, Column1, Column2, Column3 FROM dbo.TableOriginal WITH (HOLDLOCK TABLOCKX)
DROP TABLE dbo.TableOriginal
EXECUTE sp_rename N'dbo.TableClone', N'TableOriginal', 'OBJECT' 
CREATE NONCLUSTERED INDEX IX_TableOriginal ON dbo.TableOriginal

Open in new window

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 22970802
>>Plus I couldn't figure out how to add the identity attribute without dropping the column first<<
That is correct you cannot add the Identity attribute to an existing column.

Author Comment

ID: 22971586
Well I now understand that I have to insert into the cloned table recalculating the new identity value as I do so.
This just leaves the rather awkward problem of cloning the table in the first place.
This would seem to be a case for SSMO since SSMS is able to generate the SQL required.
I would appreciate a being pointed in the right direction on this
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22972428
>>since SSMS is able to generate the SQL required.<<
Than why don't you use the same SQL script SSMS uses?

Author Comment

ID: 22973807
Please don't give me that "why do you want to do that?" whine
I want to dynamically clone a table.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22990124
Fair enough.
LVL 51

Expert Comment

by:Mark Wills
ID: 22991184

One way is to simply do an insert into... then fix up indexes...


if object_id('ee_clone_receiver','U') is not null drop table ee_clone_receiver

select  identity(int,200000,1) as id, date into ee_clone_receiver from ee_clone_doner

kind of depends on table structures a bit - there are foreign keys, addiotnal indexs etc - it is possible to script it all up so that you can simply do a "maketable" type stored procedure - there are a few versions of that floating around - want me to dig one up ?

Author Comment

ID: 22992040
exec sp_MakeTable 'OrignalTbl'   'CloneTbl' is certainly what I am looking for

But it does have to be able to  reproduce the original's

Constraints, Indexes, Calculated fields and triggers

just like the script generated by SSMS
LVL 51

Expert Comment

by:Mark Wills
ID: 22993010
part of the real challenge is error handling - there can be various constraints that do not exist. "smart" packages will highlight / flag etc and ask you what you want to do (or auto create dependancies) and that is why some of thos packages can get away with charging reasonable dollars...

Is it within the same database and frequently run, is it data as well as structure ?

Not sure that the simple editions will go to the extent you are looking for...  Then it might be best to compare a few commerical packages.

At the risk of being shouted at, curious what you are upto... can you spare a moment to describe what you are doing ? Sounds interesting...

Author Comment

ID: 22993244
well ... to considerably over-simplify (and yes I am fully conversant with the concepts of Normalization)
the original application simply cloned a database so that all the user application had to decide was "Which Database?"
This is starting to get out of hand because the Database contains some truly global tables which end up being "cloned" and the contents replicated.
This replication will be avoided if certain tables within the now single database can be cloned.
Once a table has been cloned ... it is quite likely to be enhanced ... however it is essential (from an O-O point of view) that they start off as true clones.
Since this appears to be pretty complicated from a raw T-SQL point of view ... I would say that my choices appear to be A) Powershell over SSMO or B) maintain the DDL in my own code.
to answer the Question ... the Data is required to be cloned as well but only for testing purposes.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 22993532
The T-SQL is probably easy enough - almost everything we need is in information_schemas and a couple of other places. the complexity comes into it when there is a potential point of failure because of referential integrity, historical data no longer linked and so forth. Being in the same database is a good start to help minimise those incidences, but they are still real.

Who has control over the cloning ? is it a user invoked action (with tons of controls one would hope given a  typical user) or is it more proceduralised and a formalised process to modify database structures ? Hopefully the latter !

Copying data across is not a big problem when they have the same structure - assuming check constraints and referential integrity rules are still available. So...

Don't reeally like to say it, but, option B is the best - it is selective as to which tables, it serves as a independant reference point for subsequent enhancements, and those DDL scripts can be archived in something like source safe to preserve versioning and as a control point. The other advantage is you can use scripting tools (like ssms wizards and so forth) as your starting point.


Author Closing Comment

ID: 31516958
Well I guess I have been pointed in the right direction

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

834 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