Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

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
dtolo
Asked:
dtolo
  • 4
  • 3
  • 2
2 Solutions
 
jdlambert1Commented:
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
 
jdlambert1Commented:
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
 
Scott PletcherSenior DBACommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jdlambert1Commented:
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
 
dtoloAuthor Commented:
How do I set the delete to happen every other week with no human intervention?  Would that be a job in enterprise Manager?
0
 
dtoloAuthor Commented:
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
 
dtoloAuthor Commented:
0
 
dtoloAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>>
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now