SQLFilter: Simple Data Import Question

April 17, 2007 9:53 AM

How do I merge an external text file into a SQL table (MS SQL Server)?

Let's say I have a table (in MS SQL Server 2000 (or in general, I suppose)) with columns ID, FirstName and LastName, all filled up. I got this sweet new .csv file with ID and MiddleInitial. I want to get the MiddleInitial data in my table, so I add the MiddleInitial column to my table.

(Say I have 20,000 users in this database.)

What's the easiest way to import my data? I've poked around with the wizards, to no avail. It seems like something I shouldn't need to write code to do, so that's sort of what I'm looking for...

posted by joshjs to computers & internet (6 comments total)

To clarify: By "users," I meant "records."
posted by joshjs at 9:56 AM on April 17, 2007

OPENROWSET with the text driver.
posted by majick at 10:01 AM on April 17, 2007

You have 2 options here that I can think of:

1) (No code required, but kludgy) Export a CSV from your current SQL table with the rest of the data, pop it in Excel and sort it by ID, sort your ID/MiddleInitial CSV by ID as well, and paste it in... Reimport the completed dataset as one table.

2) (Sorry, code required) Write a script that goes through each line of the CSV and basically does: UPDATE users SET MiddleInitial='$middleinitial' WHERE ID='$ID'; ....
posted by twiggy at 10:05 AM on April 17, 2007

1. import the text file using DTS. Its pretty easy to do this using Enterprise manager....you just right click on the DB and select import data.
2. use the following sql query.
update table oldtablename
from newtablename
set oldtablename.middleinitial = newtablename.middleinitial
where oldtablename.id = newtablename.id
posted by alkupe at 10:12 AM on April 17, 2007

Thank you, sirs. Shoulda thought of that last one.
posted by joshjs at 10:14 AM on April 17, 2007

(Sirs or ma'ams.)
posted by joshjs at 10:14 AM on April 17, 2007