Discussion Locked

SQL 2000: SQL Statement OR Trigger Help

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here we go: I have a SQL SERVER 2000 database. In it I have a table(1) that is updated every half hour or so by a COBOL extraction(Outsourced. not in-house) I have created another table(2) and imported all the information from table(1) into it. Every time that table(1) is updated I want it to be mirrored in table(2). They are both on the same machine and in the same database. Unfortunately, this configuration can not change, so I have to work within these constraints.

My first idea was to create a simple asp script that runs on the server that will periodically compare the 2 and then insert any data from table(1) into table(2) that does not already exist there. The problem I am having here is that my SQL statement is not just returning data that does not exist, but is taking each row and comparing it to every other row and returning any combination of the two that do not match, so I am getting thousands of returned recordsets.

I either need a SQL statement that will look at bothtables and only return the data that does not exist in both, or a trigger that will take any updated or newly inserted data from table(1) and automatically insert or update it in table(2). Thank you for your help.

3 replies

by miket

Insert trigger:
CREATE TRIGGER trigger1
ON COBOL_Import_Table
FOR INSERT
AS
INSERT INTO ProductionTable
(Field1,
Field2,
Field3,
Field4,
etc.)
SELECT Field1, Field2, Field3, Field4, etc.) FROM Inserted

Go

Update trigger:
CREATE TRIGGER trigger2
ON COBOL_Import_Table
FOR UPDATE
AS
Update ProductionTable pt
Set pt.Field1 = inserted.Field1,
pt.Field2 = inserted.Field2,
pt.Field3 = inserted.Field3,
pt.Field4 = inserted.Field4,
etc.)
Where pt.key = inserted.key
Go

You might also look into replication.

This discussion is closed to new replies.