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

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
>
>

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" <(E-Mail Removed)> wrote in message
news:4B9B954B-BAA8-4541-A11B-(E-Mail Removed)...
>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