How can I make a copy of multiple tables in a new table using SQL SERVER?

You can enter using the template below:

INSERT INTO tabelan(coluna1, coluna2, coluna3, coluna4, colunaz)
SELECT coluna1, coluna2, coluna3, coluna4, colunaz FROM tabela1
UNION
SELECT coluna1, coluna2, coluna3, coluna4, colunaz FROM tabela2
UNION
SELECT coluna1, coluna2, coluna3, coluna4, colunaz FROM tabela3
UNION
SELECT coluna1, coluna2, coluna3, coluna4, colunaz FROM tabelaz

It's interesting to name the columns so they do not go wrong in the order of INSERT .

It is important to note that UNION will delete repeated lines, so if you want to keep duplicate records between different tables, use UNION ALL .

Using Union , the columns must have the same name and be in the same order.

Note: In this case you will not be copying, just adding the result of the selection of several tables. To copy to another table, you need to make a insert with the result of these Unions

The suggestions in this answer assume that a new table must be created and that the source tables are declared with columns in the same sequence.

(1) You can insert one table at a time, without the need for UNION ALL:

-- código #1
SELECT coluna_1, coluna_2, ..., coluna_n 
   into DATATRAN_CSV
   from DATATRAN2013_CSV;

INSERT into DATATRAN_CSV (coluna_1, coluna_2, ..., coluna_n)
  SELECT coluna_1, coluna_2, ..., coluna_n 
     from DATATRAN2014_CSV;

INSERT into DATATRAN_CSV (coluna_1, coluna_2, ..., coluna_n)
  SELECT coluna_1, coluna_2, ..., coluna_n 
     from DATATRAN2015_CSV;

INSERT into DATATRAN_CSV (coluna_1, coluna_2, ..., coluna_n)
  SELECT coluna_1, coluna_2, ..., coluna_n 
     from DATATRAN2016_CSV;

In this way, fewer memory resources are used.

(2) If you want to merge the source tables, before creating the new table, here's another option:

-- código #2
with cteDATATRAN as (
SELECT * from DATATRAN2013_CSV
union all
SELECT * from DATATRAN2014_CSV
union all
SELECT * from DATATRAN2015_CSV
union all
SELECT * from DATATRAN2016_CSV
)
SELECT * 
  into DATATRAN_CSV 
  from cteDATATRAN;

Note that you must use UNION ALL to get the complete union. If you use only UNION, and the process slows down, repeated lines are discarded.

(3) To have absolute control, I suggest you do not use * in the column list of the source tables, but rather define the column names:

-- código #3
with cteDATATRAN as (
SELECT coluna_1, coluna_2, ..., coluna_n 
  from DATATRAN2013_CSV

union all
SELECT coluna_1, coluna_2, ..., coluna_n 
  from DATATRAN2014_CSV

union all
SELECT coluna_1, coluna_2, ..., coluna_n 
  from DATATRAN2015_CSV

union all
SELECT coluna_1, coluna_2, ..., coluna_n 
  from DATATRAN2016_CSV
)
SELECT * 
  into DATATRAN_CSV 
  from cteDATATRAN;