July 12, 2011 at 7:53 am
I am not a database designer by any means, graphics are my thing but in this instance i am the only thing available at present.
I have a list of numbers in a text file. I have a table in a database with a column called Box_ID. I need to select the entries with Box_ID numbers that match my text file, and then change the value in a third column to a specific value.
How should i go about doing this?
Thanks!
July 12, 2011 at 7:58 am
First things first. You need to get your file of numbers into SQL. If there are a huge number of them, they will need to be imported into a table in the db. If it's only smalll number, you might be able to cut-and-paste them into a query.
How are your numbers stored in the text file? One number per line? Comma separated?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 12, 2011 at 8:41 am
if you're more of a visual person, you might want to consider looking into SQL Server Integration Services. In SQL Server 2005, this can be found in the Business Intelligence Studio, which is a tie-in with VS 2005, while in SQL Server 2008 it has its own stand-alone interface.
SSIS lets you do a lot of ETL operations - Extract, Transform, Load. Basically, the concept is that you can select a source of data to load into the operation, in your case, the text file (Extract). Then, you can manipulate the data, so that it conforms with the fields you want to deal with (Transform). Finally, you can then commit the data to your database (Load).
In your particular case, what I could recommend is doing a Data Flow task, which begins with a OLE DB Source. The source would be your database table. You could load the table into your project. Then, you have a seperate source, from a flat file, in your case the text file. Using some form of a merge operator, you can then get all the rows from your database which match the rows in your flat file. Finally, you can execute a SQL command against these rows, to perform the update.
July 12, 2011 at 8:59 am
There are about 500 7 digit numbers in the text file, currently one number per line but i can format the text file as needed.
July 12, 2011 at 9:41 am
To import your file into a table:
From SSMS, make sure you have the object explorer open and your database name displayed on the left hand side.
Right click on your db name and select "Tasks | Import Data " from the menus presented. For your data source, select "FLAT FILE SOURCE" from the dropdown list, and hit the browse button to find and select your file. From there it should be fairly self-explanatory, you designate the database where you want the imported table to be created.
Get back to us when you've got the data imported, or if you run into any problems. (If you are successful, please tell us the name of the newly-imported table.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 13, 2011 at 5:18 pm
ok so i imported the text file which became a table called "temp_info"
July 13, 2011 at 5:29 pm
It should only have a single column called [Column 0]. Correct? Or did you give the column a name?
Assuming it's called [Column 0], see if the following code returns the rows you want to update.
select *
from temp_info t
join SomeOtherTable s on S.Box_ID = t.[Column 0]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply