insert from one table into another

esthera · #1 (**permalink**) 05-21-07, 03:19

i have

insert into membersarchive
select firstname,lastname from members

how can i do this but I also want to add an additional field not in members called username and add this to the values being inserted?

ashish_mat1979 · #2 (**permalink**) 05-21-07, 05:02

You have to specify from where you want to pick username value and fieldnames explicitly in Insert into query; it will look somewhat like this, where users is another table for example from where you have to pick username:

insert into membersarchive (firstname,lastname,username)
select firstname,lastname,username from members,users where members.userid=users.userid

gvee · #3 (**permalink**) 05-21-07, 05:26

Quote:

Originally Posted by esthera

I also want to add an additional field not in members called username and add this to the values being inserted

Where is this extra value held?
Without knowing this information we can't help you!
Here's a quick quess

Code:

INSERT INTO membersarchive (firstname,lastname,username)
SELECT	 firstname
	,lastname
	,Left(surname,6) + Left(firstname,2)
FROM	 members

aschk · #4 (**permalink**) 05-21-07, 05:42

Where are you getting the variable username from? Is it coming in from a script? Is it coming from another table? Do you want to insert a blank?

Blank :

Code:

INSERT INTO 
  membersarchive(firstname,lastname,username)
SELECT firstname,lastname,'' FROM members

Variable from script (php) (foreach user)

Code:

INSERT INTO 
  membersarchive(firstname,lastname,username)
SELECT firstname,lastname,'{$php_username}' FROM members WHERE <some clause>

From another table

Code:

INSERT INTO
  membersarchive (firstname,lastname,username)
SELECT firstname,lastname,username from members m
JOIN users u ON m.u=u.userid

gvee · #5 (**permalink**) 05-21-07, 05:57

Tiny amendment in Aschk's code (aliases)

Code:

INSERT INTO membersarchive(firstname,lastname,username)
SELECT	m.firstname, m.lastname, u.username
FROM	members m
JOIN users u
	ON m.u=u.userid

Internet Services

Web Development

Digital Marketing

Consumer Tech