Solved

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

Posted on 2004-09-23
9
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

624 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