| Author |
Topic |
|
Pasi
Posting Yak Master
166 Posts |
 Posted - 2014-05-08 : 17:00:01
|
HI All,Wondering if its possible to copy a table form one DB and paste to another dB? is there a tool for that? I guess copy past out of questions? Thanks,Pasi |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-08 : 17:10:10
|
| This will grab table definition and data:select *into DB2..Table1from DB1..Table1If you need to copy just the table definition, then add WHERE 1=0. If you want indexes, constraints, etc, then you can script those.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2014-05-08 : 17:31:27
|
Thanks Tara!quote:
Originally posted by tkizer
This will grab table definition and data:select *into DB2..Table1from DB1..Table1If you need to copy just the table definition, then add WHERE 1=0. If you want indexes, constraints, etc, then you can script those.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2014-05-08 : 17:35:13
|
Do I need the dots.......Pasiquote:
Originally posted by tkizer
This will grab table definition and data:select *into DB2..Table1from DB1..Table1If you need to copy just the table definition, then add WHERE 1=0. If you want indexes, constraints, etc, then you can script those.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-08 : 17:37:21
|
| The dots are due to using the 3-part naming convention. If your tables are owned by dbo, then DB1..Table1 is equivalent to DB1.dbo.Table1. You do need the dots for the remote table as you need to use the 3-part naming convention for a remote object on the same SQL instance. For a remote object on a remote SQL instance, you need the 4-part naming convention: ServerName.DatabaseName.SchemaName.ObjectName.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2014-05-08 : 17:53:27
|
ok got it thanks. didn't know it was just an writing expression or else?Pasiquote:
Originally posted by tkizer
The dots are due to using the 3-part naming convention. If your tables are owned by dbo, then DB1..Table1 is equivalent to DB1.dbo.Table1. You do need the dots for the remote table as you need to use the 3-part naming convention for a remote object on the same SQL instance. For a remote object on a remote SQL instance, you need the 4-part naming convention: ServerName.DatabaseName.SchemaName.ObjectName.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-05-08 : 17:55:45
|
| If you have Sql Management Studio (SSMS) you can use the Import/Export functionality to transfer data/tables between servers/databases. Just right-click on a database and go to Tasks -> Import Data or Export Data. |
|
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2014-05-09 : 12:00:31
|
Thanks Lamprey! I will try that instead of restoring dB. I haven't done this before but would it ask you what table you want to export to where? cause as I accidently deleted the row on a table called user_pref, would this option good for me to restore the table back from one dB to another?Pasiquote:
Originally posted by Lamprey
If you have Sql Management Studio (SSMS) you can use the Import/Export functionality to transfer data/tables between servers/databases. Just right-click on a database and go to Tasks -> Import Data or Export Data.
|
|
|
|
|
moving one table form db to another dB