0

I would like to select into table variable from @query.

Here is @query:

SELECT @QUERY = 'SELECT DISTINCT USER_KEY, (SELECT TOP(1) USER_FNM  FROM BAUSER WHERE BAUSER.USER_KEY = PIVOT_LOCATIONS.USER_KEY), ' + @COLDEPSUMMARY + '
FROM CAUSDE_TAS 
PIVOT
(
SUM(USDE_HSU)
FOR DEPA_KEY IN (' + @COLDEPARTMENTS + ')
) PIVOT_LOCATIONS
WHERE USDE_DAT >= ''' + format(@DATEFROM, 'MM.dd.yyyy') + ''' AND USDE_DAT <= ''' + format(@DATETO, 'MM.dd.yyyy') + ''' 
AND USER_KEY IN (' + @USERS_STR + ')
GROUP BY USER_KEY'


EXECUTE (@QUERY)

What I would like to do is get results from EXECUTE(@QUERY) and insert those results into temporary @USERS table:

DECLARE @USERS TABLE
(
  USER_KEY INT,
  USER_FNM VARCHAR(50),
  USDE_HSU DECIMAL(8,2),
  [110000003] DECIMAL(8,2),
[120000002] DECIMAL(8,2),
[120000003] DECIMAL(8,2),
[120000004] DECIMAL(8,2),
[120000005] DECIMAL(8,2),
[120000006] DECIMAL(8,2),
[120000007] DECIMAL(8,2),
[120000008] DECIMAL(8,2)
)

Is this possible?

FrenkyB
  • 6,625
  • 14
  • 67
  • 114
  • Why are you using dynamic SQL? In this instance, it doesn't appear to be necessary. Without dynamic SQL, you can use the standard `SELECT...INTO` statement. – Taylor Buchanan Feb 15 '17 at 15:31
  • 1
    @TaylorBuchanan - sorry, it is not visible from example, but there are around 80 columns. I am generating them dynamically. (this is part of result from dynamic generation. I haven't pasted all columns, it would be unnecessary complication). – FrenkyB Feb 16 '17 at 08:55

3 Answers3

1

Should be as simple as

INSERT INTO @USERS
EXECUTE(@QUERY)

Given that you have made all proper declarations in advance.

Example:

declare @something table (
 someint int
)

declare @query varchar(max);

select @query = 'select 1
union all
select 2';

insert into @something (someint)
execute(@query)

select * from @something

Yields

 someint
       1
       2
Tobb
  • 11,850
  • 6
  • 52
  • 77
  • What if table has some columns that query is not returning? Should table and query always has the same columns? I mean - is there a way of returning e.g. NULL into column which is not part of query, but it is part of table? – FrenkyB Feb 15 '17 at 10:16
  • As you see in my example, I specify which columns to insert into. It works just as a normal insert into with values procured by query.. – Tobb Feb 15 '17 at 10:31
0

If you switch your table variable to a temporary table, you can insert into it from within the dynamic SQL in the same batch. This avoids the possible future error listed in the next section.

CREATE TABLE #USERS (...);

DECLARE @SQL NVARCHAR(MAX) = N'
    INSERT #USERS (USER_KEY, ...)
    SELECT USER_KEY, ' + @COLDEPSUMMARY + N', ...';

EXEC sp_executesql @SQL, N'@DATEFROM DATE, @DATETO DATE, ...', @DATEFROM, @DATETO, ...;

You'll also notice I used sp_executesql instead. It's generally a bad practice to use EXEC instead of sp_executesql. You won't be able to use parameters for the dynamic column portion, but you can at least use it to pass in the other parameters such as @DATEFROM and @DATETO. You can also pass your other parameter @USERS_STR if you convert to a table type beforehand.


As previously mentioned, I suggest against using INSERT....EXEC where possible as it can eventually result in the following error if you have nested stored procedures both using it:

Msg 8164, Level 16, State 1

An INSERT EXEC statement cannot be nested.

Example:

CREATE PROC #Test1
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Test1 ( A INT );

    INSERT #Test1
    EXEC('SELECT 1')
END;
GO
CREATE PROC #Test2
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Test2 ( B INT );

    INSERT #Test2
    EXEC #Test1
END;
GO
EXEC #Test2;
Community
  • 1
  • 1
Taylor Buchanan
  • 4,155
  • 1
  • 28
  • 40
0

You have to use table-functions as example:

create FUNCTION [dbo].[Get_Some_Table] (
    @Id int
)

RETURNS @tbResults TABLE (
    uId uniqueidentifier,
    sType VARCHAR(5),
    sCode VARCHAR(50)
)

AS

BEGIN

    -- select, insert into, create various @tables ecc...

    RETURN

END

GO

Then use:

INSERT INTO @tb (uId, sType, sCode) SELECT uId,sType,sCode FROM dbo.Get_Same_Table(@id)

This prevents NESTED INSERT EXEC and uses the same lines of code. Hope this helps, see you!

Orlando Herrera
  • 3,481
  • 1
  • 34
  • 44