| 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/ |
|
|
|
|
TRIGGER WITH BCP