Convert access query to sql and export statement to text (2 Viewers)

[zentel]

Hi,
I am a beginner in ms access and working on a project of moving some data in access to an oracle database.
Please i need assistant on how to create an insert query from an access table and export the sql to a text file. This way i can run the sql statement that has been export into the text file in sql *plus.

I have been searching but have not had any solution and i don't know were to begin. Please any help will be greatly appreciated.

Thank you

 

[plog]

Can you create an odbc connection in Access to the Oracle database? If so, you can just link the table you want it to go into and use the access query builder to make an APPEND query.

If that's not possible, I would create a comma seperated text file from your Access database via the wizard, then import that into Oracle.

 

[zentel]

Thanks for the quick response. Unfortunately I can't have a direct connection with the Oracle database. The database server is being hosted by a vendor and the only connection I have been allowed so far is using an oracle sql client to query data to and from it.

Thank you

Can you create an odbc connection in Access to the Oracle database? If so, you can just link the table you want it to go into and use the access query builder to make an APPEND query.

If that's not possible, I would create a comma seperated text file from your Access database via the wizard, then import that into Oracle.

 

[arnelgp]

you can create an insert query in access and import it to text file using docmd.OutputTo.

Docmd.OutputTo acOutputQuery, "queryName", acFormatTXT, "d:\Output.sql"

 

[zentel]

Thanks for the great response, very much appreciated. I have few questions...is append query same as insert query in access? if so i created an append query and used the suggestion you made to export the query as *.sql but it did not export the sql statement but rather the results of the append query.
I want to be able to get the actual sql so i can run in sql plus or any other oracle sql client.

Please any additional suggestion will be greatly appreciated.

Thank you

you can create an insert query in access and import it to text file using docmd.OutputTo.

Docmd.OutputTo acOutputQuery, "queryName", acFormatTXT, "d:\Output.sql"

 

[plog]

It's a lot more complicated than that, he left out the hardest part--the creation of SQL statements.

I would check with the tool you are going to use to import data with and see what type of files it can accomodate. Moste likely a comma seperated text file will be your best option. Again, run through the import options of the tool you are going to use and see what options it allows.

 

[zentel]

Thanks for you feedback.
Pardon me if my question is not clear, but what i want to is to be able to convert the query or table into SQL statment like below
This:
INSERT INTO contacts (contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.');

or this:
INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM') INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft') INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google') SELECT * FROM dual;This way i will be able to just run the sql code in my oracle sql client.

Please any additional help will be greatly appreciated.

thank you

It's a lot more complicated than that, he left out the hardest part--the creation of SQL statements.

I would check with the tool you are going to use to import data with and see what type of files it can accomodate. Moste likely a comma seperated text file will be your best option. Again, run through the import options of the tool you are going to use and see what options it allows.

 

[plog]

