question

wolfeste avatar image
wolfeste asked

Dynamically Create temp table

I am looking to find a way to create a temp table from a stored procedure dynamically. I have a very complex stored procedure from MS Great Plains that has many columns in it. It would take hours to research every column to find the data types so just looking for a less extensive way to insert all records into this temp table. here is the SP I am using from MS Great Plains exec [seeglPrintSRSTrialBalance ] @I_tInactiveAccounts=0,@I_tIncludeUnitAccounts=0,@I_tIncludeZeroBalance=0,@I_cStartingAccountNumber=N'5100-11-101 ',@I_cEndingAccountNumber=N'6100-11-101 ',@I_dStartingDate='2018-01-01 00:00:00',@I_dEndingDate='2018-01-31 00:00:00',@I_iYear=2018,@I_tHistoryYear=0,@I_iRangeByAccount=N'1',@I_iPostingAccounts=N'1'
sql server 2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

b.joseph avatar image
b.joseph answered
You can use SELECT fld1,fld2 INTO newTempTable FROM YourTable
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tried a variation of that but the sytnax is not correct with a stored procedure
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Use [sp_describe_first_result_set][1] to get the definition of the output. Use that to create the definition of a table which you can then execute the stored procedure into using INSERT INTO #yourtable EXECUTE YourStoredProc Note however that this only works for the first result set output from a stored procedure. [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@wolfeste Of course it goes without saying that in order not to run into problem with getting the true first result set info, one mustn't forget to set that [nocount][1] to ON. This is hardly worth mentioning because from what I understand, ***everyone, always, without any exceptions whatsoever***, includes the line reading set nocount on; in the body of any stored procedure, right? That is unless there is a specific reason to do otherwise. [1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql
1 Like 1 ·
How could i use my stored procedure with sp_describe_first_result_set? When I input my stored procedure into this I get a syntax error. I believe it has to do with the single quotes.
0 Likes 0 ·
What exactly is the error?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.