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?
santa-claraAsked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
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
	)  ON [PRIMARY]
 
SET IDENTITY_INSERT dbo.TableClone ON
 
INSERT INTO dbo.TableClone (IdentityNum, Column1, Column2, Column3)
SELECT IdentityNum+20000, Column1, Column2, Column3 FROM dbo.TableOriginal WITH (HOLDLOCK TABLOCKX)
 
 
SET IDENTITY_INSERT dbo.TableClone OFF
 
DROP TABLE dbo.TableOriginal
 
EXECUTE sp_rename N'dbo.TableClone', N'TableOriginal', 'OBJECT' 
 
CREATE NONCLUSTERED INDEX IX_TableOriginal ON dbo.TableOriginal
	(
	Column3
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
 

Open in new window

0
 
Faiga DiegelSr Database EngineerCommented:
You have to manually script it.

--create constraint
ALTER TABLE dbo.CloneTable
ADD CONSTRAINT [PK_ColumnID]
PRIMARY KEY CLUSTERED (CoulunID)
GO

--drop constraint
ALTER TABLE dbo. CloneTable
DROP CONSTRAINT PK_ColumnID
GO

--reseed indentity
DBCC CHECKIDENT('[dbo].[IDV_CloneTable]', RESEED, 200000)
0
 
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

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

e.g.

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 ?
0
 
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
0
 
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...
0
 
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.
0
 
Mark WillsConnect With a Mentor Topic 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.


0
 
santa-claraAuthor Commented:
Well I guess I have been pointed in the right direction
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.