Copy data from one table to another in sql server

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected.

We can copy all columns from one table to  existing table:

INSERT INTO  Destinationtable

SELECT * FROM SourceTable 

We can copy only the columns we want to into existing table:

INSERT INTO Destinationtable

            (column1,

             column2,

                 column3,

                  column4)

SELECT    column1,

             column2,

                 column3,

                  column4

FROM   SourceTable 

Limitations and Restrictions

When you copy a column that has an alias data type from one database to another, the alias data type may not be available in the destination database. In such a case, the column will be assigned the nearest matching base data type available in that database.