SELECT INTO cross server 1

[nickdel]

Can I do a Select Into across 2 DB servers?

Nick

 

[blom0344]

Check out 'linked servers'.
You can use sp_addlinkedserver "server name" to create a linked server.

To run a query over a linked server

select * from [server name].[database name].[owner name].[table name]

Ties Blom

 

[TysonLPrice]

I tried it and I got a message about too many qualifiers.

Server: Msg 117, Level 15, State 1, Line 3
The object name 'Server.database.owener.table' contains more than the maximum number of prefixes. The maximum is 2.

I don't know how else to qualify it. What happens when you try it?

What about using DTS?

 

[PerlyGates]

Yes you can.

Qualify your server name with square brackets e.g.

SELECT *
  INTO newTable
  FROM  [red][Server][/red].database.owener.table

 

[TysonLPrice]

I did...it just wasn't in the error message. Try it. Maybe it's just me.

Here's what I tried:

select top 1 * into
[devsql\tpa1].manhattan..claim
from claim

Both the same layouts.

 

[nickdel]

Yeh I'm getting the same error as Tyson. Is this realiant on running sp "sp_addlinkedserver" because I dont have permissions to run it on the primary server.

Nick

 

[TysonLPrice]

You would get a different message if they were not linked.

You can use the import/export wizard to do it.

 

[blom0344]

AFAIK when using the wildcard * combined with insert statements, then you need to work with an alias.

select top 1 G.* into
[devsql\tpa1].manhattan..claim
from claim G

or

insert into 
[devsql\tpa1].manhattan..claim
select top 1 G.* from claim G

Ties Blom

 

[PerlyGates]

nickdel - yes you do have to set up the link to the other server - sorry my fault but I thought that was obvious. And yes you'll need permissions.

TysonLPrice - you're query is the wrong way around it should be (assuming you have the link to the other server set up):

SELECT *
  INTO newTABLE
  FROM [linkedservername].database.owner.tablename

- Apologies

 

[TysonLPrice]

Scratch what I said about the message. Just try and select top 1 from the fully qualified server in the other DB. That will tell you if they are linked. You can also see the "sharing hand" from EM if it is.

 

[gmmastros]

select top 1 * into
[devsql\tpa1].manhattan..claim
from claim

[!]Both the same layouts.[/!]

Both the same layouts? Well then, that won't work.

Select into only works if the table you are selecting in to does not already exist. If the table already exists, then you need to use insert into. Like this...

[tt][blue]Insert Into [devsql\tpa1].manhattan..claim
Select * From claim[/blue][/tt]

Note the * in the previous example. In my opinion, this is poor coding. The * syntax only works of the table structures are the same, including the column order. I don't like relying on column order, so I always specify the columns myself.

[tt][blue]
Insert
Into [devsql\tpa1].manhattan..claim
(Col1, Col2, col3)
Select (Col1, Col2, Col3)
From claim[/blue][/tt]

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

gmmastros,

Thanks...I would have got to that point if I was really attempting to insert a record. All I was trying to do at the time was to cross servers and never got past the other errors.

I think the other posters answered the OP's question.

Always keeping an eye out

 

[ESquared]

First, devsql\tpa1 looks like a direct sql\instance name, not a linked server name. Are you sure this is actually the name of a linked server you created on the destination server?

You can add a linked server not just with sp_addlinkedserver but also through Enterprise Manager/Management Studio. You should know how to do it with sp_addlinkedserver, though.

Second, if you run into distributed transaction problems (unable to start DTC or some similar error), you might try creating the table first and doing an insert into instead of using a select into. Of course you could turn on DTC on both servers if you absolutely need to do things like joins between servers. If either of the servers are clustered it gets even more complicated.