Join for merging records
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:
Software/Hardware used:
SQL Server 2005 Express
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
ASKED:
July 1, 2010 12:08 PM
UPDATED:
July 12, 2010 2:58 PM
Approved Answer - Chosen by MelanieYarbrough
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)
Other Answers:
Latest Answer Wiki Contributors:
To see other answers submitted to the Answer Wiki: View Answer History.
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 ?
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.
Thank you Carlos! This worked flawlessly.