cloning a table
-
Hi,
I have a populated table.
I’d like to “clone” it,
ie if the table is called “tblOriginal”
I’d like to create another table called “tblCloned”
Which is identical in everything but name.
I’ve tried to:
1) rightClick > copy > then paste in EM,
2) copy the table in the EM “Diagrams” section.
But it doesn’t work.
I initially created “tblOriginal”thru EM.
Do I need to manually create a table in EM that has the same schema as that of “tblOriginal”, and then use a SELECT INTO statement to populate it?
Seems like the long way.Id like to be able to do it in code, thru QA, but I’ve checked all the “sp_’s” and can’t see a suitable one.
Is this possible thru QA and t-sql?
cheers,
yogiberricus
-
As you surmised you will want to use SELECT INTO...
SELECT
<fieldlist>
INTO <newtablename>
FROM <oldtablename>
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
If you want to use EM, you can use the Import/Export wizard. The source and target database would be the same, then select your table in the dialog and change the destination name in the grid. Both the structure and data get copied.
Alternatively, you could right click the table, select All Tasks, Generate SQL Script. That'll pop the SQL into a file, and you can load that into QA. You'd still have to do an INSERT... SELECT to populate the data.
Programmatically in QA/T-SQL? That's some more research.
Does that help?
SJTerrill
-
aye folks,
that's great.Though I am surprised that there isn't an easy way to do it all thru t-sql..probably why i couldn't find it.
ta,
yogi
-
Yogi,
Doing the Select <fieldlist> INTO <newtblname> FROM <originaltable> will create the table for you with the same structure. It won't however create any constraints or indexes. Often times I will do the select into with a where clause of "WHERE 1 = 2" to just create the structure and then do a normal insert statement.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Hi Gary.
It's certainly a lot less hassle to simply add constraints to a ready populated table than it is to build one from scratch.Cheers for the tip
BTW I was just wondering...do you normally manually create sprocs to create your tables?
Ta,
yogi
-
I usually do all my data modeling in Erwin. It then creates a script that I modify to create the tables and triggers. Once I have run the script I then create my basic stored procedures for inserting, updating, deleting using a vb app I created many years ago. All the SPs that have specific business rules I create manually from within Visual Studio against my development server. Once I have it working correctly I get a code review and sign off from testing and then run the script on the production machine.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Righto,
I'd say I've a bit of upgrading to do on my dev process.
Ta,
yogi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply