I need help with copy table to table with asp

[phiber102]

Hello!

I been working on a problem for a while. I need to copy data from 2 tables and merge them in to 1 table that already exists. i need to extract a column called "bpo" and a column called "usr" from 2 different tables called anv and bpo and then copy them into a table called mybpo.

I guess it shood look something like this:

sql = "INSERT INTO mybpo SELECT usr FROM anv where usr ='" & usr & "' and SELECT bpo FROM bpo "

but i cant get it to work, tried allot of combinations, Please help me

/Regards Phiber

[jhardman]

Phiber,

I'm not sure I understand you, let me see if I have this straight:
You have three tables in the same database, "bpo", "anv", and "mybpo". You need to take values from two of those tables (bpo and anv) to insert new records into the third table. Is this right? It sounds like a job for a custom view or a stored procedure rather than a whole table. Or do you just need to pull up a single query with info from all three tables? That is what "Join"s are for.

Jared

[phiber102]

hi sorry for the late answer
Its a one time copy every time a user register as a "builder" on the site, then all the "build plans/schematics" will automatically copy in to the mybpo, with bpo + anv on the same id (table anv holds the user data), which ofc will increase the number on the "id" tag like :

usr1+bpo1 id=1
usr1+bpo2 id=2
usr1+bpo3 id=3
usr1+bpo4 id=4
usr1+bpo5 id=5
usr1+bpo6 id=6

usr value and bpo value are collected through 2 tables in the database, 1 reason, to make entry for new build plans easier and when the user register as builder all plans in the database gets available unique to usr.

I know i can do it the other way around and list the plans, and then let the user add just by clicking on them, but this is really time consuming and therefore not an option

thx for shown interest btw :D

[jhardman]

Make the insert one at a time, but you can definitely automate it with a loop. use a subquery that joins the two other tables and returns the two values you want.

Jared

[phiber102]

ok great idea thx, will try to play around with that