August 18, 2009 at 1:57 pm
I'm having trouble importing data to a database. I have a flat .csv file that I bring into Excel and convert to an .xls, then I import that into a table in Sql Server. This all seems to be working fine, but then, when I import the data from the "dummy" table to the "real" data tables the first section of the Insert trigger throws an error and the whole process is aborted. The error always seems to refer to the first data row in the "dummy" table.
Here's the weird part: it works OK with up to 25 or so records, but not with 50 or 100 or the entire 6500 I have to import. On my main server, it would work if I had did the 25 record set first. In other words, if I imported the 25 records, and THEN imported the 6500, it would take them. But if I just deleted all the records and THEN imported the 6500, it would fail.
But on my laptop, it takes my 25 record set OK, then always fails with my 100 records set.
The script starts like this:
USE BWD
GO
--ALTER DATABASE SET SINGLE_USER
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tblInItem1')
DROP TABLE tblInItem1
PRINT 'Dropped tblInItem1'
SELECT * INTO tblInItem1 FROM SVRINITEM...[Sheet1$] AS f
WHERE ProductLine > ''
PRINT 'Loaded tblInItem1'
-- NEXT LINE DELETES ALL DATA IN TABLE!!!!!!!!!!!!!!!!!!!!
delete from tblInItem
-- *******************************************************
PRINT 'Deleted tblInItem'
INSERT INTO tblInItem (ItemId, Descr, SuperID, ItemStatus, ProductLine, SalesCat, PriceID, TaxClass,
UomBase, UomDflt, LottedYN, AutoReorderYN, KittedYN, ResaleYN, PictID, UsrFld1, UsrFld2,
UsrFld3, UsrFld4, HMRef, CostMethodOverride, ItemType)
SELECT ItemId, Descr, NULL SuperID, ItemStatus, ProductLine, SalesCat, NULL PriceID, TaxClass,
UomBase, UomBase UomDflt, 0 LottedYN, 0 AutoReorderYN, 0 KittedYN, 1 ResaleYN, NULL PictID, UsrFld1, UsrFld2,
UsrFld3, UsrFld4, NULL HMRef, 0 CostMethodOverride, 1 ItemType
FROM dbo.tblInItem1
PRINT 'Loaded tblInItem'
-- NEXT LINE DELETES ALL DATA IN TABLE!!!!!!!!!!!!!!!!!!!!
DELETE FROM tblInItemUom
-- *******************************************************
PRINT 'Deleted tblInItemUom'
INSERT INTO tblInItemUom (ItemId, Uom, ConvFactor, UPCcode)
SELECT ItemID, UOMBase, 1 ConvFactor, UPCcode
FROM dbo.tblInItem1
PRINT 'Loaded tblInItemUom'
Here's the part of the trigger that keeps stopping me:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER trgInItemI ON dbo.tblInItem FOR INSERT AS
SET NOCOUNT ON
Declare @FldVal varchar(255)
Declare @Undo bit
Set @Undo = 0
/* BEGIN tblInProductLine */
IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.ProductLine Is Not Null))) != (SELECT COUNT(*) FROM tblInProductLine, inserted WHERE (tblInProductLine.ProductLine = inserted.ProductLine))
BEGIN
Select @FldVal = Cast(inserted.ProductLine As Varchar) from inserted
RAISERROR (90010, 16, 1, 'trgInItemI', @FldVal, 'tblInProductLine.ProductLine')
Set @Undo = 1
END
/* END tblInProductLine */
And here's a sample error message:
Dropped tblInItem1
Loaded tblInItem1
Deleted tblInItem
Msg 90010, Level 16, State 1, Procedure trgInItemI, Line 11
(trgInItemI) The value '530 ' must exist in 'tblInProductLine.ProductLine'. Unable to insert.
Msg 3609, Level 16, State 1, Line 20
The transaction ended in the trigger. The batch has been aborted.
My laptop has an Intel Core 2 T5200 1.6GHz CPU and 1 GB RAM and is running Windows XP Pro SP3 with SQL Server 2005. Could it be a memory problem, or one of my default settings is wrong, or an error in the script, or what? Is there some obvious error here I'm overlooking? I could really use a little help with this one!
August 19, 2009 at 7:08 am
Hi Phillip,
would it not be better to replace the trigger with a foreign key reference to tblInProductLine? Is there a specific reason for having the trigger rather than a FK?
August 19, 2009 at 4:32 pm
Good idea. I was using the trigger because it's part of an accounting package and their tech support guy warned me against turning them off to do a data import, but it looks like your idea should give the protection I need and get me around this error. Thanks!
August 20, 2009 at 3:58 am
Only a pleasure mate! Let me know if it works with the foreign key!
August 21, 2009 at 1:58 pm
Interesting that the trigger only fires on insert, as an UPDATE could easily make it possible to change the value of a record so that it no longer matches. I would be asking the support person why UPDATES aren't similarly protected before I substituted the FK relationship.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 22, 2009 at 10:40 am
That's a good point, but I guess it's my fault for trying to keep things too short and simple. They DO actually have another trigger for updates, and one for deletions, and even the one for Inserts is much longer that the snippet I posted. I was afraid no one would want to take the time to read through everything. Sorry, and thanks for the input.
January 8, 2010 at 4:32 am
In case i'm importing data from one database to another database using import wizard in sql server 2005.on that time if imported table has any insert trigger that does not invoke when data insert on the table ,but i found all the values in imported table
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply