Help with inserting new records+updating existing

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

SQL Server Development (2000)

Help with inserting new records+updating existing

Author

Topic

Qualm
Starting Member

7 Posts

Posted - 2004-03-10 : 16:09:19

I'm not very experienced with SQL, and I need some help in figuring out the best way to do this:

We have a database which contains a table (called Cases) whose records have a 3-field primary key (lets call them Fieldx, Fieldy, Fieldz).

Each week we get a text file which contains updates to this database, BOTH entirely new rows AND updates (actually replacements for) existing rows.

I need some pointers as to the correct way to update the base datatable with the new/updated rows. Would that be:

1. Delete the records from the existing table where the primary key of the existing table matches the primary key of the weekly update table, and then insert the entire weekly update table into the base table? What would the outline of the SQL code look like for that?

2. Do a some kind of "INSERT with SELECT" query (mentioned in my SQL reference book) that adds the records of the second table into the first? Would that do the row updating properly, or would it error out on duplicate primary keys?

3. Do a fancy join or sequence of joins combined with a truncate to get the rows combined into one table?

4. Do something else I'm not aware of ...

Any insight is appreciated.

- Qualm

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-10 : 16:40:14

I knew I had this lieing around somewhere

Basically take your file and load it to a staging table...

This is assuming you get all of the data, because it also processes deletes...you can easily take that step out..

Cut and paste the code, it should run as is...


USE Northwind
GO

SET NOCOUNT ON
GO

SELECT * FROM Employees

-- Create some test data

SELECT * INTO EMP1 FROM Employees  -- Pretend this is the New File
SELECT * INTO EMP2 FROM Employees  -- Pretend this is the database table that needs to be acted upon
GO

-- Create some changes
DELETE FROM EMP1 WHERE EmployeeID = 1
INSERT INTO EMP1 (LastName, FirstName) SELECT 'Kaiser','Brett'
UPDATE EMP1 SET LastName = 'White' WHERE EmployeeId = 4
GO

-- Take a Look
SELECT * FROM EMP1

-- Find New records

    SELECT l.* 
      FROM EMP1 l 
 LEFT JOIN EMP2 r 
	ON l.EmployeeId = r.EmployeeId 
     WHERE r.EmployeeID IS NULL

-- Find deleted records

     SELECT r.* 
       FROM EMP1 l 
 RIGHT JOIN EMP2 r 
	ON l.EmployeeId = r.EmployeeId 
     WHERE l.EmployeeID IS NULL
SELECT * FROM EMP1
SELECT * FROM EMP2


-- Find Updated Records

    SELECT  * 
      FROM EMP1 l 
INNER JOIN EMP2 r 
	ON l.EmployeeId  =  r.EmployeeId
     WHERE ( l.LastName  <> r.LastName
        OR   l.FirstName <> r.FirstName)
GO

-- Add New reocrds
INSERT INTO EMP2 (LastName, FirstName) 
     SELECT l.LastName, l.FirstName 
       FROM EMP1 l 
  LEFT JOIN EMP2 r 
	 ON l.EmployeeId = r.EmployeeId 
      WHERE r.EmployeeID Is NULL

-- Delete missing
DELETE FROM EMP2
WHERE EmployeeID IN (
    SELECT r.EmployeeID  
      FROM EMP1 l 
RIGHT JOIN EMP2 r 
	ON l.EmployeeId = r.EmployeeId 
     WHERE l.EmployeeID Is NULL)

-- Update the changes ....update all columns for simplicity
UPDATE r
  SET r.LastName  = l.Lastname
    , r.FirstName = l.Firstname
      FROM EMP1 l 
INNER JOIN EMP2 r 
	ON l.EmployeeId = r.EmployeeId
     WHERE ( l.LastName  <> r.LastName
        OR   l.FirstName <> r.FirstName)
GO

-- Take a peek
   SELECT * FROM EMP1
UNION ALL
   SELECT * FROM EMP2
 ORDER BY EmployeeID
GO


-- Clean up this mess

SET NOCOUNT OFF

DROP TABLE EMP1
DROP TABLE EMP2
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources