| Author |
Topic |
|
fire3
Starting Member
5 Posts |
 Posted - 2007-05-22 : 09:37:45
|
| Hi,I'm working on a remote database that the company is taking the data from and transfering to a new database. The issue is that that certain data needs to have values changed (i.e. 0,1,2 equals 10,20,50 in the other) however the data values can't be changed permenatly. Is there any way to pull the data while substituting the correct values of the column. The database is sql server 2000. Thanks :0 |
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 09:49:17
|
| Depends on how you're doing the transfer. Are you using DTS or just straight Sql statements? Either way the answer is "yes", as data scrubbing/evaluating is a common task. |
 |
|
|
fire3
Starting Member
5 Posts |
Posted - 2007-05-22 : 10:07:03
|
| I'm more familiar with sql statements, so I would prefer to use them, but I'm not opposed to using DTS. Do you have any advice on how to begin doing this? I've been spinning my wheels for awhile now... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-22 : 10:11:59
|
You can also setup linked server to the remote database and use T-SQL to pull the data over
KH |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 10:17:58
|
quote:
Originally posted by fire3
I'm more familiar with sql statements, so I would prefer to use them, but I'm not opposed to using DTS. Do you have any advice on how to begin doing this? I've been spinning my wheels for awhile now...
I'd create a linked server, or if the source db is not too big, recreate it (via a backup) on the destination box. If I could get by with dts, I'd use it, because it's pretty much all drag and drop.However, whenever I've done a conversion, it's usually insane, and I need to get more hands on. That's where you'll want to use straight sql statements. But dts could handle this as well. |
|
|
|
fire3
Starting Member
5 Posts |
Posted - 2007-05-22 : 10:21:53
|
| Thank you, although its not the tranfer of the data that is causing me greif so much as substituting the data without permenatly changing it. The data that it is being trasfered to is not a sql server so it needs to be in the form csv format which is not a problem, but changing the data (from 0,1,2 to 10,20,50 so it is executable) in a non-permenant way is an issue... |
|
|
|
fire3
Starting Member
5 Posts |
Posted - 2007-05-22 : 10:23:33
|
| Sorry rudesyle, I was writing my response while yours posted. But what sort of sql statements would I use for data conversion (besides update of course) |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 10:46:57
|
quote:
Originally posted by fire3
Sorry rudesyle, I was writing my response while yours posted. But what sort of sql statements would I use for data conversion (besides update of course)
You're either going to have to (a)split the csv into a value or (b)import the csv into a sql table. I'd go with "b". You would then need a select statement like ...select case when csvValue=1 then 10 when csvValue=2 then 20 when csvValue=3 then 30 end as NewValuefrom csvTableAt this point, you can run updates, or inserts, off of that sql statement. Just use it as a temp table, table variable, or derived table. |
|
|
|
fire3
Starting Member
5 Posts |
Posted - 2007-05-22 : 10:49:31
|
| Thans rude, that was really helpful, I appreciate the excellent sql example as well :) |
|
|
|
|
changing values for data transfer