Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SELECT INTO 1
- Thread starter dpaulson
- Start date
-
1
- #2
- Thread starter
- #3
I'm using Jet.
Here's an example
SELECT QtyOnHand, UnitCost, ItemID FROM Inventory
and insert those values into another table (InvTransactions_Detail). I know that I can create a recordset and loop though it and do an insert into command, but I was trying to learn a new way using just the connection.execute command. I have managed to do exactly this with the select into , but only into a new table and not into an existing table.
David Paulson
Here's an example
SELECT QtyOnHand, UnitCost, ItemID FROM Inventory
and insert those values into another table (InvTransactions_Detail). I know that I can create a recordset and loop though it and do an insert into command, but I was trying to learn a new way using just the connection.execute command. I have managed to do exactly this with the select into , but only into a new table and not into an existing table.
David Paulson
As I mentioned, you can use INSERT INTO.
[tt]INSERT INTO InvTransactions_Detail (QtyOnHand, UnitCost, ItemID)
SELECT QtyOnHand, UnitCost, ItemID
FROM Inventory[/tt]
The query design window can be useful for creating examples of SQL. Build the query and then look at SQL view. You mat need to clean the SQL up a bit.
You can post on Jet SQL in forum701.
[tt]INSERT INTO InvTransactions_Detail (QtyOnHand, UnitCost, ItemID)
SELECT QtyOnHand, UnitCost, ItemID
FROM Inventory[/tt]
The query design window can be useful for creating examples of SQL. Build the query and then look at SQL view. You mat need to clean the SQL up a bit.
You can post on Jet SQL in forum701.
- Thread starter
- #5
It looks simple enough now that I see it. I've tried the sql statement. It produces no errors, but it neither inserts any rows. But at least it a start. I'll play with it some more. Thanks for the link to the SQL forum. I've been a member here for > 7 years and never new it existed. Thanks again
David Paulson
David Paulson
Are there any records in Inventory? Are duplicate records allowed in InvTransactions_Detail? If you are using CurrentDB.Execute, you must use dbFailOnError, if you do not wish the SQL to fail silently.
- Status
Similar threads
- Locked
- Question