SQL query required for moving data

[marklcfc]

I am relatively new to Mysql / PHP and have a database currently set up, but I think I have done it partly wrong.

I have a table containing around 1,500 entries. In these entries there is some data in 5 columns. I have been told it would be better if this info was in a new table.

I would like to move data from these 5 columns and I would also like it to copy the ID as this is important. How would I go about this?

I will give names of the table and columns if that will help.

Current table name = players

Columns = playerid, buyfrom, buydate, buydate2, buyfee, buynote.

I need to move this data from each entry into a new table called transfers. Can anyone advise??

[firey]

I'd probably select all the data... loop through the data building an insert query out of the data for the entry into the new table.

So like.

SELECT playerid, buyfrom, buydate, buydate2, buyfee, buynote

FROM players

INSERT INTO (newTable)

(field1,2,3,4,5)

VALUES

Then do a foreach(result r in query)

split up your r (or if it's already an array) then you would append the following

(r[1],'r[2]',r[3]',etc,etc),

then remove the last "," character. Then just run the built query.

[kaborka]

In MS T-SQL, you could say

insert transfers (playerid, buyfrom, buydate, buydate2, buyfee, buynote)

select playerid, buyfrom, buydate, buydate2, buyfee, buynote from players

if PlayerID is an identity column, you must first use a Set statement to turn Identity_Insert ON for the Transfers table, and turn it off afterwards. I don't know how that's handled in MySQL

[marklcfc]

I'd probably select all the data... loop through the data building an insert query out of the data for the entry into the new table.

So like.

SELECT playerid, buyfrom, buydate, buydate2, buyfee, buynote

FROM players

INSERT INTO (newTable)

(field1,2,3,4,5)

VALUES

Then do a foreach(result r in query)

split up your r (or if it's already an array) then you would append the following

(r[1],'r[2]',r[3]',etc,etc),

then remove the last "," character. Then just run the built query.

I wish I could understand that :(

I'm ok up until the VALUES part and everything that followed. :/

[Squirrelington]

In MS T-SQL, you could say insert transfers (playerid, buyfrom, buydate, buydate2, buyfee, buynote) select playerid, buyfrom, buydate, buydate2, buyfee, buynote from players if PlayerID is an identity column, you must first use a Set statement to turn Identity_Insert ON for the Transfers table, and turn it off afterwards. I don't know how that's handled in MySQL

Indeed, <3 me some Microsoft SQL.

[Tjcool007]

Insert ... Select could work.

INSERT INTO transfers
  SELECT playerid, buyfrom, buydate, buydate2, buyfee, buynote
  FROM players

You'll still need to create the transfers table manually first though.

[Wilhelmus]

If you have the 'transfers' table already created:

INSERT INTO transfers (playerid, buyfrom, buydate, buydate2, buyfee, buynote)
  SELECT playerid, buyfrom, buydate, buydate2, buyfee, buynote FROM players ORDER BY playerid;
[/CODE]

If not:
[code]
CREATE TABLE transfers (
  SELECT playerid, buyfrom, buydate, buydate2, buyfee, buynote FROM players ORDER BY playerid
);

# add index and change playerid to auto inc.
ALTER TABLE transfers ADD PRIMARY KEY (playerid);
ALTER TABLE transfers CHANGE playerid playerid INT(11) NOT NULL AUTO_INCREMENT;