TRIGGER WITH BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

TRIGGER WITH BCP

Author

Topic

mark-ec
Starting Member

4 Posts

Posted - 2014-07-25 : 18:22:21

I have a text file with 5 rows (example), to avoid Primary key in my production table (FELEC05), I insert into a intermediate table (FELEC07 same structure like production) and this table had a trigger for insert, so when I use bcp to save data over my intermediate table the trigger must save data in my production table, this works the first time all 5 records are saved in my 2 tables but if I include another record in my text file (5 originally and 1 extra) the information is saved in my intermediate table but the record 6 doesn't save in my production table, it's like inserted fields doesn't refresh.

I appreciate your help

THIS IS THE TRIGGER

CREATE TRIGGER INFE05 ON FELEC07
AFTER INSERT
AS
BEGIN
if not exists(select * from FELEC05, inserted where FE05001= FE07001 and FE05002= FE07002 )
Begin
insert into FELEC05 select * from inserted
End

END
GO

THIS IS HOW I CALL BCP

bcp BDD.dbo.FELEC07 in d:\INFFECABF.txt -c -tç -h "FIRE_TRIGGERS" -UUser -PPass -SSERVSQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-25 : 19:19:41

CREATE TRIGGER INFE05 ON FELEC07
AFTER INSERT
AS
BEGIN

insert into FELEC05
select *
from inserted i
where not exists (select * from FELEC05 f where i.FE07001 = f.FE05001 and i.FE07002 = f.FE05002)

END
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mark-ec
Starting Member

4 Posts

Posted - 2014-07-28 : 12:23:54

Thank you tkizer

The problem continue, first time I use bcp two tables 5 records, second time FELEC05 5 records FELEC07 11 recors. I wish to use this way to load data to handle a lot of information

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 12:58:50

You'll need to post a couple of sample files for us to test on our own machines then.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mark-ec
Starting Member

4 Posts

Posted - 2014-07-28 : 13:29:55

TABLES

create table FELEC07 (
FE07001 integer null,
FE07002 varchar(3) null,
FE07003 varchar(5) null,
FE07004 varchar(5) null,
FE07005 integer null,
FE07006 varchar(15) null,
FE07007 integer null,
FE07008 varchar(250) null,
FE07009 varchar(15) null
)
go

create table FELEC05 (
FE05001 integer not null,
FE05002 varchar(3) not null,
FE05003 varchar(5) not null,
FE05004 varchar(5) not null,
FE05005 integer not null,
FE05006 varchar(15) null,
FE05007 integer null,
FE05008 varchar(250) null,
FE05009 varchar(15) null
constraint PK_FELEC05 primary key nonclustered (FE05001, FE05002, FE05003, FE05004, FE05005)
)
go

FILE 1 BCP (INFFECABF.txt)

1ç1ç001ç001ç128ç25/07/2014ç0çESTEBANç171791
1ç1ç001ç001ç125ç25/07/2014ç0çANNYç171891
1ç1ç001ç001ç126ç25/07/2014ç0çCHARLESç171991
1ç1ç001ç001ç127ç25/07/2014ç0çMIKAELç172091
1ç1ç001ç001ç129ç25/07/2014ç0çSOFIAç172191

FIEL 2 BCP (INFFECABF.txt)
1ç1ç001ç001ç128ç25/07/2014ç0çESTEBANç171791
1ç1ç001ç001ç125ç25/07/2014ç0çANNYç171891
1ç1ç001ç001ç126ç25/07/2014ç0çCHARLESç171991
1ç1ç001ç001ç127ç25/07/2014ç0çMIKAELç172091
1ç1ç001ç001ç129ç25/07/2014ç0çSOFIAç172191
1ç1ç001ç001ç130ç25/07/2014ç0çSILVIAç172291

BCP COMMAND
bcp BDD.dbo.FELEC07 in d:\INFFECABF.txt -c -tç -h "FIRE_TRIGGERS" -UUser -PPass -SSERVSQL

TRIGGER
CREATE TRIGGER INFE05 ON FELEC07
AFTER INSERT
AS
BEGIN

insert into FELEC05
select *
from inserted i
where not exists (select * from FELEC05 f where i.FE07001 = f.FE05001 and i.FE07002 = f.FE05002)

END
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 15:59:53

Don't you need to include all 5 columns in the not exists to match the PK?

create TRIGGER INFE05 ON FELEC07
AFTER INSERT
AS
BEGIN

insert into FELEC05
select *
from inserted i
where not exists (select * from FELEC05 f where i.FE07001 = f.FE05001 and i.FE07002 = f.FE05002 and i.FE07003 = f.FE05003 and i.FE07004 = f.FE05004 and i.FE07005 = f.FE05005 )
end
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mark-ec
Starting Member

4 Posts

Posted - 2014-07-28 : 17:31:07

Thank you Tara, all is working at this time I appreciate your help best regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 17:38:41

You're welcome, glad to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources