| Author |
Topic |
|
jak3f
Starting Member
33 Posts |
 Posted - 2009-09-17 : 11:25:53
|
| My ultimate goal is to import an excel file into my database and if a row is updated or inserted in the excel spreadsheet I need that to be reflected in my table.Heres my situation:I have a table, 'Customers' with a pk ID. I want to insert/update using sqlBulkCopy, which really is just SqlBulkInsert.(ASP.net) I know I need to write a trigger that will update records if the ID exists, and if the ID doesnt exist then insert it.I have a few questions however. Do I need to make a temporary table that will hold the imported data or can I just have it going straight to the Customers table? It seems like no matter how I write this trigger it wont work, is there any way to make sure the trigger is working?I am writing the triggers in VS2008, using sql server 2005 and I just really need some help. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 11:36:16
|
Show us the "not working" trigger please.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 11:39:33
|
| ALTER TRIGGER trg_duplicate_prevent ON CustomersINSTEAD OF INSERTASBEGINSET NOCOUNT ON--Check for duplicate row. if no duplicate, do an insertIF(NOT EXISTS(SELECT C.CustomerID FROM Customers C, inserted i WHERE C.CustomerID = i.CustomerID)) INSERT INTO Customers(CustomerID, Name, Address, City, State, ZipCode, Phone, Email, EFIN, RegCode, AccountNumber, ChargeNumber, ProductCode, PurchaseDate, PriorSoftware, Bank, BalanceDue, Renewed)SELECT i.CustomerID, i.Name, i.Address, i.City, i.State, i.ZipCode, i.Phone, i.Email, i.EFIN, i.RegCode, i.AccountNumber, i.ChargeNumber, i.ProductCode, i.PurchaseDate, i.PriorSoftware, i.Bank, i.BalanceDue, i.Renewed FROM inserted iELSE UPDATE Customers SET CustomerID = i.CustomerID, Name = i.Name, Address = i.Address, City = i.City, State = i.State, ZipCode = i.ZipCode, Phone = i.Phone, Email = i.Email, EFIN = i.EFIN, RegCode = i.RegCode, AccountNumber = i.AccountNumber, ChargeNumber = i.ChargeNumber, ProductCode = i.ProductCode, PurchaseDate = i.PurchaseDate, PriorSoftware = i.PriorSoftware, Bank = i.Bank, BalanceDue = i.BalanceDue, Renewed = i.Renewed FROM Customers C, inserted i WHERE C.CustomerID = i.CustomerIDENDbut wtf is inserted i? is that a default temporary table? I guess I need to declare a temp table idk tho |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 11:59:17
|
IFBEGIN...ENDELSEBEGIN...ENDA trigger has access to tables that you cannot see they exist at runtime for the trigger only.Triggers knowinserted and deletedinserted holds the inserted data if an insert or update happeneddeleted holds the deleted data if deleted and the old data if updated
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 12:12:33
|
| Ok great thanks for that tidbit. I know now that inserted i is not just a dumb move but is inserted i found after the insert? I dont think I am even getting past the insert to do it, so that is why I had it set as instead of insert, should I make it before insert? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 12:26:54
|
I think your trigger looks fine but INSTEAD OF is most used when a user wants to insert data in a view and the trigger then can handle the underlaying tables in right form...Can you define more clear "not working"?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 12:33:46
|
Additional I must say that I would not solve this by using a trigger.I would pump the data into a "staging table" and after that doing the insert/update by checking if exists(...)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 12:58:57
|
| Honestly my construction has been more like this;I have been pumping the data into a table, ExcelInfo, then from there I have been executing this statement : INSERT INTO Customers (CustomerID, ...) select X.* from (SELECT DISTINCT CustomerID,... FROM ExcelInfo) X LEFT JOIN Customers C on X.CustomerID = C.CustomerID where C.CustomerID IS NULLThis works fine for inserting records that do not already exist in the Customers table, but I need to be able to update them as well. The thing is the ExcelInfo table is full of duplicates from every bulk copy that has had duplicates (since the ExcelInfo table doesnt have a PK) since there is not a PK I cannot update a specific customer right? |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 13:04:42
|
| I really would like to knock out the third party table and just be able to insert and update right into Customers from the Insert but i will take whatever works and roll with it |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 13:04:51
|
So you have many duplicates for a customer and you want to update the customer table wit the most recent data from your ExcelInfo?Do you have any datetime column in your ExcelInfo to determine the most recent record for earch customer?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 13:05:43
|
And why you do not empty the ExcelInfo after work?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 13:09:23
|
| could I technically empty it after every bulk insert? I am sorry im a noob and the DBMS(i guess you could call it that) to write my triggers etc is in VS2008 so I am thinking how I could empty it after insert. |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 13:10:19
|
quote:
Originally posted by webfred
So you have many duplicates for a customer and you want to update the customer table wit the most recent data from your ExcelInfo?Do you have any datetime column in your ExcelInfo to determine the most recent record for earch customer?
No, you're never too old to Yak'n'Roll if you're too young to die.
No I sure dont but thats a good idea too, add that and then update a customer where datetime is most recent? is that what youre on to? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-17 : 13:17:14
|
quote:
No I sure dont but thats a good idea too, add that and then update a customer where datetime is most recent? is that what youre on to?
yup!There is a solution using row_number() to mark the most recent one.But to work out an example I need to know a bit more about the table structure...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jak3f
Starting Member
33 Posts |
Posted - 2009-09-17 : 13:30:44
|
| I am really just trying to take data from another database, export it to an excel worksheet, and then bulk insert it into my database. There will be customers on the excel worksheet that have already been imported and data will have been updated on the worksheet , so my table needs to be able to adapt to this kind of situation.It would be great if I could somehow mark the most recent one that was imported because that has the updated data in itWhat about on insert, delete the rows with matching PK's in the ExcelInfo table(after recreating the table and setting the PK to customerID) then it would just replace the row with the same ID and I could successfully update into the customers table |
|
|
|
|
UPSERT trigger help