March 16, 2005 at 9:23 pm
Hi,
SQL2000 SP3a
I wish to avoid triggers as a method to maintain data integrity. And do this way !
CustomerTransactions Table is [Customers]
CustomerNames Table is [CustomerNames]
In the Customers table, I have data like
CustCode, Amount, Qty
1546 , $50.00,5
5556 , $60.00,6
1235 , $70.00,7
5556 , $50.00,5
7896 , $40.00,4
In the CustomersNames table, I have data like
CustCode, CustOmerName
1546, Bruce Smith
1235, John Woods
7896, Ian Brown
As you can see custcode '5556' does not exist in the Customer names table, and it has appeared twice in the 'Customers' table.
Sure a trigger may send a Message saying this, BUT I wish to do a SP that places the '5556' in a [Customer Code Missing] table..so I can audit may data after a posting a fix things up.
Like this I guess
INSERT INTO [Customer Code Missing] ('CustCode','SourceTable')
SELECT CustCode, 'Customers' FROM [Customers] WHERE CustCode <> (SELECT CustCode FROM [CustomersNames]);
This will place in the [Customer Code Missing]
CustCode, SourceTable
5556, CustomersNames
5556, CustomersNames
Even though I end up with Two entries I can group them up to One entry, this avoids 'DISTINCT' statements.
I have at most 5 foreign keys to check per table. Data is loaded up like 4000 records at a time. Any examples on how this can be done, is there a better way ?
'<>' is this correct, in above code.
Thanks
March 17, 2005 at 3:34 am
Hi,
What is your requirement.. If I understand clearly whenever u want to insert data into customers table, you will want to insert data into CustomerNames table as well. Or you will want to first enter data into Customers table and then add data into customerNames table. In the second case you may want to find all customers who do not have prioper data in CustomerNames table.
If the case is scinario 1 then have a stored procedure that inserts data into table customers and CustomerNames simultaneously and put both the statements with in a begin and commit tran. Ofcourse you need to check if any error occured in first transaction and rollbac.
In the second case. You may enter record data into the customers table without regard for the second table and have a table variable that picks up nonexisting cutomers in the CustomerNames table and do an insert. This need not be part of your transaction
select * from Customers left join CustomerNames on
Customers.CustId =CustomerNames.custId
where CustomerNames.CustId is null
Hope this works
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply