Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Serge
Starting Member
44 Posts |
 Posted - 2007-11-19 : 10:47:35
|
| HiI need do a SELECT statement from one table in one DB and insert the results in to another DB which is on the other server. Is this possible at all and if so how do I do it. I tried full naming convention and its not working.Thanks a lot |
|
|
hitman
Starting Member
23 Posts |
Posted - 2007-11-19 : 10:49:32
|
| You should create a linked server.-----------------------http://www.sqltips.info----------------------- |
 |
|
|
Serge
Starting Member
44 Posts |
Posted - 2007-11-19 : 10:59:58
|
| Excellent, thanks! |
|
|
|
Serge
Starting Member
44 Posts |
Posted - 2007-11-19 : 11:10:37
|
| I just had a look and one can use Import Export wizard in SQL2005, is there are a way of writing a script that will do this instead of using GUI wizard? |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-19 : 11:35:22
|
quote:
Originally posted by Serge
I just had a look and one can use Import Export wizard in SQL2005, is there are a way of writing a script that will do this instead of using GUI wizard?
Yes, use SP_ADDLINKEDSERVER to add a linked server to the other DB and then build the SELECT INTO... script referencing the table in the linked server using the proper fully qualified name: <servername>.<databasename>.<schemaowner>.<tablename>
Future guru in the making. |
|
|
|
Serge
Starting Member
44 Posts |
Posted - 2007-11-19 : 11:41:18
|
| I thought that would be the case behind Import/Export wizard which at the end of the query removes the linked server! Is my understanding correct? |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-19 : 12:16:33
|
quote:
Originally posted by Serge
I thought that would be the case behind Import/Export wizard which at the end of the query removes the linked server! Is my understanding correct?
Not positive, but it is likely. If you want to be sure you can turn on profiler and trace the steps.
Future guru in the making. |
|
|
|
Serge
Starting Member
44 Posts |
Posted - 2007-11-20 : 09:33:46
|
| Cool, thanks! |
|
|
|
|
|
|
Copying records across DB's using TSQL