Insert Into...select From...

[rahulpatel]

Been trawling sites for a few weeks trying to find answers about my SQL and have finally come up with this SQL (VB6 and MS Access):

sSQL = "INSERT INTO try([mytext], [mydate]) SELECT quantity From Stores Where CatalogueNo = '" & txtSearch.Text & " ' "

This uses two tables and drags the field 'quantity' where txtSearch = a certain value in the stores table and inserts it into the try(mytext) table. I have a field, mydate which I want to have a todays date but according to this SQL it needs to have a field from stores.

Can I INSERT todays date INTO the try(mydate) table without having to SELECT it FROM the the Stores table?

[FunkyDexter]

Yes, you simply substitute the value into the select list. I'm afraid I don't know access's function to return the current date but sql server is getDate(). Assuming Access's is the same it would look something like this:-

sSQL = "INSERT INTO try([mytext], [mydate]) SELECT quantity, getDate() From Stores Where CatalogueNo = '" & txtSearch.Text & " ' "

When doing a select you don't neccessarily need to select a field from a table, you can select variables, constants, functions... anything that has a scalar value.

[rahulpatel]

I thought it would throw an exception due to 'date' not being in a field. That's good to know.

I don't think I can UPDATE one table and INSERT values INTO a different table with the same SQL though can I?

Thanks for your help and quick reply.

[FunkyDexter]

I don't think I can UPDATE one table and INSERT values INTO a different table with the same SQL though can I?

No... but your original query didn't mention that . Give us a bit more info about what you're trying to do and we'll see if we can help. Normally you have to do separate inserts and updates though.

[rahulpatel]

No... but your original query didn't mention that . Give us a bit more info about what you're trying to do and we'll see if we can help. Normally you have to do separate inserts and updates though.

I was thinking out loud, I think! The code you provide and the explanation has allowed me to do what I planned to do. Just need to create a relationship between two tables and I'm sure I'll be good to go.

Thanks again.