0

i have 2 tables. TABLE A COLUMNS - aid , aname TABLE B COLUMNS - bid , bname

from table A, i will pick up data from column 'aid', AND insert it in 'bid' column of table B , but in bname column of table B, i will insert a new value. how do i do this?

create table A(aid int,aname char)

insert into A values(111, 'e') 


create table B(bid int, bname char)


insert into B (bid,bname)

bid will take value from the query : select aid from a bname will get a new value -m

expected result should be : THE TABLE B WILL HAVE :

bid bname --- ----- 111 m

sqlchild
  • 8,754
  • 28
  • 105
  • 167

1 Answers1

2

Try this:

insert into b (bid, bname) select aid, 'm' as bname_fixed_val from a

Two facts enabled the solution above:

  1. The insert .. select clause allows you to insert the values returned with any select.

  2. You can return constant values as fields with select, like for instance:

    SELECT 0 as id, 'John' as name

Combining these two points together, I used an insert..select clause to select the field value from the first table (aid), along with a constant value for the second field (m). The AS bname_fixed_val clause is simply a field alias, and can be omitted.

For more information on SQL, here 's a link: http://www8.silversand.net/techdoc/teachsql/index.htm, although googling it wouldn't hurt also.

Ioannis Karadimas
  • 7,746
  • 3
  • 35
  • 45
  • thanks a lot sir, it worked , can i have a simple explanation also on this query , which you advised – sqlchild Mar 04 '11 at 16:12