Using Native SQL Imports

Importing data directly into SQL Server is the fastest way to get your members added to the ListManager database. The following fields should be imported or otherwise filled in the ListManager members_ table:

EmailAddr_ — the recipient's email address

Domain_ — everything after the @ sign in lower case

UserNameLC_ — everything before the @ sign in lower case

List_ — the name of the list the address should be assigned to

Your SQL database server provides many methods to import data. You should use the program that you feel most comfortable with to accomplish this task.

Documented below is one method that uses the program “BCP” that ships with Microsoft SQL Server. This Bulk Copy Program reads a format file to know how to read the data file, and then rapidly adds data to the SQL Server.

The data below gives an example of a format file. In this example the absolute minimum number of columns is provided to load data into the members table. In this case, the EmailAddr field is the only one required, and the rest will be defaulted or auto-computed by the SQL Server.

This example should not serve as a substitute for the documentation provided by Microsoft for the BCP utility.

In this case BCP is expecting a file with a single email address that is no more than 100 bytes long and is terminated by \r\n. If you want to bulk load additional columns of data into the member table you can add columns to this format file.

7.0

1

1 SQLCHAR 0 100 "\r\n" 11 EmailAddr_

The basic arguments to the BCP program are:

BCP members_ in Members.TXT –fmembers.fmt –Sserver –Uuser –Ppassword

Where:

members_ is the name of the members table.

Members.TXT is the text file with email addresses separated by \r\n.

members.fmt is the name of the format file described above.

Server is your server name

User is your user name on that server that has rights to bulk-copy into the database

Password is the password for this user (if any).

There are other arguments to BCP that may apply to your situation. Please consult the provided documentation from Microsoft for further details.

Using DTS to Import to Microsoft SQL Server

There is a bug in DTS that prevents CSV files with identity columns from being imported. The workaround is detailed here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246310

It involves importing the data into MS Access, mapping the Access columns to the SQL server columns, and then doing the import.

The second example is the example that relates directly to the ListManager members_ table.

An easier way to import CSV data is to use the Members: Add Members: Import Members from CSV File feature.