Sql-server – Insert output into another newly created table

sql serversql server 2014t-sql

How can I insert the output of below table into newly created [DBname].[dbo].[Tablename]?

The below T-SQL query outputs two columns: server and name.

T-SQL 1

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1 
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I want to insert the output of T-SQL 1 into a newly created table [DBname].[dbo].[Tablename].I already created two columns server and name.

So far I tried like below:

Insert into [DBname].[dbo].[Tablename]

select 

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1 
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I used the Stack Overflow Q & A referenced below, but somehow could not do what I need:

How to do INSERT into a table records extracted from another table

Best Answer

Make use of the OUTPUT clause.

Let's say you need to insert #TEMP1 data into #TAB2 when an INSERT is done on #TEMP1:

CREATE TABLE #TEMP1 
([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200),
);

CREATE TABLE #TAB2 ([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200));

INSERT INTO  #TEMP1
OUTPUT INSERTED.[SERVER],INSERTED.NAME INTO #TAB2
SELECT  (SELECT @@SERVERNAME) AS SERVER,NAME FROM MASTER.SYS.DATABASES;

Now see the results in both tables:

SELECT * FROM #TEMP1;
SELECT * FROM #TAB2;

See the product documentation: OUTPUT Clause (Transact-SQL) for more information, including some important restrictions.

If you want to automate this process, you need to write an AFTER INSERT trigger. Please find a very good example exactly matching your requirement in SQL Server: Coding the After Insert Trigger in SQL Server by Vishwanath Dalvi.

Related Question