Hi!!
I have 2 tables, named tableA and tableB. I want to insert data from tableA into tableB.
Below is the structure of tableA (data originally stored under MS Access, now I have converted it to be used under MySQL) and tableB (data stored in MySQL, which is used by a forum for login purpose). The two tables are stored within the same database.
tableA (extracted, the original one has 10 coloum)
----------------------------------------------------
coloum name | type | example data
----------------------------------------------------
idno | text | 0509A888
dob | date/time | 1989-08-31 00:00
surname | text | CHAN
tableB (extracted, the original one has 15 coloum)
----------------------------------------------------
coloum name | type | example data
----------------------------------------------------
username | text | 0101A234
password | text (md5) | eadg484eaga484g8ag8agrg55aaad (md5 from dob[date] - type: yyyymmdd)
email | text | abc@domain.com
What I want to do is to insert all the data "idno" in tableA into "username" in tableB, and "dob"(only the date is needed, i.e. yyyymmdd) in tableA into "password" in tableB. No other coloum(field) is required, just the said two coloum.
How can I do in SQL (MySQL)? Using a sql statement or a php file??
I'm new in SQL or MySQL, so, I hope all of you understand what I'm talking about!! :P
Thanks in advance!!!
sridhar_423
02-16-2006, 06:16 AM
this is a SQL Query..
insert into TABLEB select idno,to_char(dob,'yyy-mm-dd mm:ss'),'' from TABLEA
wwhassupp
02-16-2006, 06:31 AM
this is a SQL Query..
insert into TABLEB select idno,to_char(dob,'yyy-mm-dd mm:ss'),'' from TABLEA
Thanks for your reply!
How about the coloum name of tableB? Do I need to consider it? Or just copy & paste your query?
I want the "idno" coloum in tableA insert into "username" coloum in tableB.
And how about the password? md5? Use the similiar query you've provided?
Thanks again!!
sridhar_423
02-16-2006, 06:37 AM
the query will do the following..
irrespective of the columns in TABLEB, it just takes the values from TABLEA and simple inserts the values in COLUMNS username and password with email as blank.
This will insert new rows. no update will happen.
after you run the insert query, TABLEB will be..
username password email
7656 2006-01-16 abc@bbc.com
8453 2005-01-16 ksd@bbc.om
123 2006-01-16
423 2005-01-16
wwhassupp
02-16-2006, 07:37 AM
the query will do the following..
irrespective of the columns in TABLEB, it just takes the values from TABLEA and simple inserts the values in COLUMNS username and password with email as blank.
This will insert new rows. no update will happen.
Thanks for your reply!
The coloum has different name, the one in tableA is called idno, and the one in tableB is called username.
How about the sequence of the coloum have changed, if, for example:
tableA:
idno | dob |surname
tableB:
email |username |password
is your query still workable?
sridhar_423
02-16-2006, 07:51 AM
no, the fields shud match
idno | dob |surname --> TABLE1
user | pas |email -->TABLEB
insert into TABLEB select idno,to_char(dob,'yyyy-mm-dd mm:ss'),'' from TABLEA
idno | dob |surname --> TABLE1
email| user |pas -->TABLEB
then, the query wud be...
insert into TABLEB select '',idno,to_char(dob,'yyyy-mm-dd mm:ss') from TABLEA
clear..?
wwhassupp
02-16-2006, 10:01 AM
no, the fields shud match
idno | dob |surname --> TABLE1
user | pas |email -->TABLEB
insert into TABLEB select idno,to_char(dob,'yyyy-mm-dd mm:ss'),'' from TABLEA
idno | dob |surname --> TABLE1
email| user |pas -->TABLEB
then, the query wud be...
insert into TABLEB select '',idno,to_char(dob,'yyyy-mm-dd mm:ss') from TABLEA
clear..?
Thanks!
The difference is the position of ". It should match with the sequence of the tableB, right?!
So, if I've, say, 3 more fields after "password" field in tableB, the query should be:
insert into TABLEB select '',idno,to_char(dob,'yyyy-mm-dd mm:ss'),","," from TABLEA
Is that right?!
What else if I don't add the last 3 "(because it may be blank, i.e. no data should be insert) in the above query, should it still work?
sridhar_423
02-16-2006, 11:44 PM
yes , it'll work only if you add the blank quotes. the number of fields that you are retrieving from TABLEA shud match the number of fields which are going to be inserted in TABLEA. as u dont have tat many fields in TABLEA/ unwanted fields, u simply put blank quotes. you can alsospecif some string or a sequence. like tat whichever suits you.
wwhassupp
02-17-2006, 05:29 AM
yes , it'll work only if you add the blank quotes. the number of fields that you are retrieving from TABLEA shud match the number of fields which are going to be inserted in TABLEA. as u dont have tat many fields in TABLEA/ unwanted fields, u simply put blank quotes. you can alsospecif some string or a sequence. like tat whichever suits you.
Hi!!
I've tried the query today. But something was wrong.
First, I execute the below query in phpMyAdmin. (Note: my MySQL version is 4.0.26)
INSERT INTO tableB
SELECT '', student_id, to_char(dob, 'yyyy-mm-dd mm:ss'), '', '', '', '', '', '', '', '', email_adress, '', '', '', '', '', '', '', '', '', '',name_in_chinese, '', '', '', '', '', '', '', '', '', '', '', ''
FROM tableA
and the server shown the below message.
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(dob,'yyyy-mm-dd mm:ss'), '', '', '', '', '', '', '', '
Then, I try the below query, and it works fine. (i.e. without to_char())
INSERT INTO tableB
SELECT '', student_id, '', '', '', '', '', '', '', '', '', email_adress, '', '', '', '', '', '', '', '', '', '',name_in_chinese, '', '', '', '', '', '', '', '', '', '', '', ''
FROM tableA
Also, I try the below query, and it works fine too. (i.e. just use dob)
INSERT INTO tableB
SELECT '', student_id, dob, '', '', '', '', '', '', '', '', email_adress, '', '', '', '', '', '', '', '', '', '',name_in_chinese, '', '', '', '', '', '', '', '', '', '', '', ''
FROM tableA
I guess I do something wrong with the to_char().
The output format of dob in tableA is: 1980-12-25 00:00
And I want the format to be used in tableB is: 19801225
Then, I will md5(19801225).
So, how can I do?
Please give me some advice.
Thanks again!!!!!
sridhar_423
02-17-2006, 11:05 AM
sorry dude.. i'm donno MySql..the query that i gave is an SQL query (Oracle Database). You shud mention MySql in the post itself to avoid this type of confusion.
newayz, post one more thread., stating MySql as your database. Many functionalities which Oracle Database supports, MySql doesn't.
wwhassupp
02-17-2006, 11:30 AM
sorry dude.. i'm donno MySql..the query that i gave is an SQL query (Oracle Database). You shud mention MySql in the post itself to avoid this type of confusion.
newayz, post one more thread., stating MySql as your database. Many functionalities which Oracle Database supports, MySql doesn't.
Thanks for your reply!!
I forgot to mention it in the post. Although your query is an SQL query, it help me a lot!!!
Thanks for your kind help!!! :D