BCP problem.Pls solve anyone immediately

Hi all,

I tried with that but i am able to inserting abtable(this has 4 cols and 12000 records) with bcp.that is working very good.but i'm getting problem with other table bibtable(this has 52 cols and 73000 records) with bcp but it is inserting all rows with dts.i want to insert both of the tables either of one bcp or dts to insert data into sqlserver 7.0

my bcp commands are:
bcp master..BIBDATA in c:\BIB20031006.TXT -fc:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is working properly with abtable but not with bibtable

bcp master..bibdata in c:\medsite\idocfile\new\bib20031006.txt -c -F2 -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is not working with anyone

in dts:

.delimited
filetype:ANSI SKIP ROWS:0
ROW DELIMITER:LF FIRST ROW HAS COLNAMES CHECKED
TEXT QUALIFIER:''

this is inserting bibtable perfectly but not abtable(inserting few records only)Try removing the space after the -e in the string -e c:\mssql7\binn\bib52.fmt so it looks like -ec:\mssql7\binn\bib52.fmt and put spaces in this string -SJAVADEV2-PC-NJ so it looks like -SJAVADEV2 -PC -NJ|||Try removing the -F2, this is telling the bcp utility to only copy the first two rows.

bcp is inserting blank space for empty string

I'm doing a bcp out of a table to a file. Some of the fields in a
record may have an empty string.

When I bcp out to the file and examine it, the fields that have an
empty string in the database now show up in the file as having one
blank character.

Why is bcp doing this? I don't want the blank character in my output.

Thanks,
EricWhich version of MSSQL? What data type is the column? Is ANSI_PADDING
on or off for the column? If your column is char(1), then this would be
expected, as char columns are padded out with spaces; a varchar should
not be padded, though.

If this doesn't help, I suggest you post (simplified) CREATE TABLE and
INSERT statements to show exactly what your table and data look like.

Simon|||Simon Hayes wrote:
> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
> on or off for the column? If your column is char(1), then this would
be
> expected, as char columns are padded out with spaces; a varchar
should
> not be padded, though.
> If this doesn't help, I suggest you post (simplified) CREATE TABLE
and
> INSERT statements to show exactly what your table and data look like.
> Simon

SQL Server 2000
Varchar 16
ANSI_PADDING is off.

Give it a shot. Create a table with three columns, all varchar. Insert
an empty string into each to get rid of any nulls. Then do a bcp out to
an output file and let me know if you get the same results.

Eric|||"epaetz" <epaetz41@.hotmail.com> wrote in message
news:1112704850.296614.123580@.o13g2000cwo.googlegr oups.com...
> Simon Hayes wrote:
>> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
>> on or off for the column? If your column is char(1), then this would
> be
>> expected, as char columns are padded out with spaces; a varchar
> should
>> not be padded, though.
>>
>> If this doesn't help, I suggest you post (simplified) CREATE TABLE
> and
>> INSERT statements to show exactly what your table and data look like.
>>
>> Simon
> SQL Server 2000
> Varchar 16
> ANSI_PADDING is off.
> Give it a shot. Create a table with three columns, all varchar. Insert
> an empty string into each to get rid of any nulls. Then do a bcp out to
> an output file and let me know if you get the same results.
> Eric

I used this test script (on 8.00.760 Enterprise):

set ansi_padding off
go
create table eric (col1 varchar(16) null)
go
insert into eric select '' -- empty string
insert into eric select ' ' -- single space
go
select col1, len(col1) as 'Length', datalength(col1) as 'Datalength', col1 +
'X'
from eric
go

Then I exported the file with bcp:

bcp Development..eric out c:\temp\eric.txt -S kilkenny -c -T

When I checked eric.txt with a hex editor, it showed this:

00 0D 0A 20 0D 0A

So the empty string is an ASCII NUL character in this case, but the space is
ASCII 20. Is this the behaviour you see, or do you get something different?
Setting ANSI_PADDING ON didn't change the output (and BOL recommends it
should always be on anyway).

Simon|||I found a work around, using a Case structure in the sql query to
change empty string to a null. The BCP in turn outputs the resulting
null as an empty!

Thanks for your assistance.

Eric

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
>