bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million row
s
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time th
e
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server
.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is bei
ng
> truncated? How is the BCP process (the one that is misbehaving) called?
Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million rows
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time the
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!
No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!
|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is being
> truncated? How is the BCP process (the one that is misbehaving) called? Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

Batch SQL

Questions

I need to batch a set of update commands. Can that be done and if so what are the possible ramfications? Can one mix / match Deletes/Inserts and Updates into a SQL command via the semi-colon in a batch set? (Is this the appropriate forum?)

WHY

I am unable to use the

dynamic sql building found in the dataset due to a multiple table

contstraints. I have built classes that will extract the differences

from two datasets and report on the updates/deletes and inserts

required. Since I have multiple SQL statements, it would be nice to combine them into one statement.

Platform

.Net 2C#IIS6SQL Server 2000 is the eventual target database for the generated SQL

advTHANKSance

Regarding Transact-SQL it is possible to combine multiple statement into one batch. Separate the statements with semi-colon.

If possible I would look into using stored procedures instead. It will help keeping more logic on the backend and allowing re-usal if you want to build a client for another platform.

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
Dan
Are you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could
be slowing down as the transaction log grows. Try breaking the updates into
smaller logical batches (say 1000 inserts/updates at a time) and issue a co
mmit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your problem...
Brad Feake
Database Administrator

Batch Inserts from DataTable

Please help guys,
I have a DataTable filled from the parsing of a csv file by the OleDb text driver.
This DataTable could on occassion contain in excess of 2000 rows.
I want to be able to batch the inserts to my backend sql table and be able to recorver on errors during the insert.
i.e, maybe send the first 500 rows to insert via an insert dynamic text... really don't know the optimal insert technic to use.
but, if I get an error on say the third batch, I want to be able to recorver, and not have to start all over again and continue the inserts from the batch that failed....
Please help... what is the best way to perform the inserts and how can I track these inserts and recorver on errors like power failures or sql server unavailable etc.
Please help...could I batch the reading of data from a csv file, I mean, if the csv file contains hundreds of thousand of records, how do I read say 500 at a time, using the Microsoft Text Driver - please help...|||

You could run dynamic selects

dim i as int32

for each row in datatable.rows

sql += "insert into table;"
i += 1

if i = 500 then

'do connection and things

i = 0

end if

next

Nick

|||

You could use DTS package to move the CVS file but if you want an alternative, try the links below for options. Hope this helps.

http://www.sqldts.com/?220

http://www.users.drew.edu/skass/sql/TextDriver.htm

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous check
s
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,
Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

basic trigger ?

Hi
I want to have a trigger than when record is inserting into table A, the
trigger inserts a record into table B
I want the identity value from Table A to be one of the values inserted into
table B.
How do I get the indentity value from tableA in my trigger so that I can
insert into table b
thanksAussie Rules (someone@.somewhere.com) writes:
> I want to have a trigger than when record is inserting into table A, the
> trigger inserts a record into table B
> I want the identity value from Table A to be one of the values inserted
> into table B.
> How do I get the indentity value from tableA in my trigger so that I can
> insert into table b
CREATE TRIGGER A_tri ON A FOR INSERT AS
INSERT B (some_col, some_other_col, ...)
SELECT i.identity_col, i.other_col, ...
FROM inserted i
JOIN ...
inserted is a virttual table holds the inserted rows, so you find the
identity value right there.
Be aware of that a trigger fires once per *statement*.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sirs
You might want to consider using 'After Insert' instead of 'For
Insert'. This way you know that the record has been successfully inserted
into Table A, Before you insert into Table B.
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C24758FE7FYazorman@.127.0.0.1...
> Aussie Rules (someone@.somewhere.com) writes:
the
> CREATE TRIGGER A_tri ON A FOR INSERT AS
> INSERT B (some_col, some_other_col, ...)
> SELECT i.identity_col, i.other_col, ...
> FROM inserted i
> JOIN ...
> inserted is a virttual table holds the inserted rows, so you find the
> identity value right there.
> Be aware of that a trigger fires once per *statement*.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||I expect you meant Instead Of rather than For.
For Insert triggers are classified as After Insert triggers, and only fire
after the row has been successfully inserted. Instead Of triggers fire in
place of the triggering action. If you want to perform an insert, then the
body of the Instead Of Insert trigger must issue an insert into the
underlying table.
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:O4hAxQPrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> Sirs
> You might want to consider using 'After Insert' instead of 'For
> Insert'. This way you know that the record has been successfully inserted
> into Table A, Before you insert into Table B.
> Mark
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96C24758FE7FYazorman@.127.0.0.1...
> the
inserted
can
>

basic timestamp question.

Hello I have a very basic timestamp question. There is a data type in SQl Servertimestamp.

what I basically want is that when someone Inserts a data the timestamp column puts a timestamp there...how can i achieve that?...I thought that the timestamp type automatically does that

Yes you cannot insert into a timestamp column. SQL Server does that for you.

|||

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

|||

Thats the hex value of the date/time value. Its very specific. I forget what measure of time it measures to, but its very specific.

Tim

|||

sahajMarg:

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

Yes its a binary number assigned by SQL Server. you should read up books online for timestamp columns to understand where/how it can be used.

|||

sahajMarg:

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

Yes its a binary number assigned by SQL Server. you should read up books online for timestamp columns to understand where/how it can be used.

|||

Just read it the same post the it is the Hex value of date and time..is that correct?