Pardon me if my question is not clear, but what i want to is to be able to convert the query or table into SQL statment like below
This:
INSERT INTO contacts (contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.');

Your request was clear, but from your uncertainty about how to do it, probably above your skill level. An export to a file like this:

FieldName1, FieldName2, FieldName3, ...
James, 13, 2/1/2014
Sally, 19, 3/3/2009

would be a lot simpler to achieve. Most likely your import tool can easily handle that. Doing it your way is a lot more difficult because with it you will need to construct that SQL string.

Since you have your heart set on it though, this is how: Create a query based on the table you want to export. Then in the bottom section of the query create a calculated field like so:

InsertStatement: "INSERT INTO contacts (contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.');"

Except, in the part of the 'VALUES (' you need to replace thos actual values with field names that hold those values and escape your string so the values themselves appear instead of the hardcoded values.

 

[zentel]

Thank you very much, your suggestion has peaked my interest. unfortunately i am a newbie in access so don't know where to begin. I only started learning access because of this migration project.

Please can you give me a mock on your proposal this way i can have some foundation to work on.

again, Thank you

Your request was clear, but from your uncertainty about how to do it, probably above your skill level. An export to a file like this:

FieldName1, FieldName2, FieldName3, ...
James, 13, 2/1/2014
Sally, 19, 3/3/2009

would be a lot simpler to achieve. Most likely your import tool can easily handle that. Doing it your way is a lot more difficult because with it you will need to construct that SQL string.

Since you have your heart set on it though, this is how: Create a query based on the table you want to export. Then in the bottom section of the query create a calculated field like so:

InsertStatement: "INSERT INTO contacts (contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.');"

Except, in the part of the 'VALUES (' you need to replace thos actual values with field names that hold those values and escape your string so the values themselves appear instead of the hardcoded values.

 

[zentel]

Please any help still on this? I am not sure how to go about this but is it possible to have a code to go through the results of a query and use the print method to print an insert sql statements for each record into a text file.

Please any help will be greatly appreciated

Thank you

 

[The_Doc_Man]

Let me ask a direct question first...

Is it your goal to create a SERIES of single-record INSERT INTO queries, one per record of your source table, that contain the literal values to be appended for each record because you can't directly connect to your ORACLE target to do the APPEND or INSERT queries from Access to ORACLE?

Before we try anything else, I think we need to know if that is what you wanted.

The confusion might come about because normally you would just run the INSERT INTO query from your source table to your destination table where your syntax would be more like "INSERT INTO target-table (value-list) SELECT value-list FROM source-table ;" But if you answer yes to my direct and narrow question then your answer is a bit more complex.

 

[plog]

Yes, he wants a file of INSERT statements because he figured out how his tool can execute those.

I've tried to explain that linking the Oracle tables would be the best option, but he says he doesn't have access that way.

I then tried to tell him that exporting a raw data file from Access and importing it via his tool would be the 2nd best option. He refuses to explore his tool to see how that is done.

He has his heart set on pasting in a bunch of INSERT statements. However, he doesn't know how to generate those and I'm not really too keen on helping him with that option without exploring other simpler methods of importation.

He wants someone to generate the SQL to create all those INSERT statements. Knock yourself out doing that.

 

[zentel]

Thank you The_Doc_Man,
Your question is what is am looking for. What I was thinking off was a way to print the insert statements line by line per record, to the test file where the values contains the records of the query or table.

Hope this helps. Thank you

Let me ask a direct question first...

Is it your goal to create a SERIES of single-record INSERT INTO queries, one per record of your source table, that contain the literal values to be appended for each record because you can't directly connect to your ORACLE target to do the APPEND or INSERT queries from Access to ORACLE?

Before we try anything else, I think we need to know if that is what you wanted.

The confusion might come about because normally you would just run the INSERT INTO query from your source table to your destination table where your syntax would be more like "INSERT INTO target-table (value-list) SELECT value-list FROM source-table ;" But if you answer yes to my direct and narrow question then your answer is a bit more complex.

 

[zentel]

Hello Plog,
Is not that I have not explore those option, the sql client I am limited to right now is the sql* plus. I am guessing you understand how that will be difficult to work with aside having to put in bunch of sql statements. At least that is all I now.
I came to this forum for help and to learn and will do anything to make my project a success. I have researched and treid to use the print feature but I am not good at vba or access with no success, that is why why I am seeking help. If the options provide will have work I will be more than happy.

Please hope you understand

Yes, he wants a file of INSERT statements because he figured out how his tool can execute those.

I've tried to explain that linking the Oracle tables would be the best option, but he says he doesn't have access that way.

I then tried to tell him that exporting a raw data file from Access and importing it via his tool would be the 2nd best option. He refuses to explore his tool to see how that is done.

He has his heart set on pasting in a bunch of INSERT statements. However, he doesn't know how to generate those and I'm not really too keen on helping him with that option without exploring other simpler methods of importation.

He wants someone to generate the SQL to create all those INSERT statements. Knock yourself out doing that.

 

[plog]

I apologize, I thought you figured out one way and had your heart set on it.

On researching SQL *Plus, I see a mention of an SQL *Loader which will mass import from a file. Is there a way you could get SQL *Loader?

 

[zentel]

Thanks Plog,
Please no need to apologize, I understand where you coming from.
Also I am not sure if you are referring to bulk loader... one of the DBA'S I am working with had me tried that on a text file i pulled from earllier suggestion, but after the upload there was some in consistency especially in the data. Some items had funny characters and so on, so all uploaded data had to be purged
Tge data had formating issues and that was the reason for the funny characters. So in nut she'll we are in did waters and had to purge. I figured with all this complications this process will have been one more option to try.
The access dB that was created was done poorly I am told, but as of now the data in there is about 150000+ records.

Again any direction is greatly appreciated.

I apologize, I thought you figured out one way and had your heart set on it.

On researching SQL *Plus, I see a mention of an SQL *Loader which will mass import from a file. Is there a way you could get SQL *Loader?

 

[plog]

I don't want to write that full expression for you, because its going to be a pain. I will write one to get you started. I'll use some SQL you posted earlier to aim for:

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM');

Let's assume the 1000 and 'IBM' values come from a table in Acces called 'YourTable' and the fields 'ID_Field' & 'Name_Field' respectively.

To generate that SQL statement for ever record in YourTable, you would use this SQL:

SELECT "INSERT INTO suppliers (supplier_id, supplier_name) VALUES ('" & [ID_Field] & "', '" & [Name_Field] & "');" AS InsertStatement
FROM YourTable

In your Access database, create a new query, go into SQL view of it and paste that code. Be sure to replace 'YourTable' with the name of your table and the field names I used with the actual field names. Then run it. You will get your INSERT statements.

2 notes:

1. If your data values have single quotes in them (e.g. Johnson's Automotive), then the statements will fail when you run them in your import tool. I'm hoping you don't have any such data, but if you do let me know.

2. To add more fields to the INSERT, you would follow the pattern of that statement. You would add the Oracle field name inside the first set of parenthesis (supplier_id, supplier_name) and then continue to build a string as I have done for the second set of parenthesis. (double quotes, single quote parenthesis, double quotes, field....)

 

[zentel]

Thanks Plog, much appreciated.
I have also been researching trying and for what if worth I did something in excel which works but I am trying replicate that in Access with no success.
Please see attach image.

Any help will also to have this work is access will be appreciated as well.

Thank you

I don't want to write that full expression for you, because its going to be a pain. I will write one to get you started. I'll use some SQL you posted earlier to aim for:

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM');

Let's assume the 1000 and 'IBM' values come from a table in Acces called 'YourTable' and the fields 'ID_Field' & 'Name_Field' respectively.

To generate that SQL statement for ever record in YourTable, you would use this SQL:

SELECT "INSERT INTO suppliers (supplier_id, supplier_name) VALUES ('" & [ID_Field] & "', '" & [Name_Field] & "');" AS InsertStatement
FROM YourTable

In your Access database, create a new query, go into SQL view of it and paste that code. Be sure to replace 'YourTable' with the name of your table and the field names I used with the actual field names. Then run it. You will get your INSERT statements.

2 notes:

1. If your data values have single quotes in them (e.g. Johnson's Automotive), then the statements will fail when you run them in your import tool. I'm hoping you don't have any such data, but if you do let me know.

2. To add more fields to the INSERT, you would follow the pattern of that statement. You would add the Oracle field name inside the first set of parenthesis (supplier_id, supplier_name) and then continue to build a string as I have done for the second set of parenthesis. (double quotes, single quote parenthesis, double quotes, field....)

 

[zentel]

Hello all,
I have been searching, studying and learning from post from here and other places to help resolve my issue. I have been able to have this work with a code in excel and need help replicating the code to work in access. As stated in previous post, i am not good with VBA and struggling badly

Please any help will be greatly appreciated.. Thank you

Please see code below

Private sub saveme()
application.screenupdaing=false
filepath=thisworkbook.sheets("data").cells(1,2)&"\"
t_name=sheets("data").cells(2,1)
outputfilename=filepath
'file pointer
finalFile=outputfilename & "mytextfile"
prmap=freefile
open finalFile for output as prmap
for rs = 3 to 100
wirecast= ""
if (sheets("data").cells(rs,3) <> "") then
c1 = sheets("data").cells(rs,1)
c2 = sheets("data").cells(rs,2)
c3 = sheets("data").cells(rs,3)
c4 = sheets("data").cells(rs,4)
c5 = sheets("data").cells(rs,5)
c6 = sheets("data").cells(rs,6)
c7 = sheets("data").cells(rs,7)
c8 = sheets("data").cells(rs,8)
c9 = sheets("data").cells(rs,9)
c10 = sheets("data").cells(rs,10)
'Prining text to filepath
wirecastinsert = "IINSERT INTO Customers (CustName, ConName, Addr, City, PCode, Country,EMcontact,other,other,other)
VALUES ('" & c1 & "','" & c2 & "','" & c3 & "','" & c4 & "','" & c5 & "','" & c6 & "','" & c7 & "','" & c8 & "','" & c9 & "','" & c10 & "');"
print #prmap, wirecastinsert
wirecastcommit = "commit;"
print #prmap, wirecastcommit
else
wirecast = wirecast & "</JOB>"
endif
Next rs
Close prmap
end sub

 

[plog]

As stated in previous post, i am not good with VBA and struggling badly

Ok, then how about try my SQL solution?