Copy table into linked server

Question

Copy table into linked server

gugan_ta

SSCommitted

Points: 1649
More actions
September 17, 2009 at 1:01 am

#204695

Hi,
I have to copy a table from server1 to server2.
created the SP where table frm server1 should be copied to server2 (linked server in server1)
have tried the following query but it showing following msg..
Select * into "server2".dbname.dbo.tablename from tablename
"The object contains more than the maximum number of prefixes. The maximum is 2."
Note :
The entire process should be performed in server1.
Pls help how to perform the above process.
Thanks..Gugan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

gugan_ta (9/17/2009)
Hi,
I have to copy a table from server1 to server2.
created the SP where table frm server1 should be copied to server2 (linked server in server1)
have tried the following query but it showing following msg..
Select * into "server2".dbname.dbo.tablename from tablename
"The object contains more than the maximum number of prefixes. The maximum is 2."
Note :
The entire process should be performed in server1.
Pls help how to perform the above process.
Thanks..Gugan

Select * into "server2".dbname.dbo.tablename from tablename

Just remove the double quotes around server2. This query will insert data into tablename stored on server2 from current server.

Pradeep Singh

gugan_ta SSCommitted Points: 1649 More actions · Answer 2

Hi,

It gives the same error...

Pls provide alternate..?

Gugan

ps. One Orange Chip Points: 29422 More actions · Answer 3

gugan_ta (9/17/2009)
Hi,
It gives the same error...
Pls provide alternate..?
Gugan

can u post the error message?

The other way is to:

1. create a linked server on server2 pointing to server1. from there insert the data

select * into mytable from server1.dbname.dbo.mytable

2. import the table data

3. bcp out data from source table and then bcp in to target database's table.

4. create ssis package.

Pradeep Singh

Lowell SSC Guru Points: 323518 More actions · Answer 4

you cannot create a table on the fly across a linked server. the select into...from tries to do that.

you can only insert into tables that exist.

so INSERT INTO "server2".dbname.dbo.TableThatExists

select * from tablename

will work.

the linked server only allows Data Maipulation(DML), and not object creation (DDL)

hope that helps!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ps. One Orange Chip Points: 29422 More actions · Answer 5

Thanks Lowell. That didnt click to me :w00t:

Pradeep Singh