dtolo
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
How do I set the delete to happen every other week with no human intervention? Would that be a job in enterprise Manager?
ASKER
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
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
ASKER
Question continued to : https://www.experts-exchange.com/questions/21142665/trigger-error.html
ASKER
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
DELETE FROM Collect LEFT JOIN
Employee ON collect.EmployeeNumber = employee.EmployeeNumber
WHERE employee.EmployeeNumber IS NULL
I get incorrect syntax near key word left
>>
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.
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.
DELETE c
FROM Collect c
LEFT JOIN Employee e ON c.EmployeeNumber = e.EmployeeNumber
WHERE e.EmployeeNumber IS NULL