Programatically Import Text File Into SQL Server 2005

[crj...@earthlink.net]

I've got a text file that has fixed length fields/records delimited by
a pipe "|" that will need to be imported into my SQL Server 2005
database on a monthly basis. What would be the best way to achieve
this?

I've been looking into using a BULK INSERT but I'm not sure if this is
the way to go.

I've worked with SQL Server 2000 off and on in the past, but SQL
Server 2005 is totaly new to me.

Thanks,

CR Junk

[J. M. De Moor]

BULK INSERT is pretty easy. The tedious part is laying out the format
file. We insert text to a #TempStaging table and populate the permanent
tables from there.

Joe

[TheSQLGuru]

In no particular order:

1) Import/Export wizard
2) OPENROWSET
3) bcp
4) BULK INSERT
5) INSERT with an EXEC xp_cmdshell ... type command.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

<crj...@earthlink.net> wrote in message
news:6a0d36fb-6549-49f6...@f3g2000hsg.googlegroups.com...

[Erland Sommarskog]

(crj...@earthlink.net) writes:
> I've got a text file that has fixed length fields/records delimited by
> a pipe "|" that will need to be imported into my SQL Server 2005
> database on a monthly basis. What would be the best way to achieve
> this?
>
> I've been looking into using a BULK INSERT but I'm not sure if this is
> the way to go.

That or the command-line tool BCP. BULK INSERT is best if the
file lands on the SQL Server machine, or a network drive visible from
SQL Server, and you want to start the load from SQL Server.

If the file is a machine that is not accessible from SQL Server, BCP is
better.

The tools are functionally the same to a large extent. There are
some subtle differences, since they are different implementations, but
they are usually interchangeable.

> I've worked with SQL Server 2000 off and on in the past, but SQL
> Server 2005 is totaly new to me.

BCP and BULK INSERT are nothing new to SQL 2005. SQL 2005 has
OPENROWSET(BULK which is new, and which use engine as BULK INSERT.
While it has some advantages, I tend to stick with BULK INSERT myself.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[crj...@earthlink.net]

Thanks for everyone's suggestions. I think I'm probably going to use
the BULK INSERT so I can call store my query as a stored procedure and
pass parameters to it.

One question: If I use a statement similar to the online help
example (shown below), then I should not need a format file. Am I
understanding this correctly?

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

Thanks,
CR Junk

[Erland Sommarskog]

(crj...@earthlink.net) writes:
> One question: If I use a statement similar to the online help
> example (shown below), then I should not need a format file. Am I
> understanding this correctly?
>
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )

Right. As long the file is regular enough and maps exactly to your
table you don't need a format file.

[crj...@earthlink.net]

> Right. As long the file is regular enough and maps exactly to your
> table you don't need a format file.

Thanks! After making a few changes to my tables and getting the user
accounts setup correctly, the BULK INSERT statements work great.

CR Junk