Join for merging records

175 pts.
Tags:
JOIN statement
SQL Server 2005 Express
SQL Server 2005 merge replication
SQL tables
I'm trying to figure out what the best type of join I could use in TSQL for merging records in about 6 tables where there is a match. So I have 6 different tables where there are many matching records. So I figured that I would use a left join in my first join, keep those records that were matched and then seek to join the unmatched records from my first join to my next table and vice versa. Can someone please help? Here is an example of what I'm writing but I'm sure I'm doing something wrong as I get no results. I'm thinking I need parenthesis somewhere here:
SELECT T.*
FROM TABLE1 T
LEFT OUTER JOIN TABLE2 T2
ON T.KEY=T2.KEY
INNER JOIN TABLE3 T3
ON T.KEY=T3.KEY
WHERE T.KEY IS NULL


Software/Hardware used:
SQL Server 2005 Express

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I'm not sure I understand what you want to get as result. Can you post some example data from your tables and the results you would want to get ?
    69,920 pointsBadges:
    report
  • Eabouzeid
    OK, maybe better if I explain practically. I have many different tables where each table represents a scan of the network that I performed gathering information about installed software on our network by computer user. Sometimes an individual may not be logged into the network to scan. I want to therefore merge all of the tables using the serial number of each computer so as to create one final table that has the results of all computers in the company but without duplicates. Thank you for your help Carlos.
    175 pointsBadges:
    report
  • carlosdl
    If I understand correctly, and if this is a one-time operation, an easy (but probably not efficient) way to populate your new table would be creating a unique index on the serial number (if it doesn't exist) with the IGNORE_DUP_KEY option set to ON, and execute an INSERT-SELECT command from a union from your source tables. Something like this:
    CREATE UNIQUE INDEX yourIndex ON yourFinalTable(serial_number) WITH (IGNORE_DUP_KEY = ON)
    
    INSERT INTO yourFinalTable
    SELECT * FROM table1
    UNION SELECT * FROM table2
    UNION SELECT * FROM table3
    ...
    
    ALTER INDEX yourIndex ON yourFinalTable SET (IGNORE_DUP_KEY = OFF)
    69,920 pointsBadges:
    report
  • Eabouzeid
    Thank you Carlos! This worked flawlessly.
    175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following