Direct data insert across two DSNs 1

[Extras]

Please advise if this is possible and if it is how should I do it?

I have the following two databases with different DSN's

Database1.mdb
Table Name: List1
Table Fields: PK1, F_Name1, F_Name2, E_mail1
DSN: DSN1


Database2.mdb
Table Name: List2
Table Fields: F_Name2, F_Name2, E_mail2, Cust_Cat
DSN: DSN2

I want to transfer F_Name1, F_Name2, E_mail1 from List1, to F_Name2, F_Name2, E_mail2, but
a) I do not want to transfer PK1 from List1
and, I want to
b) insert a fixed value of 1 within Cust_Cat on List2

I have been tearing my hair out the last day or so - any help would be greatly appreciated!

TIA!

 

[gana78]

Im assuming that you just want to transfer all rows from List1 to List2. If you want to transfer only selected rows, include a WHERE condition in the first query. If this is not what you want, get back with a little more explanation of what exactly you wish to do.

<cfquery datasource=&quot;DSN1&quot; name=&quot;rsList1&quot;>
SELECT F_Name1, F_Name2, E_mail1 FROM List1
</cfquery>

<cfoutput query=&quot;rsList1&quot;>
<cfquery datasource=&quot;DSN2&quot; name=&quot;inList2&quot;>
INSERT INTO LIST2 VALUES ('#rsList1.F_Name1#', '#rsList1.F_Name2#', '#rsList1.E_mail1#', 1)
</cfquery>
</cfoutput>

Hope this helps

 

[Extras]

thanks a lot! I will give it a try right now...

 

[Extras]

Awesomo - worked like a charm..

The table structure on List2 (some fields did not need updating, like autonumber) was a bit different but I managed to get it with some syntax adjustment.