April 5, 2007 at 2:28 pm
HI. I loaded a table from an access database into a new sql server table using the DTS wizard. All worked fine. Now the developer said one of the fields did not come across correctly. I cannot reload the whole table because the users have already made changes to the database. Is there a way to import just 1 field from the access database table into this sql table? I tried to see how to do this with DTS but all I can get it to do is import a whole table.
Any suggestions?
Juanita
April 5, 2007 at 2:35 pm
If you have access to the database through Query Analyzer, the best way would probably be to load all the data in the existing table into a temp table, then create a script to recreate the table exactly the way you need it (you can just generate a CREATE TABLE script using the existing table as a starting point, then edit the script to make the necessary change you need to the field in question).
Then, drop the existing table, run your CREATE TABLE script, and load the data back in from the temp table. If you are changing the datatype, be sure to add in any cast/convert clause if necessary if the datatypes are not compatible...
Luke
April 5, 2007 at 3:09 pm
Juanita,
What needs to be changed about the column that the developer says is wrong?
Greg
Greg
April 5, 2007 at 3:11 pm
The data in that column is wrong. She said it copied the column values from the column that preceeds this particular column in question.
April 5, 2007 at 4:43 pm
Ah. My suggestion is to import the Access table again into a different table in SQL Server then run an update statement to change the data in the bad column. Something like
update badtable set badcolumn = goodtable.badcolumn
from badtable,goodtable
where badtable.pkcolumn = goodtable.pkcolumn
badtable is the original table with bad data in one column; goodtable is the second table you imported with good data in the column; pkcolumn is whatever the primary key is in both tables.
Greg
Greg
April 5, 2007 at 8:55 pm
I would also check to see if your DTS package transformations have been changed. It is easy when you highlight a column to scroll on your mouse which will cause the field mapping (transformation) to be changed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply