While playing with SQL Server 2008 I found new feature of “Row Constructors”, where I can load temp table from stored procedure directly.
Look at the following SQL where I have to use OpenQuery from server to itself creating loopback server and execute stored procedure and insert into temp table.
INSERT INTO #TempTable
SELECT *
FROM OPENQUERY(ServerName, 'exec StoredProc')
Above mentioned same query can be now written with simpler statement as described here.
INSERT INTO #TempTable
EXEC StoredProc
Note that this does not work with real tables or any other objects. This feature is only available to load temp tables.
Reference : Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
What’s the differetnt from SQL Server 2005. This script works well in SQL Server 2005 and in SQL Server 2000.
Also with ‘real’ tables.
use master
drop database testdb
go
create database testdb
go
use testdb
create table t (i int)
go
insert into t values (1)
insert into t values (2)
insert into t values (3)
insert into t values (4)
go
create proc usp_t
as
select *
from t
go
select top 0 *
into #t
from t
go
insert into #t
exec usp_t
go
select * from #t
go
drop table #t
hi sir,
can u pls help me??
WITH TopRow
AS
(
SELECT TOP 100 Applied_to_leavemanager,leavetype,leavefrom,leaveto,No_of_hours,status
FROM ahd_leave
ORDER BY leaveID
)
SELECT *
FROM TopRow
by using this queryy i can retrive the entire data but how can i retrieve this data row wise to my table(in my page)???
In earlier version also, it is possible with Openrowset
hi i need one help. i have created one procedure, in which i am retriving data from link server by using open query , the problem is i want to retrive the result after executive above procedure in a variable. can u help me pls irs very urgent
create PROCEDURE usp_GetList @CorrectScore varchar(7500) output
AS
declare @QuesBankId nvarchar(3900)
set @QuesBankId=’SFHISTORYQuestionbank01′
set @CorrectScore = ‘Select SectionCorrectScore from UPSSF_History..tblTestSectGeneral_History where QuestionBankId=”’+ @QuesBankId +””
set @CorrectScore = N’select * from openquery
(ASHOKGPC,”’ + REPLACE(@CorrectScore, ””, ”””) + ”’)’
GO
Hi Pinal,
I refered your code sample and tried to execute it, but it was not useful . I need to insert into temporary table from Stored Procedure result set. The problem is the scehma for the result set returned by the stored procedure is not known paramter. Hence please help me to get some code sample in order to resolve this issue.
Thanks In Advance,
Santosh
wat if have row constructor within Store Procedure like this :
insert into tblAcademicRecord(educationlevel, examname,institution,passingyear,obtainedmarks,totalmarks,grade, percentage, remarks, userid)
values(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid),(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid)
now how to pass values to it via EXEC ?
Record Insertion in 3 Different Ways –
(Using SQL SERVER 2008)
—- Declare table variable ———-
DECLARE @tblCountries table
(id int, country varchar(50))
————————————————
–Inserting multiple values
Insert into @tblCountries (id,country)
Values
(1,’USA’), –Row 1
(2,’UK’), –Row 2
(3,’France’) –Row 3
–Now select, you will get 3 rows
Select * from @tblCountries
————————————————
Insert into @tblCountries (id,country)
SELECT 1,’China’
UNION ALL
SELECT 2,’Japan’
UNION ALL
SELECT 3,’Thailand’
Select * from @tblCountries
———————————————–
Insert into @tblCountries (id,country)
EXEC [USP_Test]
/*
CREATE/ALTER PROCEDURE [USP_Test]
AS
— EXEC [USP_Test]
BEGIN
SET NOCOUNT ON;
SELECT 1,’India’
UNION ALL
SELECT 2,’NY’
UNION ALL
SELECT 3,’London’
SET NOCOUNT OFF;
END
GO
*/
Select * from @tblCountries
———————————————–
Njoy!!!! :)