How to Join 2 tables in to 1

bex · June 28th, 2009, 05:14 AM

I Have 2 SQL Sql Servers one is sql2000, the other sql2005.

SQL2000 is being updated every day, I Need this updates to be imported to sql2005
The way i try to do it is by running a Import Data Wizard on SQL 2005, after the wizard has finished importing data i see New record are addet to the database in sql 2005 instead of updating the existing ones.

Lets say SQL2000 database table1 has 5 records , i run the import data wizard and the 5 records are imported to SQL2005 database table1, now a new record is being inserted in sql2000, next time i run import wizard 6 records are being added to sql2005 so from 5 records it goes to 11
What i Need is a script that searches the database in sql2000 for any changes and imports them to SQL2005.

gbianchi · June 28th, 2009, 09:15 AM

That happens because it is an "import wizard", it's not made to update the table every time you receive new info, you should be running it only once... Anyway, I don't understand how can I help you...

bex · June 28th, 2009, 11:36 AM

can you point me in the right direction to pull only the updates...

also can Replication be Implemented between sql2000 and sql 2005?

gbianchi · June 28th, 2009, 01:55 PM

No, you will have to check out google to find the method that suits better for your scenery... I don't have a clue of what you have or what you need...

Imar · June 28th, 2009, 02:07 PM

Hi Bex,

You may want to get one of these two books:

http://www.wrox.com/WileyCDA/WroxTit...470247959.html
http://www.wrox.com/WileyCDA/WroxTit...764584359.html

They deal with Integration Services for SQL Server 2005 and 2008 and show you how to accomplish imports like this.

There are many ways to do what you need to do. A simple solution is to import all records in a temp table, then delete the ones from the original table that have a record in the temp table and then insert anything from the temp table to the destination table.

Integration Serives offers may other tasks that allow you to work on a row by row basis making it possible to do something like this:

IF EXISTS (SELECT FROM SomeTable WHERE Id = IdThatsBeingImported)
Update existing record
ELSE
Insert new record
END IF

You need a least a basic understanding of T-SQL and SQL Server to accomplish this; no wizards here.... ;-)

http://www.wrox.com/WileyCDA/WroxTit...764584340.html
http://www.wrox.com/WileyCDA/WroxTit...764543792.html

Cheers,

Imar

bex · July 8th, 2009, 11:13 AM

I managed to do this:

Code:

IF(EXISTS(SELECT AccountNumber
FROM SimOverride 
AS t1 
WHERE t1.AccountNumber ='G111'))
BeginSelect SLP05.CNAM05, SLP05.CUSN05 , SLP05.CAD205 , SimOverride.SimNet, SimOverride.Cost, SimOverride.Commission, SimDefault.SimNet, SimDefault.Cost, SimDefault.Commission From SimOverride CROSSJOIN
SLP05 CROSSJOIN
SimDefault
WHERE
(SLP05.CUSN05 ='G111')
End
Else
BeginSelect
SLP05.CNAM05, SLP05.CUSN05, SLP05.CAD205, SimDefault.SimNet, SimDefault.Cost, SimDefault.Commission FROM SimDefault CROSSJOIN SLP05 Where(SLP05.CUSN05 ='G111')
End

But this Selects every thing from SimDefault and Sim Override, What i need is Select what is in SimOverride then go to SimDefault and get All minus what it took from SimOverride.

The else Is OK ,

Thanks for looking

peace95 · July 8th, 2009, 12:57 PM

bex:

When I join tables, I have a higher success rate using either a RIGHT or LEFT Joins. Could you send the scheme layouts of the tables depicting their Primary Keys and Foreign Keys, not the entire layout? I think I can get the "to-be"Table from previous posts.