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

Asked

Viewed 775 times

3

I have several tables with the same number of columns. and I want to insert the records of these tables into a new table. Anyone have an idea? because I only managed to insert one and does not accept the rest of tables I have.

select  *
from DATATRAN2013_CSV A 
UNION
 select  * from DATATRAN2014_CSV B 
 UNION
select *from  DATATRAN2015_CSV C 
UNION
select * from DATATRAN2016_CSV D
database sql-server

  • This is an Insert with sub query

3 answers

5

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

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

  • thanks friend for the tip I realized it when I was trying to kill myself yesterday inserted this.

5


You can insert 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 is interesting to name the columns so as not to err on the order of INSERT.

Importantly, the UNION will delete repeated lines, so if you want to keep duplicate records between different tables, use the UNION ALL.

  • UNION discards repeated lines (if any).

  • @Josédiz this invalidates the answer to the point of receiving one downvote? It is interesting to read the privileges when you acquire them, for example here to use the forum correctly.

  • @Sorack was a great help

0

Suggestions in this reply consider that a new table should be created and that the source tables are declared with columns in the same sequence.

(1) It is possible to 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;

This way less memory resources are used.

(2) If you want to merge the source tables, before creating the new table, here is 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;

Please note that you must use UNION ALL to achieve full union. If you use only UNION, in addition to the process slowing down, repeat lines will be discarded.

(3) To have absolute control, I suggest that you do not use * in the column list of the source tables but set 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;

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.