copy recorset from table to other table

[sal21]

I use adojet + vb6 and access database.
Admit have table_old and table_new with same structure( number of field and format type field into same access database).
I want to copy in "one shot only" all value in columns from old_table to new_table where field1 in table_old="eur"....
Please query.
Tks.

Note:
please with fast method i can have approx 450.00 in old_table

[HansV]

You can use the following SQL:

INSERT INTO [table_new] SELECT * FROM [table_old] WHERE [field1]="eur"

[sal21]

Hummmmmmmmmm....
And if i want to select each fild from old table to filed new table, for example:
insert filed4,field5 from old to field9,field15 into new

[HansV]

Use

INSERT INTO [table_new] ([field4], [field5]) SELECT [field9], [field15] FROM [table_old] WHERE [field1]="eur"

This will only fill field9 and field15, and leave all other fields blank.

[sal21]

Absolutly the HansV best!
Tks for patience...

[sal21]

Use

INSERT INTO [table_new] ([field4], [field5]) SELECT [field9], [field15] FROM [table_old] WHERE [field1]="eur"

This will only fill field9 and field15, and leave all other fields blank.

Hi friend, peraph i'm stupid!
With this line the code create a new table cc_daily_1 and insert records from cc_daily to cc_daily_1...
But i just have cc_daily_1!

How ro recopy all records without create a new table?

Code: Select all

strSQL = "SELECT * INTO CC_DAILY_1 FROM CC_DAILY"
    GAF_CONN.Execute (strSQL)

[HansV]

If you want to delete the existing records from cc_daily_1, then copy records from cc_daily into cc_daily_1, you have to execute two SQL statements:

' Delete records
strSQL = "DELETE * FROM CC_DAILY_1"
GAF_CONN.Execute strSQL
' Copy records
strSQL = "INSERT INTO CC_DAILY_1 SELECT * FROM CC_DAILY"
GAF_CONN.Execute strSQL

[sal21]

If you want to delete the existing records from cc_daily_1, then copy records from cc_daily into cc_daily_1, you have to execute two SQL statements:

' Delete records
strSQL = "DELETE * FROM CC_DAILY_1"
GAF_CONN.Execute strSQL
' Copy records
strSQL = "INSERT INTO CC_DAILY_1 SELECT * FROM CC_DAILY"
GAF_CONN.Execute strSQL

i'm sure... i love YOU

[sal21]

If you want to delete the existing records from cc_daily_1, then copy records from cc_daily into cc_daily_1, you have to execute two SQL statements:

' Delete records
strSQL = "DELETE * FROM CC_DAILY_1"
GAF_CONN.Execute strSQL
' Copy records
strSQL = "INSERT INTO CC_DAILY_1 SELECT * FROM CC_DAILY"
GAF_CONN.Execute strSQL

i'm sure... i love YOU

sorry me now, end if i want to copy field1 from cc_daily to field5 cc_daily_1, field9 from cc_daily to field11 cc_daily_1... ecc

[HansV]

See my reply in Post=15058 higher up in this thread.