Magic query for table operation in SQL

Posted on

Sometimes, we feel very annoyed where doing job in database management. Building apps without good database is like building a skyscraper without cement. It’s imposible, actually, although it’s posible but it’s harder to do that. Well, we agree if database is so important to building apps. If we understand that, we can be more carefull when designing database.

It’s ok when we do our own project, but how about developing old project that built by others? We can’t be sure that the database and normalization is good. I told you, I have some problem. In my office, I have do old project that it’s built by others company before. And you know, they have so bad database design.

In short, I must read the unstructure database relations, analysis the bussines process and do some magic trick to old database before I writing code in system app. I need some new tables, because I found anomaly. Actually, I think I can do my jobs without doing some tricks, but again I think about how about maintenance the apps? I do not want this to bother me at the end.

Well, let’s back to writing notes about query. There are some table mighbe helpfully to building or maintance our project.


#1 Copy all table structure and data to new table that we don’t have yet

CREATE TABLE new_table AS SELECT * FROM old_table;

#2 Copy selected column and data from old table to new table that we don’t have yet

CREATE TABLE new_table AS SELECT column1, column2, etc FROM old_table;

#3 We also can join the old table with others table

CREATE TABLE new_table AS SELECT * FROM old_table JOIN old_table2 On params1=params2;;

#4 If you need some condition, it’s easy to do that

CREATE TABLE new_table AS SELECT * FROM old_table Where'3';

Looks like simple query? Yeah, they are!

Some times we need something simply to solving hardly problem. And, in fact they are is some of very-very much query modification to do table operation in SQL. I know something else, but I’ll update this post someday. 😀

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments