Solved

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

Posted on 2004-09-23
9
289 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 300 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 69

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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