SQL Server - How to create a new table using a select from another table (Both in the same Database)

Question

I made an sql script by joining some tables in SQL Server and will have the result in some fields, but I would like to create a new table and copy everything that Filtrei with select into it... Something like: create table Address_new as (my sql script....).

Someone would know how to help me?

Thank you :)

Select 
loc.loc_nu_sequencial, 

CASE
	WHEN
		loc.cep IS NULL
	THEN logr.cep 
	WHEN
		logr.cep IS NULL
	THEN loc.cep 
	ELSE loc.cep
END AS cep,

 logr.log_nome,
 logr.log_complemento,
 log_bairro.bai_no,
 loc.loc_no,
 loc.ufe_sg 
  
  from log_localidade as loc

  left join log_logradouro as logr on loc.loc_nu_sequencial = logr.loc_nu_sequencial
  left join log_bairro on logr.bai_nu_sequencial_ini = log_bairro.bai_nu_sequencial

  order by loc_nu_sequencial

Answer 1

Use the clause INTO in your code. The name entered in it will be used in the creation of the new table.

SELECT 
    loc.loc_nu_sequencial, 
    CASE
        WHEN loc.cep IS NULL THEN logr.cep 
        WHEN logr.cep IS NULL THEN loc.cep 
        ELSE loc.cep
    END AS cep,
    logr.log_nome,
    logr.log_complemento,
    log_bairro.bai_no,
    loc.loc_no,
    loc.ufe_sg 
INTO nova_tabela
FROM log_localidade AS loc
    LEFT JOIN log_logradouro as logr 
        ON loc.loc_nu_sequencial = logr.loc_nu_sequencial
    LEFT JOIN log_bairro 
        ON logr.bai_nu_sequencial_ini = log_bairro.bai_nu_sequencial
ORDER BY loc_nu_sequencial

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql