?
Solved

What is the best way to syncronize tables? -with a hitch-

Posted on 2004-09-23
9
Medium Priority
?
299 Views
Last Modified: 2006-11-17
OK Experts, I need your help again.

I am new to MS SQL.  What is the best way to sncronize tables?  I have 1 table(Employee) that has a field called EmployeeNumber as it's primary key.  I have another table(Collect) that has the same field but a different primary key.  I would like to set it up so that every time a new record gets added to the Employee table The EmployeeNumber gets added to the collect table as well.  

I have a procces that runs monthly it delets all of the records in the employee table then adds a whole new record set.  When this happens, I would like it to add new records to collect but not delete any.

I need one more thing to happen but I can continue that in a new question If you experts think that is best and I don't know how difficult this all is.  I need SQL server to periodically compare the two tables and remove any records that exist in the collect table that do not exist in the Employee Table.

If I am not explaining this well please ask questions.  I know what I want, but only a very vague idea of how to do it.

Thank You!
~David
0
Comment
Question by:dtolo
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 1200 total points
ID: 12134501
Triggers are used to automatically copy data to a second table when data is inserted in the first one. Here's a sample:

CREATE TRIGGER trig_Emp ON Employee FOR INSERT AS
BEGIN
  INSERT Collect(EmployeeNumber)
  SELECT EmployeeNumber FROM inserted
END

I'll post something comparing and deleting unmatched records in a few minutes (if no one else beats me to it).
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12134619
okay, this will compare the two tables and remove records from Collect if there isn't a matching record in Employee:

DELETE c
FROM Collect c
 LEFT JOIN Employee e ON c.EmployeeNumber = e.EmployeeNumber
WHERE e.EmployeeNumber IS NULL
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 12134622
You also wanted to insert only if it didn't already exist:


CREATE TRIGGER Employee_Trg_Ins
ON Employee
FOR INSERT
AS
INSERT INTO Collect
SELECT EmployeeNumber
FROM inserted
WHERE NOT EXISTS (
    SELECT 1
    FROM Collect
    WHERE Collect.EmployeeNumber = inserted.EmployeeNumber )
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 15

Expert Comment

by:jdlambert1
ID: 12134642
If you need to match on more columns than just EmployeeNumber, add the addtional columns this way:

DELETE c
FROM Collect c
 LEFT JOIN Employee e ON c.EmployeeNumber = e.EmployeeNumber AND c.ColX = e.ColX AND c.ColY = e.ColY
WHERE e.EmployeeNumber IS NULL
0
 
LVL 2

Author Comment

by:dtolo
ID: 12134672
How do I set the delete to happen every other week with no human intervention?  Would that be a job in enterprise Manager?
0
 
LVL 2

Author Comment

by:dtolo
ID: 12135610
When I go to create this triger

CREATE TRIGGER Employee_Trg_Ins
ON Employee
FOR INSERT
AS
INSERT INTO Collect
SELECT EmployeeNumber
FROM inserted
WHERE NOT EXISTS (
    SELECT 1
    FROM Collect
    WHERE Collect.EmployeeNumber = inserted.EmployeeNumber )

I get an error: 213 : insert error column name or number of supplied values does not match table definition
0
 
LVL 2

Author Comment

by:dtolo
ID: 12135637
0
 
LVL 2

Author Comment

by:dtolo
ID: 12135879
on this

 DELETE FROM Collect LEFT JOIN
                      Employee ON collect.EmployeeNumber = employee.EmployeeNumber
WHERE     employee.EmployeeNumber IS NULL

I get incorrect syntax near key word left
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12138032
>>
CREATE TRIGGER Employee_Trg_Ins
ON Employee
FOR INSERT
AS
INSERT INTO Collect
SELECT EmployeeNumber
FROM inserted
WHERE NOT EXISTS (
    SELECT 1
    FROM Collect
    WHERE Collect.EmployeeNumber = inserted.EmployeeNumber )

I get an error: 213 : insert error column name or number of supplied values does not match table definition
<<


D'OH, SORRY, I should have specified column name on insert:

CREATE TRIGGER Employee_Trg_Ins
ON Employee
FOR INSERT
AS
INSERT INTO Collect (EmployeeNumber)
SELECT EmployeeNumber
FROM inserted
WHERE NOT EXISTS (
    SELECT 1
    FROM Collect
    WHERE Collect.EmployeeNumber = inserted.EmployeeNumber )



Naturally add other columns if you need to.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question