Copy data from one table to another without entering field by field

Question

Does anyone know the SQL command that copies the data from one table to another table, without having to clarify all the fields of the original table? I know there is a way but I forgot how it does and I could not find on the net, if anyone knows, I would be very grateful if you could share here with me, I thank you for your attention. #SQL

Answer 1

You can do it 3 ways.

-- sql tabela volátil só existira em tempo de execução
declare @HIERARQUIA table
(
    [ID_HIERARQUIA] [int]  NOT NULL,
    [MICRO] [int] NOT NULL,
    [DESCR] [varchar](250) NOT NULL,
    [MACRO] [int] NULL,
    [POSICAO] [varchar](250) NOT NULL
)

-- 1ª 
-- copia para tabela temporaria ...  ficar no banco tempdb até que finalize o execução.
select * into #HIERARQUIA from HIERARQUIA ;

-- 2ª 
-- copia para tabela volátil só existira em tempo de execução
insert into @HIERARQUIA
select * from #HIERARQUIA;

-- 3ª 
-- copia para uma tabela da base de dados
insert into HIERARQUIA
select * from HIERARQUIA ;

Summarizing. You will need to create a table with the same fields or use a temporary one and use the * in the Select , this maps your fields to another table automatically.

Answer 2

I managed to solve using SELECT * INTO, I thank you all for your attention.

Answer 3

In SQL Server and other databases you can use select into (with some syntax differences)

SELECT * INTO nova_tabela FROM outra_tabela

Inserting Rows by Using SELECT INTO