I don't believe this is the proper place to get this information, but I'm not sure I know the full extent of the problem. Is this import you are trying to do something you are trying to make an automated process or is it something you will do infrequently and just need a tool for the import? If you just need a tool for the import, SQL Server Enterprise Manager provides everything you need.

In that case open Enterprise Manager. Expand the "Microsoft SQL Server Nodes" branch and keep expanding until you see what looks like a list of folders. Then expand the "Databases" folder. Right-click on the database into which you would like to import the data (or create a new database first) and select "All Tasks | Import Data..."

At this point, you'll be prompted with a wizard that will walk you through the import proceess. Click "Next>>". On the next screen, you'll see a drop down list labeled "Data Sources". In that drop down, select "Text File" which is the second from the bottom in the list on my system. From there you should be able to figure out the rest.

Now, of course, if you want to do this regularly with a script to automate something, you will need to install perl on Windows (I use ActiveState). Then from a command line run ppm. This is similar to the CPAN shell which allows you to install modules on the fly. You can, at the prompt, type: install DBI. Once that has finished, type: install DBD-ODBC. Once these two modules are installed, you should be able to connect to a DSN through ODBC.

The perl code to do what you want would look something like:

#!C:/perl/bin/perl

use strict;
use DBI;

my $filename = shift || die "Please provide a filename.\n";

my $dbh = DBI->connect( "dbi:ODBC:DSNNAME", "", "",
    {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or
die "Unable to connect: " . $DBI::errstr . "\n";

# Open your file
open( INFILE, $filename ) or die "Couldn't open file for reading.$!\n"
+;
while( $line = <INFILE> )
{
     my @cols = split( "\t", $line );
     # build insert string with items in @cols
     my $sql = "INSERT INTO...";
     $dbh->do( $sql ) or die "Couldn't insert record. $!\n";
}
close(INFILE);
$dbh->disconnect;
[download]

This code is untested!!

This method is not ideal for large amounts of data because you are doing one insert per record which means lots of overhead. In that case I would use the method I mentioned first of using Enterprise Manager. There may even be a way to automate that.

Good luck.

-Matt

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.