| Author |
Topic |
|
shifis
Posting Yak Master
157 Posts |
 Posted - 2003-05-05 : 15:22:21
|
I have some databases and I want to copy a table from one database to another. But I don't know how  Note: The table do not exist in the other database. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-05 : 15:30:30
|
| several methodsselect * into newtbl from otherdb.dbo.oldtbldtsscript table, run script in new database, bcp data out, bcp data in.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
shifis
Posting Yak Master
157 Posts |
Posted - 2003-05-05 : 17:46:30
|
| Thank you!!I used the instruction:use smartpselect * into TB_VENDOR from smart.dbo.TB_VENDORand it works just fine, thanks a lot!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-05 : 18:02:07
|
| When you use SELECT * INTO, keep in mind that none of the constraints or indexes are copied over. Those will have to scripted out and run on this new table.Tara |
|
|
|
shifis
Posting Yak Master
157 Posts |
Posted - 2003-05-05 : 20:24:34
|
| Thanks for the warning, I will take that on mind. |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-05-05 : 21:14:01
|
| Or you could Use Data Transformation Services which will let you transfer the whole structure and data |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-06 : 10:24:13
|
Is that true? I just did a DTS package to copy a table within the same database to a new table (table2) and only the base structure copied...gotta admit not being well versed in DTS though.Many ways to do things...I would script all of the strucures and save the DDL as a script. Execute that script in the new database.Create a bcp out script and a bcp in script and execute those.You need to make things easily repeatable.Because the biggest lie in IT is:Man/Woman playing IT Director/Manager:quote:
It's only a one time thing
Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-06 : 10:35:02
|
| DTS has lots of options. you can copy data, structure, permissions, etc. whatever you want.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-06 : 11:16:53
|
| Ok Jeff, I played with it....seems pretty powerful (and dangerous though), plus I ran in to all kinds of problems with users not being in the target...It would be neat if it would save the scripts with out executing them...but then again you could just as well script the db object s through EM yourself....It's like MS gives you sooooo much rope you could hang yourself with...PS: Is Nigel on East coast time yet?Brett8-) |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-05-06 : 17:24:16
|
| In DTS you can save your DTS package and then run it on an adhoc basis or schedule it through SQL Agent. I have done this for a couple of things and add extra non-SQL steps to the package. e.g. DTS a query of today's transactions to a CSV, then FTP it to an external party. |
|
|
|
|
How to copy a table to another database