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

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
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)
santa-claraAuthor Commented:
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
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
>>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.
santa-claraAuthor Commented:
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
Anthony PerkinsCommented:
>>since SSMS is able to generate the SQL required.<<
Than why don't you use the same SQL script SSMS uses?
santa-claraAuthor Commented:
Please don't give me that "why do you want to do that?" whine
I want to dynamically clone a table.
Anthony PerkinsCommented:
Fair enough.
Mark WillsTopic AdvisorCommented:

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 ?
santa-claraAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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...
santa-claraAuthor Commented:
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.
Mark WillsTopic AdvisorCommented:
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.

santa-claraAuthor Commented:
Well I guess I have been pointed in the right direction
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.