Quote: (sic)
i have 5 tables with same column names but different records,
can i add the records form all tables into 1 table and display it?
If you have 5 tables with the same column names but different records then what you do have is a very bad database design.
Revisit your database design.
For example: You might have some tables like
[Student]
,
[Teacher]
,
[Parent]
,
[CateringStaff]
etc.
All of those tables will have things like [FirstName],[LastName],[DateOfBirth], etc etc. All the same.
The better design is to have a table called
[Person] with columns like [FirstName],[LastName],[DateOfBirth], etc etc. plus an additional column
[PersonType]
which can link to another table
Create table [PersonType](Id int, PersonType nvarchar(125))
This latter table has a row for "Student", "Teacher", "Parent", "Caterer" etc etc.
If you want to get all the people on your database it is a simple one-table query :
SELECT * FROM Person
If you want to get all of the teachers it is another simple query:
SELECT * FROM Person P
INNER JOIN PersonType PT on P.PersonType = PT.Id
WHERE PT.PersonType = 'Teacher'
and so forth.
If you really, really want to keep your five tables (please don't do it that way) then you have no recourse other than to use UNION. However, you
do not have to use a temporary table, and you absolutely do not need to use MERGE.
UNION ALL
does not remove duplicate rows but
UNION
will. So ALL you need is
SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3
UNION
SELECT * FROM Table4
UNION
SELECT * FROM Table5
(With the caveat that you should never use SELECT * in situations like this, list the columns you want explicitly)