Linking identical tables in different databases...

• Steve Smith-133935

  SSC Rookie

  Points: 30

  More actions

  March 3, 2005 at 7:26 am

  #162880

  Hi,

  I've got two databases that are identical apart from one is the live, and one is the archive.

  My problem is that I need to link two identical tables from both databases so it appears seamlessly as one.

  Does anyone have any ideas on how to achieve this?

  Thanks

  Steve

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 3, 2005 at 4:15 pm

  #545361

  Have you consider a view with union in it?

• Jim Kitterman

  SSC Veteran

  Points: 201

  More actions

  March 3, 2005 at 5:05 pm

  #545370

  You can use a union query here is an example

  SELECT * FROM Database1.dbo.TableName

  UNION

  SELECT * FROM Database2.dbo.TableName

• Steve Smith-133935

  SSC Rookie

  Points: 30

  More actions

  March 4, 2005 at 12:30 am

  #545391

  Thanks for replies.

  I've tried a union but keep getting a collation error.

  Steve

• sholliday

  SSCrazy

  Points: 2100

  More actions

  March 4, 2005 at 8:31 am

  #545496

  There is a way to specify the collation with the query.  I dont have books online at work with me (I'm in an oracle place right now).

  I suggest

  Make sure you run sp_linkserver (?? spelling?)

  I think the query goes something like

  select f1, f2, f3 from table1 with LATIN_1

  or something like that.  that is probably wrong, but might help.

  You'll need to "match" collations if you have different defaults on the 2 different machines.

  If you're in a dotnet world, you could

  DataSet ds1 = getDataFromDB1();

  DataSet ds2 = getDataFromDB2();

  ds1.Merge(ds2);

  something like that.