How to create table from text file SQL Server 2005 Express

aacrz
aacrz used Ask the Experts™
on
I have about 4 text files that contain data (comma delimited), and would like to transform them into Tables in SQL Server 2005 Express.  
Does SQL server 2005 EXPRESS have this funcionality?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
sure
google for
sql server bulk insert

Author

Commented:
can you tell me the command if you know it by experience?
Professional Troublemaker^h^h^h^h^hshooter
Commented:
There are two ways to do this -- and I've tended to use BULK INSERT:

BULK INSERT <import.tablename> from '<full.path.to.text.file>'

Open in new window

As momi_sabag says, there is a goodly amount of information available if you search, but I'd start here.

When I've done imports in this way, I end up with one long string record per line of text, and I then run that table through other functions to break it into the fields in need.  (I've also been fortunate that my recent projects have been fixed length records.)

You'll likely need to break your comma separated values out, once you've imported them to a temporary holding table.  A couple seconds of googling reveals these suggestions:
http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial