Firing Instead of Trggers with BCP
-
Hi ,
In my application, I am loading data from text files into database using bcp
utility. I need to conditionally insert the data into the table. So i have
written Instead of triggers on the tables and checking the conditions in the
triggers. So In bcp commnad i have used -h "Fire_Triggers" switch to fire
triggers. In the MSDN it is given that "The trigger will be fired once for
every batch". In my case i have more than 2 lacs records inserting from the
text file. As i didn't given any batch size it is taking every 1000 rows as a
batch. But my trigger is firing only once for all records. When i loop in the
inserted rows in the trigger it is giving all the rows instead of batch
rows.Can any one tell me how to get the batch rows in the trigger.
I am giving the bcp Command and Trigger code below for your reference.
BCP Command:
bcp TestDB.dbo.Input_VW in \\jb-5f-016d1\Archive\InputData.txt -c -t -r
-T -h FIRE_TRIGGERS
Trigger code:
CREATE TRIGGER InputFileDataTrg on Input_VW
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Id INT
DECLARE @fileId INT
DECLARE @streamTypeId INT
DECLARE @object VARCHAR(200)
DECLARE @field VARCHAR(200)
DECLARE @value VARCHAR(1000)
DECLARE @tempTbl TABLE (Id INT IDENTITY(1,1),
FileId INT,
StreamTypeId INT,
Object VARCHAR(200),
Field VARCHAR(200),
Value VARCHAR(1000))
INSERT INTO @tempTbl(FileId,StreamTypeId,Object,Field,Value)
SELECT FileId,StreamTypeId,Object,Field,Value
FROM Inserted
SELECT @Id = MIN(Id) FROM @tempTbl
WHILE (@Id IS NOT NULL)
BEGIN
SELECT @fileId = FileId,
@streamTypeId = StreamTypeId,
@object = Object,
@field = Field,
@value = Value
FROM @tempTbl
WHERE Id = @Id
SET @object = REPLACE(@object,'.',' ')
SET @field = REPLACE(@field,'.',' ')
IF NOT EXISTS(SELECT 1 FROM dbo.INPUTFILEDATA(NOLOcK) WHERE FileId =
@fileId AND StreamTypeId = @streamTypeId
AND Object = @object AND Field =@field)
BEGIN
INSERT INTO DBO.INPUTFILEDATA (
FileId,
StreamTypeId,
Object,
Field,
Value
 
VALUES (
@fileId,
@streamTypeId,
@object,
@field,
@value
 
END
DELETE FROM @tempTbl WHERE Id = @Id
SELECT @Id = MIN(Id) FROM @tempTbl
END
Please help & Thanks in advance.
Regards,
Jitendra
-
bcp, by default, will treat the input file as one batch
To change the batch size use the -b parameter
e.g. -b 1
will set the batch to 1 row and will fire the trigger once per row
You should try to make your trigger completely set based without the loop to improve performance
e.g I think this will achieve the same
INSERT INTO dbo.INPUTFILEDATA (FileId,StreamTypeId,Object,Field,Value)
SELECT i.FileId,i.StreamTypeId,i.Object,i.Field,i.Value
FROM (SELECT FileId,StreamTypeId,REPLACE(@object,'.',' ') AS [Object],REPLACE(@field,'.',' ') AS [Field],MIN(Value) AS [Value]
FROM Inserted
GROUP BY FileId,StreamTypeId,REPLACE(@object,'.',' '),REPLACE(@field,'.',' ')) i
WHERE NOT EXISTS(SELECT 1 FROM dbo.INPUTFILEDATA a
WHERE a.FileId = i.FileId AND a.StreamTypeId = i.StreamTypeId AND a.Object = i.Object AND a.Field = i.Field)
This assumes that there could be multiple different Value's for FileId,StreamTypeId,Object,Field
If this is not true but records are duplicated then use
INSERT INTO dbo.INPUTFILEDATA (FileId,StreamTypeId,Object,Field,Value)
SELECT DISTINCT i.FileId,i.StreamTypeId,i.Object,i.Field,i.Value
FROM Inserted i
WHERE NOT EXISTS(SELECT 1 FROM dbo.INPUTFILEDATA a
WHERE a.FileId = i.FileId AND a.StreamTypeId = i.StreamTypeId AND a.Object = i.Object AND a.Field = i.Field)
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
Thanks a lot. This works for me.
Once againg Thanks a lot.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply