Bulk Insert - 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

Transact-SQL (2000)

Bulk Insert

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-13 : 03:56:26

Hello, I have problem to import data from txt file the way I want to.
Txt File name - ForImport.txt and looks like:
13052005123658465656
13052005664456546456
13052005546956564565
...
I have two tables in Database:
FileList
(ID, FileName, crDate, importDate, flag)
FileContent
(FileListID, text)

In first table I have evidence about files which I imported and in second there are data from file. For example
FileList:
1 'ForImport.txt' '2005-05-12 08:21:15.000' '2005-05-12 12:00:00.000' 0
FileContent:
1 13052005123658465656
1 13052005664456546456
1 13052005546956564565

But ... I use bulk insert to import content of file ForImport.txt and I have a problem with first column. How could I import data only to second column and to manage first one on my own.
BULK INSERT FileContent
FROM '\\...\ForImport.txt

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-13 : 05:11:46

Have a look at this:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp[/url]

Mark

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-13 : 05:40:50

Thank you Mark .. that's it.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-13 : 06:23:09

No probs

Mark

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-13 : 07:12:13

Me again

I made bcp.fmt file:

8.0
1
1 SQLCHAR 0 1500 "\r\n" 2 TEXT SQL_Latin1_General_CP1250_CI_AS
(like it is in book online)

FileContent.Text varchar(1500)

The length of one row in ForImport.txt is 510, but somehow, after bulk insert it is imported only first 506 characters.

BULK INSERT FileContent
FROM '\\...\ForImport.txt
WITH (FORMATFILE = '\\...\BCP.FMT')

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-13 : 08:23:40

forget the last question.

I couldn't see whole row in QA. But when I use select right(text,10) everything was there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources