Linking Servers and querying same column names

• John Owen-439066

  SSC Enthusiast

  Points: 139

  More actions

  May 11, 2007 at 12:40 am

  #176348

  Ok, nothing says newbie like jumping in with both feet. 

  Some of this is probably old news but as a whole I haven't been able to put it together.  I know enough about sql to get what I need done most of the time.  Today was different, I spent 14 hours recovering a server yesterday and I had no business working on the sql server first thing this morning.  I was just too damn tired and failed to check and recheck my query before hitting go. 

  Here's what I did and what I need to accomplish.  I updated a table incorrectly.  by that I mean I updated every record instead of the target records.  I have a backup of the DB that I can restore to a second sql server so all is not lost except for a few hours that I will update manually. 

  I need a query to select 2 columns (Materials_ID and Incident_Materials_ID) from a linked server and 2 columns (same names) from the production server.   The query will match Incident_Materials_ID columns and update the Production Materials_ID with what is in the linked server Materials_ID column.

  I haven't even gotten to the update part, I can't even get all 4 columns on the same query yet.

  If I could get Help with this I would be indebted to you all! 

  Oh man, hitting post new topic is a humbleing feeling...

   

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  May 11, 2007 at 2:40 am

  #705745

  1. Create a linked server

  2. Use the four part naming convention to access the remote server.

  3. Use table alais for refer the table and column names as you say that both are same in target and source databases.

  4. write a update that checks for matching records and updates the required fields.

  i hope with this you are done with.

  any questions please let me know.

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com

• John Owen-439066

  SSC Enthusiast

  Points: 139

  More actions

  May 11, 2007 at 7:03 am

  #705802

  Thank you.  I can pull the data from each DB individually but my join is failing.  Here is the basic select to see the data. 

  can you help me with the syntax for the join? Its from the second select that I want to run the update on. 

  SELECT TOP 10 offline.nc_incident_materials_id AS offline_inc_materials_id, offline.nc_materials_id AS offline_materials_id

  FROM dev.offline_ed.dbo.nc_incident_materials AS offline

  SELECT TOP 10 nc_incident_materials_id, nc_materials_id

  FROM nc_incident_materials

  If you need any information, I can get it. I just don't know what you may need.

   

  Thank you,

  John

• John Owen-439066

  SSC Enthusiast

  Points: 139

  More actions

  May 11, 2007 at 9:21 am

  #705859

  Ok.  I have been working on this.  First, I do want to learn but I need to speed this along which is why I am posting here.  I am not just looking for a hand out. 

  I have my select query working.  Now I need to create the update.  Any pointers?  Last time I ran an update I got it wrong so I want to avoid that this time.

  SELECT

   offline.nc_incident_materials_id AS offline_inc_materials_id

  ,  offline.nc_materials_id AS offline_materials_id

  ,  test.nc_incident_materials_id AS test_inc_materials_id

  ,  test.nc_materials_id AS test_materials_id

  FROM  dev.offline_ed.dbo.nc_incident_materials AS offline

  ,  nc_incident_materials AS test

  WHERE offline.nc_incident_materials_id = test.nc_incident_materials_id

   

  After each post I make I start to pick it apart and it actually helps me figure things out.  Like the last one, I knew 5 minutes after posting it what I needed to do.  Hopefully that will continue.