BULK INSERT syntax for a specific example

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

Transact-SQL (2005)

BULK INSERT syntax for a specific example

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2008-04-02 : 09:30:00

Hi,

I have a DTS package which, apart from other steps, loads a text file to the SQL Server (2000) table. The problem is that I need to do it for at least 20 text files, may be more.
As far as I have no experience in parametrizing DTS packages, I suppose it will be easier for me to do it with BULK INSERT.

What would be an equivalent BULK INSERT syntax for this load
(parameters taken from the DTS package mentioned)?

---------------------------------------
load a text file: path/txtfile.txt
(txtfile.txt on the network drive)
to an SQL Server 2000 table: db1.dbo.table1

Select File Format:
- Delimited
- File type: ANSI
- Row delimiter: Comma
- Text qualifier: Double Quote
- First row has column names: NO

Specify Column Delimiter:
- Tab
---------------------------------------

Thanks
Katarina

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 09:44:38

>> Text qualifier: Double Quote
That will be a problem as it will need a format file to get rid of the quotes.
See
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
The example for quote encapsulated strings is near the end
The format file will be something like this but with tabs instead of commas for delimiters.
8.0
5
1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 "\"," 1 s Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

Do all the files have the same format? If so it's easy to do it with dts. I would get the filenames in an SP and call the package multiple times with the filename (and tablename if necessary).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

katarina07
Starting Member

31 Posts

Posted - 2008-04-02 : 10:48:35

Hi,

-----------------------------------------
I'm sorry, the Text Qualifier can be also set to "none",

important settings are:
row delimiter: comma
First row has column names: NO
Column Delimiter: TAB

Would it work then?
-----------------------------------------

About the second alternative:
Yes, all text files have the same format. Would I have to make a DTS with 2 parameters (txt file,output table) then?
I also dont have the rights to run a DTS from Query Analyzer.
-----------------------------------------

At this moment, I would prefer the BULK INSERT if possible.

thx
Katarina

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 11:17:44

in that case
bulk insert mytbl from 'c:\myfile.txt' with (rowterminator=',',fieldterminator='<put a tab here>')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources