Update Records in 1 table,Then transfer to other table. can it be done 3

[jmiller79]

I have a table called UPS. I import my flat file into the table weekly and then run the following update.

UPDATE UPS
SET [Invoice Number] = RIGHT([Invoice Number], LEN([Invoice Number]) - 1)

This takes off the very first 0 in every record. My issue is when I import more data into the table and I have to run that update it will take a 0 off every record, including the ones I already ran this update on. Problem…… So is there a way to setup a table were I an import the New records into then run the update and then it will transfer the New Updated Record to the UPS table. So I do not cut and paste into the UPS table. Any help or direction would be great.

Thank You
Joe

 

[donutman]

Read about using the DTS wizard in BOL. You should be able to perform that operation during the import stage. You might find the info about DTS very helpful for other reasons too.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I usually import into a holding table, perform all the data transformations I need and then send the records to the real table. You can do all of this automatically using steps in DTS.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mkal]

Granted DTS is great way to Extract, Load and Transform data. That being said it might be simpler to just perform a insert into the other table similiar to:


UPDATE UPS
SET [Invoice Number] = RIGHT([Invoice Number], LEN([Invoice Number]) - 1)
go
insert <new_table_name>
select *
from UPS

This is assuming the tables are identical in nature. If this needs to be an automated process create a new job and copy the script into the command window of the steps tab and schedule it to run when needed.

 

[jmiller79]

Thanks Guys and Gals,
This has been very helpful. I gave you guys and gals all stars, have a great rest of the day