Prev: Merge membership
Next: Alternatives to cursors
From: Brian Shafer on 2 Jun 2010 14:38
Hi, I think i have most of the kinks worked out. I am doing a sqlbulkcopy
from a vb.net application. I am putting the data in Table2 and on Table2 I
have an after insert trigger that needs to insert or update data in Table1.
Only problem is.. with bulkcopy I need to process one row at a time with
the trigger. Makes since so far.. but I am having brain cramps on how to get
this down... the last sql statement i had tried is...
insert into [Table1] (a.[field1],a.[Field2],a.[Field3)
select i.[field1],i.[field2],i.[field3]
from inserted i inner join Table1 a on i.Field1 = a.Field2)
shouldn't thiis make trigger process each record added?
From: Erland Sommarskog on 2 Jun 2010 17:55
Brian Shafer (BrianShafer(a)discussions.microsoft.com) writes:
> Hi, I think i have most of the kinks worked out. I am doing a
> sqlbulkcopy from a vb.net application. I am putting the data in Table2
> and on Table2 I have an after insert trigger that needs to insert or
> update data in Table1. Only problem is.. with bulkcopy I need to
> process one row at a time with the trigger. Makes since so far.. but I
> am having brain cramps on how to get this down... the last sql statement
> i had tried is...
> insert into [Table1] (a.[field1],a.[Field2],a.[Field3)
> select i.[field1],i.[field2],i.[field3]
> from inserted i inner join Table1 a on i.Field1 = a.Field2)
> shouldn't thiis make trigger process each record added?

It's difficult to understand what you are trying to do, but your INSERT
statement looks funny. What's the point with joining to the table
you are inserting into

Nor do I understand what you mean with "with bulkcopy I need to
process one row at a time with the trigger." It's preferable to handle
all rows at once.

Anyway, if you are on SQL 2008, use the new MERGE statement, which
permits you to perform INSERT and UPDATE in a single statement. Else
you will need one INSERT WHERE NOT EXISTS and an UPDATE.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Merge membership
Next: Alternatives to cursors