June 10, 2013 at 6:13 am
Dear reader,
Below is an example to get data from a stored procedure into a table.
Is there a more generic method or better method to do this. (Generating a resulttable for example which works for 'any' sp). sp_spaceused is used as an example.
Thanks in advance,
ben
--
-- 20130610
-- ben brugman
--
-- EXAMPLE:
-- two databases
-- three tables
-- get the space used (6 rows) into a table.
--
-- Question:
-- How do I get the info of a stored procedure in a table.
--
--
-- Create a result table.
--
select
CONVERT(varchar(30), '') as name,
CONVERT(varchar(30), '') as rows,
CONVERT(varchar(30), '') as reserved,
CONVERT(varchar(30), '') as data,
CONVERT(varchar(30), '') as index_size,
CONVERT(varchar(30), '') as unused
into ##A where 1 = 1
-- second result table with an extra column
select CONVERT(varchar(30), 'Database1') as DB_name,* Into ##B from ##A
--
-- Fill the first result table.
--
insert into ##A EXEC sp_spaceused 'table_name1'
insert into ##A EXEC sp_spaceused 'table_name2'
insert into ##A EXEC sp_spaceused 'table_name3'
-- Fill the second result table with extra info.
insert into ##B
select
CONVERT(varchar(30), 'Database1') as DB_name, *
from ##A
-- Repeat process for a second database.
Use database2
delete ##A
insert into ##A EXEC sp_spaceused 'table_name1'
insert into ##A EXEC sp_spaceused 'table_name2'
insert into ##A EXEC sp_spaceused 'table_name3'
insert into ##b
select
CONVERT(varchar(30), 'Database2') as DB_name,
*
from ##A
--
-- Show all results.
--
select * from ##B
-- Clear up
drop table ##A
drop table ##B
June 10, 2013 at 7:31 am
I've always done this by first creating a table to receive the data from the stored procedure. Then populate it like this:
INSERT INTO table_name EXECUTE sp_name;
Since the results generated by a stored procedure vary by procedure in both number and type, I don't see how you could create a table to work with *any* procedure.
June 10, 2013 at 9:44 am
Have you considered using SELECT INTO?
This would work with any result set, but would create a new table every time. You could either put some logic to create a table name with a date stamp or new integer value. Or before you do the select into, check for the new table name, and drop it if it exists.
June 10, 2013 at 9:46 am
I've never tried to SELECT INTO with the results of a stored procedure. How would you go about doing that?
June 10, 2013 at 9:59 am
Ed Wagner (6/10/2013)
Since the results generated by a stored procedure vary by procedure in both number and type, I don't see how you could create a table to work with *any* procedure.
SELECT * INTO xname FROM anytable
This does work with any table, here the results vary by both number and type. So I do not think that is 'the' or 'an' objection.
The output from a stored procedure is often a resultset which is or is similar to a table.
And the resultset can very even within a single stored procedure, but the INTO construction does only work a single time so I do not see that as an objection.
It seems it is not possible yet, maybe for the next version of SQL-server.
Thanks for your time and attention,
ben
June 10, 2013 at 10:00 am
g_demetriou (6/10/2013)
Have you considered using SELECT INTO?This would work with any result set, but would create a new table every time. You could either put some logic to create a table name with a date stamp or new integer value. Or before you do the select into, check for the new table name, and drop it if it exists.
This won't work. The OP is calling a stored procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 10:04 am
IIRC you can use OPENROWSET after the FROM clause
June 10, 2013 at 10:04 am
Right - the SELECT INTO is for tables, but not procedures. I would definitely use SELECT INTO...EXECUTE if it existed. I'm glad I didn't just miss it.
June 10, 2013 at 10:18 am
g_demetriou (6/10/2013)
IIRC you can use OPENROWSET after the FROM clause
It is a bit more complicated than that. First of all you have to have ad hoc queries turned on, which is not set by default. This is not an option in a production environment for many people.
Secondly, this now has to create a second connection to the same server. Yes it can be done but you have to jump through a lot of hoops for this.
SELECT *
into #SomeTable
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','exec master.dbo.sp_who') x
select * from #SomeTable
drop table #SomeTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 11:19 am
Sean Lange (6/10/2013)
Secondly, this now has to create a second connection to the same server. Yes it can be done but you have to jump through a lot of hoops for this.
I'll have a go with this tomorrow, if this works in general, this will be at least of some help and maybe even of great help.
(If parameters can be passed this may be the solution for a larger problem we are struggling with).
Thanks,
Ben
June 10, 2013 at 1:17 pm
Sean Lange (6/10/2013)
g_demetriou (6/10/2013)
IIRC you can use OPENROWSET after the FROM clauseIt is a bit more complicated than that. First of all you have to have ad hoc queries turned on, which is not set by default. This is not an option in a production environment for many people.
Secondly, this now has to create a second connection to the same server. Yes it can be done but you have to jump through a lot of hoops for this.
SELECT *
into #SomeTable
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','exec master.dbo.sp_who') x
select * from #SomeTable
drop table #SomeTable
And you have to name the server instead of using "(local)" if it's an instance.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2013 at 2:33 pm
ben.brugman (6/10/2013)
Sean Lange (6/10/2013)
Secondly, this now has to create a second connection to the same server. Yes it can be done but you have to jump through a lot of hoops for this.
I'll have a go with this tomorrow, if this works in general, this will be at least of some help and maybe even of great help.
(If parameters can be passed this may be the solution for a larger problem we are struggling with).
Thanks,
Ben
Parameters can, indeed, be passed to the OPENROWSET method but only with "double dynamic SQL" because none of the parameters will accept a variable. Why the hell they made it that way is anyone's guess.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2013 at 5:44 am
You know, SELECT INTO...EXECUTE or EXECUTE...INTO seems a lot more intuitive. Maybe in a future version?
June 11, 2013 at 6:04 am
Sean Lange (6/10/2013)
Secondly, this now has to create a second connection to the same server. Yes it can be done but you have to jump through a lot of hoops for this.
I have been jumping through some hoops.
Got the code to work with the sp_who query.
But after a lot of jumping through (closed) hoops, I still haven't been able to use the sp_spaceused stored procedure.
Below: The Code and the Error messages.
Google: sqlserver openrowset
http://www.mssqltips.com/sqlservertip/1551/using-sql-servers-openrowset-to-break-the-rules/
http://www.sommarskog.se/share_data.html
Might give solutions or the anwser why it is not working, I am still working my way through that article.
Any help would be welcome.
Thanks for your help,
ben brugman
SELECT *
into TEMPSomeTable
FROM OPENROWSET ('SQLOLEDB','Server=AserverInstance\R2;TRUSTED_CONNECTION=YES; Initial Catalog=database1',
'exec master.dbo.sp_who') x -- Working code
-- 'SELECT top 10 * FROM database1.dbo.table_name1 where field_name1 like ''%v%''') x -- Working code
-- 'SELECT top 10 * FROM table_name1 where field_name1 like ''%v%''') x -- Non Working code
-- 'EXEC sp_spaceused ''table_name1'' ') x -- Non working code
-- 'EXEC sp_spaceused ''database1.dbo.table_name1'' ') x -- Non working code
-- 'EXEC sp_spaceused ''[database1.dbo.table_name1]'' ') x -- Non working code
-- 'EXEC sp_spaceused ''[database1].[dbo].[table_name1]'' ') x -- Non working code
select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TEMPSOMETABLE'
SELECT * FROM tempsometable
DROP TABLE tempsometable
/*
Error Error Error Error Error Error Error Error Error Error Error Error Error
'EXEC sp_spaceused ''table_name1'' ') x -- Non working code
-- Produces the following message:
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'table_name1' does not exist in database 'master' or is invalid for this operation.
Error Error Error Error Error Error Error Error Error Error Error Error Error
'EXEC sp_spaceused ''database1.dbo.table_name1'' ') x -- Non working code
-- Produces the following message:
Msg 15250, Level 16, State 1, Procedure sp_spaceused, Line 41
The database name component of the object qualifier must be the name of the current database.
*/
June 11, 2013 at 6:06 am
I currently use the OUTPUT method to populate the results from an INSERT/UPDATE
Comparing two tables and updating the data and populating the results to an audit table.
INSERT INTO tablenamehere (field1, field2, field3)
OUTPUT inserted.field1, inserted.field2, inserted.field3
FROM sourcetable
JOIN targettable
on sourcetable.key = targettable.key
WHERE source.field1 <> target.field1 OR source.field2 <> target.field2 OR source.field3 <> target.field3
Obviously creating the audit table upfront.
Hope this helps ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply