[RESOLVED] Best method for moving record to another table?

[WarrenW]

I have some records I need to move from one table to another. But in the second table it has some extra fields that need to be filled in also. For example, if I sell an item, I want to move the item record to the sales table but also include the selling price, date, etc. in the extra fields.

1. I can load all the fields from the inventory table to variables and then insert them in the Sales table with the extra fields.

or

2. I can use an Insert command like below and then do an Update command to update the extra fields. I wish I could do it all in one sql statement.

rs.Open "INSERT INTO Sales SELECT * FROM StoreInv WHERE ItemNo = '" & ItemNo2 & "'", cn

Thanks!

[BruceG]

The SQL statement can be something like this:
INSERT INTO Sales (field1, field2, ... fieldn, salesonlyfield1, salesonlyfield2)
SELECT field1, field2, ... fieldn, somevalue1, somevalue2
FROM StoreInv
WHERE ....

[si_the_geek]

You can do it in one statement... and you should be doing more of what I am here; not specifying the fields (to Insert or Select) is a recipe for disaster - what happens when either table had new fields added? or exisiting fields have their positions changed?

This is the kind of SQL you should be running already:

INSERT INTO Sales (field1, field2)
SELECT fieldA, fieldB FROM StoreInv WHERE ItemNo = 'value'

..and this is how to "add" fields to it:

INSERT INTO Sales (field1, field2, field3)
SELECT fieldA, fieldB, 3.25 as SellingPrice FROM StoreInv WHERE ItemNo = 'value'

edit: how slow was I?

[WarrenW]

So in your example, the field3 is the added field for the sales info that I want to include? And the 3.25 as SellingPrice that you have added, is that how I would actually do it just like you typed by putting the value in as the 3.25? How would I do that for a string? Would I do it like 'Abc' as SalesClerk ? Thanks alot for your help! Saves alot of work for me and on the database.

INSERT INTO Sales (field1, field2, field3)
SELECT fieldA, fieldB, 3.25 as SellingPrice FROM StoreInv WHERE ItemNo = 'value'

[si_the_geek]

All of your assumptions are correct.

[WarrenW]

I just added it into my code and it worked perfect!!!! THANKS!