Hi all



I posted this last night and had a response back from Tomm (thanks!) 

unfortunately I found out that the fields in the live table and the 

archive table will not be the same name.



So I will have something like 



select "JO" as archive_type, code as category_code from live_jobs



and then 



select "PT" as archive_type, part_number as category_code from live_parts



The "as" clause will be make the column name the same as that in the 

archive.



I then need to put this into the archive file which I currently do by 

looping through the recordset and firing off insert into commands.



What I am ideally looking for is a method to avoid doing this loop through 

the recordset and post directly to the archive file



Appreciate any pointers 



Regards

Graham

INSERT INTO

    Table2

    (

        field1,

        field2,

        field3

    )

    SELECT

        fielda,

        fieldb,

        fieldc

    FROM

        Table1



Allows you to insert fields "fielda", "fieldb" and "fieldc" from Table1 into

fields "field1", "field2" and "field3" in Table2



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: <gbrown@c...>

Subject: [asp_databases] Inserting a record set into a table (revisited!)





: I posted this last night and had a response back from Tomm (thanks!)

: unfortunately I found out that the fields in the live table and the

: archive table will not be the same name.

:

: So I will have something like

:

: select "JO" as archive_type, code as category_code from live_jobs

:

: and then

:

: select "PT" as archive_type, part_number as category_code from live_parts

:

: The "as" clause will be make the column name the same as that in the

: archive.

:

: I then need to put this into the archive file which I currently do by

: looping through the recordset and firing off insert into commands.

:

: What I am ideally looking for is a method to avoid doing this loop through

: the recordset and post directly to the archive file



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks Ken 



Sounds like exactly what I am looking for.



Regards

Graham





> INSERT INTO

>     Table2

>     (

>         field1,

>         field2,

>         field3

>     )

>     SELECT

>         fielda,

>         fieldb,

>         fieldc

>     FROM

>         Table1

> 

> Allows you to insert fields "fielda", "fieldb" and "fieldc" from Table1 

into

> fields "field1", "field2" and "field3" in Table2

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: <gbrown@c...>

> Subject: [asp_databases] Inserting a record set into a table (revisited!)

> 

> 

> : I posted this last night and had a response back from Tomm (thanks!)

> : unfortunately I found out that the fields in the live table and the

> : archive table will not be the same name.

> :

> : So I will have something like

> :

> : select "JO" as archive_type, code as category_code from live_jobs

> :

> : and then

> :

> : select "PT" as archive_type, part_number as category_code from 

live_parts

> :

> : The "as" clause will be make the column name the same as that in the

> : archive.

> :

> : I then need to put this into the archive file which I currently do by

> : looping through the recordset and firing off insert into commands.

> :

> : What I am ideally looking for is a method to avoid doing this loop 

through

> : the recordset and post directly to the archive file

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>