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

SQL Server Development (2000)

bcp problem

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-13 : 15:01:28

Anybody knows how to bulk copy into a table with IDENTITY field?

exec ('bulk insert tblTempACHInfo from "I:\Windows\A040204.txt" With (FirstRow=2)')

This give me result: (0 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-13 : 15:16:08

Did you look at the KEEPIDENTITY option?

Why do you have this in an EXEC statement?

Tara

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-13 : 15:23:38

bulk insert tblTempACHInfo from "I:\Windows\A040204.txt" With (FirstRow=2,KEEPIDENTITY)

Resulut: (0 row(s) affected)

I got same result,why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-13 : 15:25:24

What does the format of the file look like? You might need to specify FIELDTERMINATOR and/or ROWTERMINATOR.

Could you post a few rows from the file?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-13 : 15:32:19

Crosspost

I thought I was working on this one

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34134

Brett

8-)

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-13 : 15:35:29

Orginal file is a text file, what I do is to import the text file into vchrACHRecord (column name) of tblTempACHInfo (table name), but I want this table having IDENTITY column.

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-13 : 15:39:05

If I delete IDENTITY column, I can import it successfully. I still think it's code problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-13 : 16:18:34

So does your data have the identity values?

And...

What does the format of the file look like? You might need to specify FIELDTERMINATOR and/or ROWTERMINATOR.

Could you post a few rows from the file?

Tara

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-14 : 08:32:01

The text file itself has no identity field, but when I import to SQL table, I want to add an identity field because I need those ID numbers in my program.

The text file looks like this:

6272670841992080001458385 0000003206083-50-6972 ABBARNO, RICHARD M 0071100260000001

(They're actually in one line. this's customer's information, one customer one line)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 08:59:30

What's the DDL of the table?

A 1 Column varchar(8000)?

How big is the file?

If it's huge, I'd use a format file, if it's not I'd use a stage table without identity, then do an insert...

Brett

8-)

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-14 : 10:01:06

By the way, is there anyway to bulk copy to a table variable?
I try:

Declare @table table(vchrACHRecord varchar(4000), intID integer PRIMARY KEY IDENTITY (1,1))
bulk insert @Table from "I:\Windows\A040204.txt"

It gives me an error, it seems it doesn't recognize @Table.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 10:10:07

Nope....

Can you give us some feedback to the questions asked?

Brett

8-)

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-14 : 10:18:58

Brett:

Each textfile has about 10,000 - 20,000 records. I tried formatfile,but it still doesn't work. Bridge table works, but it requires one more step to insert data to bridge table then insert again to the final table.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-14 : 10:43:55

quote:
Originally posted by TAS

By the way, is there anyway to bulk copy to a table variable?
I try:

Declare @table table(vchrACHRecord varchar(4000), intID integer PRIMARY KEY IDENTITY (1,1))
bulk insert @Table from "I:\Windows\A040204.txt"

It gives me an error, it seems it doesn't recognize @Table.

No. You can only do this to a temp table.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 11:24:09

quote:
Originally posted by TAS

Brett:

Each textfile has about 10,000 - 20,000 records. I tried formatfile,but it still doesn't work. Bridge table works, but it requires one more step to insert data to bridge table then insert again to the final table.

20k records..what's it take? 10 seconds?

Anyway what version of SQL? And why didn't the format card work...

Can you post what you built?

Brett

8-)

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-14 : 11:51:36

Bulk insert tblTempACHInfo from "I:\Windows\A040204.txt" With (FirstRow=2,FORMATFILE='P:\SQLBAS\ACHFmt.txt')

This is the result:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

If I delete IDENTITY cloumn in tblTempACHInfo, it can import without any problem, otherwise, the above is result I get.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 12:05:09

Yeah it's the format file that I was asking to see....

I this the error message it erroneous...

What's in your 4th column in the format file for the IDENTITY column?

Make it a zero then try

Brett

8-)

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-14 : 12:14:18

This is the format file:

6.0
1
1 SQLCHAR 0 94 "\r\n" 1 vchrACHRecord

If it's not right, how to correct it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources