Solved

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

Posted on 2004-09-23
9
288 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

776 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