how to use bcp with triggers? 
Author Message
 how to use bcp with triggers?

I have a table where records are inserted continuously via bcp. Since this
table is changing every minute, I have no indexes on them. This makes
querying this data slow.
So I want to divide the data into multiple tables say table "A" to "Z" so
that inquiry in faster.
How do I copy the data from this "BIGTABLE" to 26 (a-z) tables and also
keep track of new insertions at the same time?
One idea was to create "Insert" trigger on this table. where I can update
other tables automatically. How do i fire a trigger when data is bcp ed in
the table? Is there a way I can handle this?

Thanks.

-- nitin



Fri, 05 Nov 1999 03:00:00 GMT
 how to use bcp with triggers?

On Mon, 19 May 1997 15:50:04 -0700, "Nitin Patwardhan"

Quote:

>I have a table where records are inserted continuously via bcp. Since this
>table is changing every minute, I have no indexes on them. This makes
>querying this data slow.
>So I want to divide the data into multiple tables say table "A" to "Z" so
>that inquiry in faster.
>How do I copy the data from this "BIGTABLE" to 26 (a-z) tables and also
>keep track of new insertions at the same time?
>One idea was to create "Insert" trigger on this table. where I can update
>other tables automatically. How do i fire a trigger when data is bcp ed in
>the table? Is there a way I can handle this?

Triggers are not fired by BCP because BCP is a non-logged operation, so...
 What you might want to try is a delete trigger that moves the data from an
'import' table to the destination table 'BIG TABLE'.

Create a delete trigger on table 'import', to copy the deleted rows to table
'BIG TABLE'.  Make sure that 'BIG TABLE' has a clustered index, with a fill
factor of at least 50%.

Set up a task (or running a script) that runs after each bcp. The task should
delete all  the rows in the import table, e.g. 'delete from import'. This will
fire the trigger, resulting in the rows being copied to 'BIG TABLE'.

This will keep your import table from getting too big, and will still allow for
fast BCP (no indexes) into table 'import'.  The data inserted into 'BIG TABLE'
by the trigger will be indexed upon insertion, and your queries should run
faster due to the index.

Be sure to 'update statistics' on 'BIG TABLE' occasionally.  Also look into
rebuilding your clustered index occasionally (new DBCC function in SQL Server
6.5, or accomplished by dropping and recreating the index with previous versions
of SQL Server).

Breaking your tables into "A" to "Z" will not help performance.  You need to use
indexes, and the bigger the range of values in an index, the more selective the
index will be, so be sure to select your indexed columns carefully....

Hope This helps!

Clayton Groom
BDO Seidman, LLP



Sun, 07 Nov 1999 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Bulk Insert/bcp using triggers

2. bcp/Importing table from Paradox for Windows into MSSQL 6.5 using bcp

3. Using BCP /n VS. BCP -f and column terminators

4. Loading table with IDENTIY fields using BCP using native format

5. Using triggers to populate Audut columns on the same triggering table

6. Trigger and Column Value / Using a NET SEND in a Trigger

7. bcp utility in trigger

8. trigger and BCP OUT....

9. SQL2000 Trigger and xp_cmdshell or bcp

10. Triggers, SQL 6.5, and bcp

11. Bcp Not firing Insert Trigger

12. bcp and triggers


 
Powered by phpBB® Forum Software