Create scripts from Excel
 

I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on a database which will update the value of the
Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to easily
create the scripts as above for the Vendor IDs in my Excel file.

Thank you

S Commar

Create scripts from Excel
 

It would help if you did a manual query of the database in excel while
recording a macro. This would give the basic templet for making the SQL
changes you need.

do the following from Excel worksheet menu
1) Tools - Macro - Record Macro
2) Data - Import External Data - New Database Query
3) Fetch data from the tables you need to modify
4) tools Macro - Stop Recording
5) Alt-F11 (get to VBA window) and get macro from Module Window. Then
modify the code yourself or post the code for others to modify.

"Sam Commar" wrote:

I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on a database which will update the value of the
Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to easily
create the scripts as above for the Vendor IDs in my Excel file.

Thank you

S Commar

Create scripts from Excel
 

Just create formulas on the sheet which give you the SQL
Eg.

="update apdoc set vendid = '" & A2 & "' where vendid = '" & B2 & "';"

Fill down and copy-paste into a SQL command window.

Tim


"Sam Commar" wrote in message
...
I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on a database which will update the value of
the Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g
below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to easily
create the scripts as above for the Vendor IDs in my Excel file.

Thank you

S Commar