Strange inserting problem

I am putting a table together with the following code:

create table temp
(pnum integer,
pnam varchar(30));

insert 开发者_开发技巧into temp(pnum)
select player_number
from players;

insert into temp(pnam)
select player_name
from positions;

It basically works except for the fact that, while both columns are preeent throughout the whole table, they are filled out sequentially. I want all the data from the second select to appear after the data from the first select ON THE SAME LINE. At present, I simply get a bunch of blank lines in the pnam column (while pnum fills out nicely), then get a bunch of blank lines in the pnum column (while pnam fills out nicely). IF anyone knows how to solve this, your prompt reply will be incredibly appreciated!

Yep - the problem is you're doing 2 discrete and seperate inserts, so SQL doesn't know that you want the numbers and the names to match up. You need to rewrite the Select statement into one.

Assuming that both the players and the positions table contains a filed called player_number, you can use this to tie the two tables together (if not, pick a unique field thats in both tables that ties the data together and use that.

insert into temp(pnum,pnam)
select player_number,player_name
from players inner join positions on players.player_number = positions.player_number

Things to note:

Line 1 - see how we are now inserting into both columns at the same time. This will give you the data layout you're looking for

Line 2 - see how we're selecting two bits of data to insert

Line 3 - this is where we join the two tables together (using player_number columnn from both). We need to join them together because in Line 2 we need to select one column from each.

Hope that makes sense. If not, shout. Also, maybe read up on inserting rows using INSERT and SELECT and using inner joins