0

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

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.