Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

about triggers and bulk insert Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 1:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:55 PM
Points: 24, Visits: 76
Hi
I have a query regarding trigger

I have a table Table1, and on this i have created a trigger Trg1 which will fire when we insert a row into Table1. If i want to perform bulk data insert into Table1, i will disable the trigger Trg1 and then i will do the bulk insert. and enable the trigeer.

consider a scenario where the Trigger trg1 will copy(replicate) the newly inserted data from Table1 to another table Table2.

Now please let me know how can we perform this replication of data for the bulk inserted rows
Post #603908
Posted Monday, November 17, 2008 1:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 175, Visits: 743
Hi,

Are these tables part of a SQL Server replication? If not, and if it is a true copy operation, why can't you BULK INSERT to the other table as well?

/Elisabeth


elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Post #603929
Posted Monday, November 17, 2008 2:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:55 PM
Points: 24, Visits: 76
Hi Thanks for your reply.
This is not part of SQL Replication.
The code in the trigger is not just copying the newly added rows. it involves some validations and then copying of newly added rows to another table.
my question is, how to run the trigger code for the newly inserted data?
Post #603940
Posted Tuesday, November 18, 2008 8:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 175, Visits: 743
Hi,

Well the trigger by definition will trigger on some action so you will have to break out the code and put it in a (e.g.) stored procedure and then find some means of identifying the rows you just inserted.

If your goal is to keep the BULK INSERT operation as fast as possible, you could also BULK INSERT into an empty temporary table (not #temp table), and then run whatever code you wish on that table; inserting into both of your tables at the same time.


/Elisabeth


elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Post #604424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse