Insert Into Select From Databases on 2 Servers

• Michael Brennan-White

  Grasshopper

  Points: 23

  More actions

  September 14, 2004 at 8:47 am

  #59979

  Please excuse the bizarre title.

  I am trying to do what should be a simple task in Query Analyzer.

  Insert into [Server1 Database]

  Select * from [Server2 Database] where trandate = '2004-09-13'

  This type of statement works if I do it on a database on the same

  server but it will not accept the IP address of the servers in the

  statement.

  What am I missing?

  Sincerely,

  Michael

• sholliday

  SSCrazy

  Points: 2100

  More actions

  September 14, 2004 at 3:39 pm

  #523076

  Well.  this will help:

   

  select * from DBXServ.pubs.dbo.titles

  where DBXServ is ~~another server.

   

  Check out the

  "Identifying a Data Source Using a Linked Server Name" in Sql Server 2000 BOL.

  and

  "Inserting Rows Using SELECT INTO"

  and

  "How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)"

   

  ----------------------

  dbo is the "owner"  ... when browsing tables in enterprise manager, you will see the owner name listed.  this is a part of the fully qualified name.

  You might have to link a server before hand. 

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  September 15, 2004 at 12:40 pm

  #523228

  You should use the Client Network Utility on Server 1 to alias the remote Server 2.

  If you are using TCP/IP address as Server 1 name, enclose it in square brackets in the 4-part qualification:

  e.g. Insert into dbname.dbowner.tablename select * from [nnn.nnn.nnn.nnn].databasename.schemaname.tablename

• DanMcClain

  SSChasing Mays

  Points: 620

  More actions

  September 16, 2004 at 8:30 am

  #523348

  if your using the IP address ( xxx.xxx.xxx.xxx ) simply quote the IP address. This can be done by [xxx.xxx.xxx.xxx]   or  quotename('xxx.xxx.xxx.xxx').  Hope this helps.

  Dan

   

• Michael Brennan-White

  Grasshopper

  Points: 23

  More actions

  September 16, 2004 at 8:39 am

  #523352

  Thanks for all the replies.

  I got it to work by making it a linked server. I found the wizard to be less than intuitive but I got it to work.

  Thanks again,

  Michael