Programatically Import Text File Into SQL Server 2005 - 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

Programatically Import Text File Into SQL Server 2005 - 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.

Programatically Import Text File Into SQL Server 2005 - Erland Sommarskog

(crjunk@earthlink.net) writes:

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.


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, esquel@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

Programatically Import Text File Into SQL Server 2005 - crjun

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

Programatically Import Text File Into SQL Server 2005 - crjun

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

Programatically Import Text File Into SQL Server 2005 - Erland Sommarskog

(crjunk@earthlink.net) writes:

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


--
Erland Sommarskog, SQL Server MVP, esquel@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

Programatically Import Text File Into SQL Server 2005 - crjun

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

CR Junk