Merge two or more existing database tables/columns into one

Posted: December 1, 2008 in SQL
Tags: , ,

I have two tables for e.g. tblStudents and tblSchools. Both are having different columns. For a reason I need to merge the two tables into one temporary table.

For example “tblStudents” contains the following design.

StudentID FirstName LastName Address DOB

Where as “tblSchools” contains the following design.

SchoolID Name Address IsSecodarySchool

Now, you would like to merge this two table columns into one temporary table then you can use the following sql script.

SELECT * INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Here, I have mentioned the “where” clause because I wanted to copy only the columns and not the data. You can ignore this clause if you want to copy the entire data too.

You can add as many tables you want to merge by separating with comma (,) between the table names.

You can add specific columns from specific tables by using table alias and column name combination.
For example
SELECT tblStudent.FirstName,tblStudent.LastName,tblSchool.Name INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Happy Programming!

Advertisements
Comments
  1. Arun says:

    hi..it was a very informative article.
    But if you want to add the datas also,in that case what would be he query look like?
    i have two tables named such as Final_EDM_Part1 and Final_EDM_Part2 with filednames such as id,mobileno,emailid and name.Structure of both the tables are exactley same.Now i want to merge these two tables into a new table called Final_EDM_Final(with field names id,mobileno,emailid and name) along with datas.

    Please help me.it is bit urgent.

  2. hspinfo says:

    Here, I have mentioned the “where” clause because I wanted to copy only the columns and not the data. You can ignore this clause if you want to copy the entire data too.

  3. Muhammad Khawar Ashraf says:

    This is fine for creating structure but WRONG for copying data.

    The join between the two tables is a FULL OUTER JOIN, it will multiply Table1 records to Table2 and you’ll get too many duplicate records. So for example you have 3 records in tblStudent and 4 records in tblSchool, you will get 12 records in final table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s