SQLFilter: Simple Data Import Question
April 17, 2007 9:53 AM Subscribe
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...
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...
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) (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
Best answer: 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
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
Response by poster: Thank you, sirs. Shoulda thought of that last one.
posted by joshjs at 10:14 AM on April 17, 2007
posted by joshjs at 10:14 AM on April 17, 2007
« Older Moving to Erie PA and need lots of info | So to summarize I have always been thin and in... Newer »
This thread is closed to new comments.
posted by joshjs at 9:56 AM on April 17, 2007