DTS says 500 rows inserted, tbl has 450

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 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS says 500 rows inserted, tbl has 450

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-05-18 : 17:01:53

I have a DTS package which inserts data from a flat file to a table.
The flat file has 500 rows in it, and when I run the DTS it says Complete (500), but when I do select * from that table I get 450 rows.
I even removed primary key constraint just in case but I got the same result. There are about 24 columns in flat file and the table has only 4 columns.

I tried deleting some rows fromt the flat file to test for different count, If I reduced the count to 450 rows the table had 417 and if I reduced to 10 rows still the table had 7 columns.

Any solution to this.

Ashley Rhodes

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 17:36:54

your dts probably filters rows on some condition.
so your dts did process (read) all 500 rows but it inserted only 450 rows.

so check what the DTS does.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-18 : 17:41:44

Or there is a bad character in the file which is causing DTS to think it has reached the end of the file when it still has more rows to process.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 07:33:34

IOf you can think of an easy way I would compare the DB data with the flat file - e.g. re-export it and use a text compare tool.

I would be looking for which rows failed to import, then you can perhaps have a better idea why.

Duplicate PK perhaps ? (although I would expect DTS to give you an error message to that effect)

If its the last 50 rows which are missing then I would put my money on Tara having the right answer

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-20 : 12:07:27

It is possible that your table has a UNIQUE index created with the IGNORE_DUP_KEY option. This causes SQL Server to not insert additional rows that contain duplicates of the UNIQUE index.

This following code shows an example of this. Notice that the batch is processed, even though the data being inserted would produce a primary key violation.


use tempdb
go
create table T_DUPE_TEST 
( NO_DUPE_COL int not null primary key clustered )

create unique index IX_T_DUPE_TEST__NO_DUPE_COL
on T_DUPE_TEST ( NO_DUPE_COL )
with IGNORE_DUP_KEY
go
print 'Insert data'
insert into T_DUPE_TEST
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 4

print 'Show T_DUPE_TEST data'
select * from T_DUPE_TEST
go
drop table T_DUPE_TEST


Results:

Insert data
Server: Msg 3604, Level 16, State 1, Line 2
Duplicate key was ignored.
Show T_DUPE_TEST data
NO_DUPE_COL 
----------- 
1
2
3
4

(4 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-05-20 : 17:04:19

Never knew that ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-20 : 22:48:43

quote:
Originally posted by Kristen

Never knew that ...

It's a little used feature, but sometimes useful.

I doubt it's the cause of the OP's problem, but I posted it in case anyone is interested.

CODO ERGO SUM

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-05-21 : 16:14:46

Thanks Tara,

But like I mentioed before that I ran the DTS for random rows for that file. I deleted the bottom 50 rows and the rows inserted were 417. Then I ran the DTS for top 10 rows and even then only 7 rows were inserted. So I am not sure what the problem is. I do not see any characters and like Michael said there is no index with IGNORE_DUP_KEY on my table.

I am still trying to figure out. I did remove all constraints and all primary keys from the table and still got the same result.

Ashley Rhodes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-21 : 16:33:18

Perhaps you could post the DDL for your table and upload your DTS file to a web site so that we can download it and test it.

Please do not send the file to us via e-mail.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources