November 4, 2016 at 5:29 pm
I used code below to create a new table.
select * into newtable from oldtable
How to code to make new table from the result of store procedure?
Like code below.
select * into newtable from (exec my_store_procedure '12345') //'12345' is one order ID as parameter
Please help to complete code.
November 5, 2016 at 4:08 am
There are three ways of doing this, using the describe_first_result_set system functions or procedures, use FMTONLY (deprecated) or OPENROWSET, the last one requiring the "Ad Hoc Distributed Queries" configuration to be enabled. I normally use the first option wrapped in a function.
😎
The function dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
(
@TABLE_NAME NVARCHAR(128)
,@SCHEMA_NAME NVARCHAR(128)
,@EXEC_SQL NVARCHAR(MAX)
,@PARAMETER_STR NVARCHAR(MAX)
,@IS_TABLE_VARIABLE BIT
,@IS_TEMPORARY_TABLE BIT
)
RETURNS TABLE
AS
/******************************************************************************
AUTHOR: Eirikur Eiriksson
-------------------------------------------------------------------------------
This code is provided as is without any warranty. It is free to use for any
purposes given that this comment section is included.
-------------------------------------------------------------------------------
COPYRIGHT © 2012 - 2016 Eirikur Eiriksson
-------------------------------------------------------------------------------
OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
-------------------------------------------------------------------------------
OUTPUT: TABLE_DDL NVARCHAR(MAX)
-------------------------------------------------------------------------------
PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
-------------------------------------------------------------------------------
DESCRIPTION:
-------------------------------------------------------------------------------
The function returns a DDL for any executable expression that returns a result
set with named columns. Columns with no name (No column name) will be ignored
and will not be present in the generated DDL.
The function can generate all three types of table DDL by setting the
@IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:
DDL Type @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
----------------------------------------------------------------
create table 0 0
create temporary table 0 1
declare table variable 1 0
NULL 1 1
----------------------------------------------------------------
-------------------------------------------------------------------------------
NOTE:
-------------------------------------------------------------------------------
1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
then the function will return an empty result set (NULL).
2) The DDL will always be HEAP.
3) The column nullability is the only constraint in the DDL
-------------------------------------------------------------------------------
USAGE:
-------------------------------------------------------------------------------
The function is an alternative and enhancement to SET FMTONLY which has been
deprecated and OPENROWSET which requires enabeling the
"Ad Hoc Distributed Queries" configuration.
-------------------------------------------------------------------------------
EXAMPLES:
-------------------------------------------------------------------------------
1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
query:
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
-------------------------------------------------------------------------------
2) Capture the output of a stored procedure into a temporary table
(#TBL_SP_WHO) and select from the table;
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 1;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
-------------------------------------------------------------------------------
3) Capture the result set of a stored procedure into a table variable (@SPWHO)
and conditionally select from the table variable.
DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 1;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO @' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM @' + @TABLE_NAME + N'
WHERE status = ''runnable'' ' + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
******************************************************************************/
RETURN
SELECT
CASE
WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
+ NCHAR(46) + QUOTENAME(@TABLE_NAME)
+ NCHAR(32) + NCHAR(40)
WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
+ NCHAR(32) + NCHAR(40)
WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
+ N' TABLE ' + NCHAR(32) + NCHAR(40)
ELSE NULL
END
+ NCHAR(13) + NCHAR(10) + NCHAR(32) +
(
SELECT
STUFF(
(
SELECT
NCHAR(44) + QUOTENAME(R.name) + N' '
+ (R.system_type_name) + N' '
+ CASE
WHEN R.is_nullable = 0 THEN N'NOT NULL'
ELSE N' NULL'
END
+ NCHAR(13) + NCHAR(10)
FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
WHERE R.is_hidden = 0
ORDER BY R.column_ordinal ASC
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)')
,1,1,N'')
) + NCHAR(41) + NCHAR(59) AS TABLE_DDL
;
Usage examples
1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
query:
DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X;
2) Capture the output of a stored procedure into a temporary table
(#TBL_SP_WHO) and select from the table;
DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 1;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
3) Capture the result set of a stored procedure into a table variable (@SPWHO)
and conditionally select from the table variable.
DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 1;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO @' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM @' + @TABLE_NAME + N'
WHERE status = ''runnable'' ' + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
November 5, 2016 at 11:23 am
Eirikur Eiriksson (11/5/2016)
There are three ways of doing this, using the describe_first_result_set system functions or procedures, use FMTONLY (deprecated) or OPENROWSET, the last one requiring the "Ad Hoc Distributed Queries" configuration to be enabled. I normally use the first option wrapped in a function.😎
The function dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
USE TEEST;GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
(
@TABLE_NAME NVARCHAR(128)
,@SCHEMA_NAME NVARCHAR(128)
,@EXEC_SQL NVARCHAR(MAX)
,@PARAMETER_STR NVARCHAR(MAX)
,@IS_TABLE_VARIABLE BIT
,@IS_TEMPORARY_TABLE BIT
)
RETURNS TABLE
AS
/******************************************************************************
AUTHOR: Eirikur Eiriksson
-------------------------------------------------------------------------------
This code is provided as is without any warranty. It is free to use for any
purposes given that this comment section is included.
-------------------------------------------------------------------------------
COPYRIGHT © 2012 - 2016 Eirikur Eiriksson
-------------------------------------------------------------------------------
OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
-------------------------------------------------------------------------------
OUTPUT: TABLE_DDL NVARCHAR(MAX)
-------------------------------------------------------------------------------
PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
-------------------------------------------------------------------------------
DESCRIPTION:
-------------------------------------------------------------------------------
The function returns a DDL for any executable expression that returns a result
set with named columns. Columns with no name (No column name) will be ignored
and will not be present in the generated DDL.
The function can generate all three types of table DDL by setting the
@IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:
DDL Type @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
----------------------------------------------------------------
create table 0 0
create temporary table 0 1
declare table variable 1 0
NULL 1 1
----------------------------------------------------------------
-------------------------------------------------------------------------------
NOTE:
-------------------------------------------------------------------------------
1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
then the function will return an empty result set (NULL).
2) The DDL will always be HEAP.
3) The column nullability is the only constraint in the DDL
-------------------------------------------------------------------------------
USAGE:
-------------------------------------------------------------------------------
The function is an alternative and enhancement to SET FMTONLY which has been
deprecated and OPENROWSET which requires enabeling the
"Ad Hoc Distributed Queries" configuration.
-------------------------------------------------------------------------------
EXAMPLES:
-------------------------------------------------------------------------------
1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
query:
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
-------------------------------------------------------------------------------
2) Capture the output of a stored procedure into a temporary table
(#TBL_SP_WHO) and select from the table;
-------------------------------------------------------------------------------
DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 1;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
-------------------------------------------------------------------------------
3) Capture the result set of a stored procedure into a table variable (@SPWHO)
and conditionally select from the table variable.
DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO';
DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 1;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO @' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM @' + @TABLE_NAME + N'
WHERE status = ''runnable'' ' + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
******************************************************************************/
RETURN
SELECT
CASE
WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
+ NCHAR(46) + QUOTENAME(@TABLE_NAME)
+ NCHAR(32) + NCHAR(40)
WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
+ NCHAR(32) + NCHAR(40)
WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
+ N' TABLE ' + NCHAR(32) + NCHAR(40)
ELSE NULL
END
+ NCHAR(13) + NCHAR(10) + NCHAR(32) +
(
SELECT
STUFF(
(
SELECT
NCHAR(44) + QUOTENAME(R.name) + N' '
+ (R.system_type_name) + N' '
+ CASE
WHEN R.is_nullable = 0 THEN N'NOT NULL'
ELSE N' NULL'
END
+ NCHAR(13) + NCHAR(10)
FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
WHERE R.is_hidden = 0
ORDER BY R.column_ordinal ASC
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)')
,1,1,N'')
) + NCHAR(41) + NCHAR(59) AS TABLE_DDL
;
Usage examples
1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
query:
DECLARE @TABLE_NAME NVARCHAR(128) = N'MY_NEW_TABLE';DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X;
2) Capture the output of a stored procedure into a temporary table
(#TBL_SP_WHO) and select from the table;
DECLARE @TABLE_NAME NVARCHAR(128) = N'TBL_SP_WHO';DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 0;
DECLARE @IS_TEMPORARY_TABLE BIT = 1;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO #' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
3) Capture the result set of a stored procedure into a table variable (@SPWHO)
and conditionally select from the table variable.
DECLARE @TABLE_NAME NVARCHAR(128) = N'SPWHO';DECLARE @SCHEMA_NAME NVARCHAR(128) = N'dbo';
DECLARE @EXEC_SQL NVARCHAR(MAX) = N'EXEC sp_who';
DECLARE @PARAMETER_STR NVARCHAR(MAX) = NULL;
DECLARE @IS_TABLE_VARIABLE BIT = 1;
DECLARE @IS_TEMPORARY_TABLE BIT = 0;
DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
(
SELECT
X.TABLE_DDL
FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
,@SCHEMA_NAME
,@EXEC_SQL
,@PARAMETER_STR
,@IS_TABLE_VARIABLE
,@IS_TEMPORARY_TABLE) X
) + N'
INSERT INTO @' + @TABLE_NAME + N'
' + @EXEC_SQL + N'
SELECT * FROM @' + @TABLE_NAME + N'
WHERE status = ''runnable'' ' + NCHAR(59);
EXEC (@EXEC_SP_WHO_TO_TABLE);
Now there's something I've never even considered. Thanks, Eirikur - you've given me a new toy. 😉
November 5, 2016 at 3:25 pm
Ed Wagner (11/5/2016)
Now there's something I've never even considered. Thanks, Eirikur - you've given me a new toy. 😉
Enjoy mate!
😎
And more to come...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply