Appending new records only - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Appending new records only

Author

Topic

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-03-12 : 15:05:53

I am trying to append just new records to a history table. The primary key is made up of 2 columns, so it makes it challenging to determine which records are already in the table.

Here is the scenario (simplified structure):

'table_stage' - I import records from a .csv file into this staging table

department_id
employee_id
employee_notes

Primay Key consists of the department_id and the employee_id.

The history table 'table_hist' has the same structure (the only difference is that I archive all data in here).

Sometimes the .csv file has records that I have previously seen and appended to the history table.

Here is my INSERT INTO statement.

INSERT INTO table_hist
(department_id, employee_id, employee_notes)

SELECT department_id, employee_id, employee_notes
FROM table_stage
WHERE 'department_id + employee_id' NOT IN (select 'department_id + employee_id' from table_hist)

So, I am concatenating the 2 fields which make up the primary key to do the check for new records. Is there a better way to do this (I know my query is not as efficient as it could be). If the table's primary key was 1 column, this would be a no brainer for me.

My main concern here is performance. What I am doing works, but not sure how slow it will be once my history table has thousands of records.

Thanks,

Jack

Rome was not built overnight.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-12 : 15:22:59

yes! A much better way -- LEFT OUTER JOIN :


INSERT INTO table_hist
(department_id, employee_id, employee_notes)
SELECT department_id, employee_id, employee_notes
FROM table_stage

LEFT OUTER JOIN table_hist
ON table_stage.department_id = table_hist.department_id AND
   table_stage.employee_Id = table_hist.employee_id
WHERE
   table_hist.employee_id IS NULL

Review how LEFT join's work if you are hazy,and you will see the logic and how this checks for existing rows first.

- Jeff

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-03-12 : 15:52:27

I really appreciate your help.

I tried a similar thing (using the old SQL standard joins):

INSERT INTO table_hist (department_id, employee_id, employee_notes)
SELECT department_id, employee_id, employee_notes
FROM table_stage S, table_hist H
WHERE S.department_id *= H.department_id
AND S.employee_id *= H.employee_id
AND H.department_id IS NULL

Instead of the INNER / OUTER JOIN syntax and it was not working for me - that's when I gave up and did the concatenation. I guess I was being lazy in not using the OUTER JOIN syntax.

--Jack

Rome was not built overnight.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources