copy records MySql

[nadinef]

Hello, I'm having trouble getting records from one table to another! I have one copy of the table with records in them and a new table exactly like it except for additional fields. The SQL i tried was

INSERT users (username, password, userid, userlevel, email, timestamp, firstname, lastname)
SELECT 'usersold', NOW(), username, password, userid, userlevel, email, timestamp, firstname, lastname
FROM usersold

but i get an error: #1136 - Column count doesn't match value count at row 1

Can anyone tell me what i'm doing wrong please?

thanks!

[coldhead]

INSERT statement has eight columns
Select statement has ten,
You need to change the insert to include cols that will hold 'usersold', NOW() values from the select statement.

[nadinef]

hm, Thank you for replying coldhead.

I removed the FROM userold statement at the end of that but it still doesnt work. i now tried:

INSERT newusers (username, password, userid, userlevel, email, timestamp, firstname, lastname)
SELECT 'users', NOW(), username, password, userid, userlevel, email, timestamp, firstname, lastname

(changed table names)

and now i get the error: #1054 - Unknown column 'username' in 'field list'

I've checked to make sure that all the fields are the same in size and structure etc. any idea why it still isnt working?

[mitchind]

hm, Thank you for replying coldhead.
I removed the FROM userold statement at the end of that but it still doesnt work. i now tried:

Why would you remove the FROM <tablename>??? :confused: He didn't suggest that..
You have to tell it where it's getting the data FROM

And (as coldhead was suggesting) you have to tell it which fields are going to receive your values of 'users' and NOW()

Right now you only match up the remaining 8 fields

[nadinef]

hm, ok, i obviously read his reply wrong. he's saying the insert statement has 8 columns, the select statement has 10. I dont see that. I see 8 fields listed in both statements. What am i missing?

[mitchind]

What am i missing?glasses

values of 'users' and NOW()

[nadinef]

yup, still not getting it... can someone please be a little less vague? pretend im the n00b idiot i am k?

[mitchind]

I don't know how any of the previous responses have been vague -

Count the fields in the Insert table
INSERT newusers (username, password, userid, userlevel, email, timestamp, firstname, lastname)
8

Then count the fields in the Select table
SELECT 'users', NOW(), username, password, userid, userlevel, email, timestamp, firstname, lastname
10

Like I said in two previous posts the extra ones that DON'T match are
'users', NOW(), username, password, userid, userlevel, email, timestamp, firstname, lastname

Very first reply ..
You need to change the insert to include cols that will hold 'usersold', NOW() values from the select statement.

[nadinef]

hm, ok, so when i first thought the added 2 fields were being read as "FROM" and "userold" the system is actually thinking "users" and "NOW ()" are fields? so the change should actually be:

INSERT newusers (username, password, userid, userlevel, email, timestamp, firstname, lastname)
SELECT username, password, userid, userlevel, email, timestamp, firstname, lastname
FROM users

Sorry, i assumed the "SELECT 'usersold', NOW ()" was a statement in itself and wasnt interfereing.

thank you for your help

[coldhead]

glasses

ha ha!