Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with a sql query....

 
Ram singh
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
Could you some1 please tell me how to merge all the rows of one old table into a new table. New table has all the fields of an old table PLUS one more field whose value is always "TRUE". I would really appreciate help on this matter. THANKS a lot
 
John Haake
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For Oracle (no boolean, but substituting 1):
INSERT INTO new_table
SELECT old_table.*, 1 FROM old_table;
This works if old_table isn't to large (huge transaction).
If you really are Oracle then the SQLPLUS "COPY" command will do the trick for very large tables.
If not Oracle, then you'll have to break up the insert with a WHERE clause on the SELECT.
 
Ram singh
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using Mysql. and the extra field(of name type) in the new table has a constant value of PRIVATE. I tried your query by putting PRIVATE instead of 1. It didn't work though..It gave me a Column private doesn't exist which is true since column type doesn't exist in old table but in a new table. Any other ideas??. Im really stuck on this..i would appreciate any help..Thanks..
 
John Haake
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my first reply I was making a few assumptions:
1) the new table looks just like the old one except one new field on the END of the row.
2) I assumed your trouble was more of a large transaction issue than a SQL understanding issue.
sorry.
The SELECT statement as found in my INSERT statement above provides the new VALUES to be inserted into the new table. What you want to substitute for my "1" is a VALUE that will go into the new column at the end of the new table. The database will insert the values provided by the SELECT into their respective columns into the new table based on POSITION. A safer, an probably more correct way of coding the SQL would have been to specifically name all columns involved (I took the short cut).
Let say the old table has three columns a, b, c, and the new table has an extra column d (which you want to be "true"):
INSERT INTO new_table ( a, b, c, d )
SELECT old_table.a, old_table.b, old_table.c, TRUE
FROM old_table;
The SELECT gets the VALUES of a,b,c for each row in old_table and adds a hard-coded value TRUE to make up a new row for new_table.
I hope that helps.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic