copy a table over to a linked server by ''SELECT ...INTO''
-
I'd do it the other way around IE:
your way...
select *
into beta.work.dbo.Table_backup
from _all_props
my preference...
select *
into dbo.Table_backup
from SourceServer.SourceDatabase.DatabaseOwner._all_props
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
try like this
select *
into beta.work.dbo.Table_backup Table_backup_1
use alias for link server
goppo
romania
-
Why would the alias make this work (even if it does, I don't see why it is required)??
-
The alias doesn't work. There is no way to execute SELECT INTO server.db..tbl.
If you want to copy a table from server A to server B, you could connect to server B and use:
select * into db..tbl from A.db..tbl
A sneaky way to do this from server A is to tell server B to execute that command
exec B.master.dbo.sp_executesql N'select * into db..tbl from A.db..tbl'
-
Yup, that's almost as sneaky as Noeld's solution to join to stored procs
. -
The way I accomplish this is create the same table on the other server and execute an insert statement.
delete from TableB (if needed)
insert into TableB select * from ServerA.DatabaseA.dbo.TableA
-
He wants to do it the lazy way >> no create statement... but I know you're right
. -
Remi what do u think ab this?
As the error message states, the SELECT/INTO form of the select clause just doesn't work using anything more than a 2 part naming convention. You will need to do something like the following from the Beta server...
SELECT *
INTO dbo.Table_backup
FROM Alpha.Work.dbo._all_props
That may require you to setup another linked server so that BETA can read from ALPHA.
--Jeff Moden
-
This is the simplest solution for this. however this is still the wrong method of doing this. There comes a point where laziness doesn't pay up. Create the table then insert into it. Doesn't take long to get a copy of the local table script and execute it on the other server.
-
I tried to do this without setting up a linked server (i.e. my own machine) on beta - it does not work. I have an administrative right on Beta, so I sh be able to create the linked server there that would point to my machine. I'm going to try this.
-
at least I learnt smth new as a result of this exercise
Thanks to everyone for yr time!
-
Glad we could help.
-
Serqei,
You cannot do this:
select *
into beta.work.dbo.Table_backup
from _all_props
instead connect to the remote server and run this:
select *
into Table_backup
from servername.dbname.dbo._all_props
also make sure table_backup does not already exist because it gets created with select into.
Jules Bui
IT Operations DBA
Backup and Restore Administrator
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply