...

View Full Version : copy data between different tables with lots of fields



babelfish
08-03-2011, 04:24 PM
hi guys

i have had to update a massive subcontractor section on our management system.

some fields are the same but many are different. basically i want to copy the old table's data into the new one, but having hundreds of fields i dont want to have to specify each field individually.

can i do it so that it just copies fields that are common to both?

guelphdad
08-03-2011, 05:31 PM
you can query the INFORMATION_SCHEMA database and that would give you a list of columns. that would at least save you typing them out.

Old Pedant
08-03-2011, 08:08 PM
Actually, you don't *NAVE* to specify field names at all in an INSERT. So long as the VALUES( ) section has one value for each field.

But miss one field and you are hosed.

Personally, I'd bite the bullet and do it right. You can indeed use the INFORMATION_SCHEMA, but it's not going to help you figure out that WIDGET in table 1, field 37, needs to go into FRAMITZ in table 2, field 103.

guelphdad
08-03-2011, 08:35 PM
I was guessing that fields "common to both" could have same names. But really no straightforward way to do this.

babelfish
08-04-2011, 08:11 AM
I was guessing that fields "common to both" could have same names. But really no straightforward way to do this.

yes, they do, all fields that are common are exactly the same. annoying there is no easy way to do this.

babelfish
08-04-2011, 11:27 AM
isnt there just a simple way to merge 2 tables?

every method ive seen wants me to have to list 200+ columsn manually. what a ball ache.

guelphdad
08-04-2011, 03:36 PM
again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

So you can do:


SELECT column_name FROM information_schema.columns
WHERE
table_scheme = 'thenameofyourtablegoeshere'


Dump that to an outfile and then it saves you typing the column names out.

babelfish
08-09-2011, 10:51 AM
again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

So you can do:


SELECT column_name FROM information_schema.columns
WHERE
table_scheme = 'thenameofyourtablegoeshere'


Dump that to an outfile and then it saves you typing the column names out.

hmm...

i take it you meant table.schema? even so i get empty set....

SELECT column_name FROM information_schema.columns WHERE table_schema = 'subcontractors_old';

more digging needed....

babelfish
08-09-2011, 10:55 AM
again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

So you can do:


SELECT column_name FROM information_schema.columns
WHERE
table_scheme = 'thenameofyourtablegoeshere'


Dump that to an outfile and then it saves you typing the column names out.

hmm...

i take it you meant table.schema? even so i get empty set....

SELECT column_name FROM information_schema.columns WHERE table_schema = 'subcontractors_old';

more digging needed....

edit:

SELECT column_name FROM information_schema.columns WHERE table_schema = 'dbname' and TABLE_NAME = 'table_name';

works.

babelfish
08-09-2011, 11:23 AM
ok, im being dumb now.

i have both lists of table fields, but whats the best way to check lists of 200+ fields against each other to see which are common?

sorry if this is a newbie question, wife is pregnant and after 6 months of sleeping for 3 or 4 hours a night i feel like a lobotomised zombie! :(

babelfish
08-09-2011, 11:31 AM
update:
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/

very useful :)

babelfish
08-09-2011, 11:57 AM
all done, thanks for your help guys!

the lobotomized zombie got it all working! :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum