April 11, 2002 at 5:51 am
How can I copy tables with SQL Server with the create table statement of TSQL. I could find a similar statement for Oracle but unable to do so in SQL Server. Is there any turnaround? Please suggest
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 11, 2002 at 6:23 am
I normally script the table out as a create, then change the name of the table in the script. If you have Access 2000, you can run it in project mode, copy the table, click paste to make a copy.
Andy
April 11, 2002 at 11:39 am
I am trying to make a backup utility where I will create an xml file from the source end and restore it on the destination end. I want to create new tables as Temp_myTable and start inserting all data from XML file to the temp tables. I have triggers on temp tables which will validate my data being inserted and would then insert or update into the main tables. So I want the script to create the temp tables exactly as the main tables. So how do I do it the best way?
quote:
I normally script the table out as a create, then change the name of the table in the script. If you have Access 2000, you can run it in project mode, copy the table, click paste to make a copy.Andy
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 16, 2002 at 8:05 am
Are you simply wanting to copy the table structure over, Then import data? If so,
You can:
Select * into <New table Name> From <Current Table> Where 1 = 2
This will create the new table without the data. If you are wanting the data the Select into will copy the table and data. -jG
-JG
-JG
April 16, 2002 at 11:57 am
Thankx, but by using this I am not able to copy the constraints of the tables. How is it possible to do that too? What I want is the replica of the same table with a different name.
quote:
Are you simply wanting to copy the table structure over, Then import data? If so,You can:
Select * into <New table Name> From <Current Table> Where 1 = 2This will create the new table without the data. If you are wanting the data the Select into will copy the table and data. -jG
-JG
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 16, 2002 at 12:40 pm
One quick way is to use Enterprise Manager and selecting / right clicking on the table, choose Task and then Generate SQL Scripts.
There are options on what you want to script.
In addition, you can simply right click the table and select copy. Go to Query Analizer and right click and select paste.
-JG
Edited by - jgee on 04/16/2002 12:53:20 PM
-JG
April 16, 2002 at 12:58 pm
🙂 yeah ... but how to do it programatically... as I have to make a backup utility for my product.
quote:
One quick way is to use Enterprise Manager and selecting / right clicking on the table, choose Task and then Generate SQL Scripts.There are options on what you want to script.
In addition, you can simply right click the table and select copy. Go to Query Analizer and right click and select paste.
-JG
Edited by - jgee on 04/16/2002 12:53:20 PM
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 16, 2002 at 1:08 pm
SQL-DMO would do the trick. See Script Method under Index on BOL.
-JG
-JG
April 17, 2002 at 11:47 pm
Yeah I tried this... this is too good! But the problem now I face is how to change the table name and the contraints name.
quote:
SQL-DMO would do the trick. See Script Method under Index on BOL.-JG
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 18, 2002 at 12:25 am
the copy table you're trying to create off of the original table, does it need to have the data in it, or just the table schema?
April 18, 2002 at 5:46 am
Yes only the schema but with all the table constraints.
quote:
the copy table you're trying to create off of the original table, does it need to have the data in it, or just the table schema?
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 22, 2002 at 4:21 pm
I feel that my question is along the same line with this topic:
How do I copy some (not all) rowsets from one table to another at different server?
-Ben
Benny Tjahjono, SM'95
April 22, 2002 at 4:29 pm
DTS is the easiest way, hides most of the work. You can bcp it out and then in on the other server. Or you can set up a linked server and do something like this:
insert into server.database.owner.table2(fld1, fld2, etc) selecdt fld1, fld2, etc from table1 where condition=whatever
Andy
April 25, 2002 at 11:05 am
DTS has 1 disadvantage as it does not create tables with the same constraints as the original table.
quote:
DTS is the easiest way, hides most of the work. You can bcp it out and then in on the other server. Or you can set up a linked server and do something like this:insert into server.database.owner.table2(fld1, fld2, etc) selecdt fld1, fld2, etc from table1 where condition=whatever
Andy
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply