The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Remapping Query

Hey, all -

I've got a problem at work, and I am hoping for some fresh ideas. My problem is that I need to copy all of the data from a series of related tables into one generic table, mapping columns from the original schema onto the new schema.

My problem is that I am unsure how to do this in the context of SQL. An obvious (and almost certainly wrong) possibility is something like:

INSERT INTO A (a, b, c, d) VALUES ((SELECT aMap FROM B WHERE...), (SELECT bMap FROM B WHERE...), (SELECT cMap FROM B WHERE...), (SELECT dMap FROM B WHERE...));

This is lame. Is there some way to do this with a single SELECT and INSERT statement? Can transactions somehow be used to handle this, while remaining in the database's sphere of influence? I would really prefer not to leave the database to do this, because that opens problems with transactions, etc. although that is certainly an option if necessary. Anyone have any ideas?

Thanks!
BrotherBeal Send private email
Monday, April 28, 2008
 
 
You didn't really give enough info.... but I would guess you will end up using the views:

INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.TABLES

For example I needed to write something that would move a row from a “contacts” table from testing to production and include all of it’s referenced rows – PhoneNumbers, Emails, Addresses, etc.  The problem was the identity columns would all be different in the new environment.  The only way I could do it in a generic way so I could reuse the stored procs on different table was to use the views above.
JBrooks Send private email
Monday, April 28, 2008
 
 
You need to find a join (or union) that can list your old records in the new format. Without knowing the actual mapping, hard to tell if it can be done.
hobit Send private email
Monday, April 28, 2008
 
 
SQL doesn't support inserting multiple rows in a single insert statement. Maybe the specific DBMS you are using supports an extension for such things, but you would need to consult your vendor documentation.

I sympathize with your situation, I have often wanted to move data from one table to another, with some simple transformation, in a single insert statement. It may not make much difference with queries embedded in a program, but it sure would make life easier from an interactive SQL session.
Jeffrey Dutky Send private email
Monday, April 28, 2008
 
 
>> SQL doesn't support inserting multiple rows in a single insert statement. Maybe the specific DBMS you are using supports an extension for such things, but you would need to consult your vendor documentation.

INSERT INTO ... SELECT ... ?


Anyway, it's really unclear what the OP is asking for here. perhaps some example data, before and after, could help.
David Aldridge Send private email
Monday, April 28, 2008
 
 
All -

Here's some clarification of my situation. The data model I am working with supports a number of specific "comment" tables which store user-input information about various pieces of data. I have been tasked to rework this functionality into a generic "comments module." As part of this, I have created a generic table which holds the necessary information for any comment in the system - text, creating user, etc.

What I need to do is populate this new table by copying all the data from specific tables into the new format. It's not exactly a linear mapping, as columns are renamed and one is derived, but it should not involve an elaborate series of joins or other manipulations to move data from one form to another. Basically, all I want to do is copy a row from a specific table and insert it into the new, generic table by mapping column names. Nothing up my sleeve, no fancy tricks - just moving data.

This would be unbelievably simple outside of the RDBMS, but that won't be pretty if anything bad happens. Thus, my question - is there a way to coax SQL into producing this kind of behavior, and if so, any suggestions? It's possible there is a DB-specific trick that enables this (we use DB-2), but I'm not a DBA and their time is hard to come by in my company for things like this. Does this clear things up?
BrotherBeal Send private email
Monday, April 28, 2008
 
 
What DBMS are you using, and what dialect of SQL does it use?

Does your SQL dialect  support  SELECT...INSERT?

It sounds like what you're trying to do is extract, transform, and load the memos into a new database.  There are a variety of ETL tools made for just this purpose, to assist with data warehousing.  If your dialect of SQL is too limited,  you might want to use an ETL tool.
Walter Mitty Send private email
Tuesday, April 29, 2008
 
 
Not sure if I understood you correctly, but here is how I would do it:

Construct a (potentially big) query to gather all the data for the new table into one result set using the UNION operator, adjusting column names and types on the fly:

SELECT a as newA, b as newB ... FROM tableA
UNION
SELECT a2 as newA, b2 as newB ... FROM tableB
UNION
...

Once the result set looks like what you expect, use the already mentioned INSERT INTO newTable SELECT bigUnionQueryGoesHere

UNION will trigger errors if your individual SELECT statements do not produce a 'coherent' result set, thus pointing you to missing conversions/adjustments.

But be aware of implicit type conversions when doing this ...
HeO Send private email
Tuesday, April 29, 2008
 
 
+1 HeO Use the union statement

If some of your tables don't have all the columns in the new table use NULL or a constant value for a default value/placeholder.  The columns in each select statement of the union have to match up.  With DB2 you'll need the cast the nulls to the correct data type.

INSERT INTO NewTable (colA, colB, colC)
SELECT oldA colA, oldB colB, oldC colC
from TABLE1
UNION
SELECT CAST(NULL AS VARCHAR(50)) colA, 2 colB, oldC colC
from TABLE2

and so on...
Ted
Tuesday, April 29, 2008
 
 
I've used SQL to generate SQL when doing this type of thing:
spool dataCopy.sql
select "insert into target values("
      || select * from source1
      || ");";
...
select "insert into target values("
      || select * from sourceN
      || ");";
select "commit;" from dual;
spool off
@dataCopy.sql

You could put commits between each source table so any errors only rollback the source table.  Also easy to re-run the generated SQl (or a protion)

Cheers
Chris Hulan Send private email
Tuesday, April 29, 2008
 
 
Hello, all -

Just FYI, I was able to do this easily through a database-specific quirk. The database tier we run supports queries of the form:

INSERT INTO TableA (value1, value2 .. valueN)
SELECT value1, value2.. valueN FROM... WHERE...

Worked like a charm, although I have been informed that this does not migrate. The alternative was either a series of interior SELECTS (as described above) or programmatically generating and running queries outside of the database's control.

Thanks to all for the input and suggestions!
BrotherBeal Send private email
Tuesday, April 29, 2008
 
 
>> The database tier we run supports queries of the form:

INSERT INTO TableA (value1, value2 .. valueN)
SELECT value1, value2.. valueN FROM... WHERE...


As far as i know they all support that. That is about as basic as SQL can possibly get -- time to read a manual or buy a book.
David Aldridge Send private email
Tuesday, April 29, 2008
 
 
If you are using oracle, dont forget about create table as select

Tuesday, May 06, 2008
 
 
My first thought was using a view of those table and
then select from that view.

But not sure without exactly knowing the relation,
dataset and the goal?
Mr. Smith Send private email
Tuesday, May 06, 2008
 
 
if you are using mysql, you can use this to do bulk inserts (multiple inserts with one statement):

INSERT INTO x (a,b)
VALUES
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')
OSS rocks
Friday, May 23, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz