October 26, 2009 at 8:12 am
Guys:
This looks pretty simple. but am unable to find the code.
I have access to one proc (say p1). when i execute p1 it returns 3 record sets.(am not sure what tables it returns).
My requirement is, I should be able to create the same number of tables along with structure and data in my database by calling the proc p1. ie P1 returns 3 record sets, so my program should create those 3 tables along with data.
am printing the existing code below. now please tell me the script which should call P1 and create same number of tables as many as returned by proc p1 along with data. (SQL server 2005). (I cannot change p1 code)
------------------------
Create Table Test1 ( C11 int, c12 varchar(10))
Create Table Test2 ( C21 int, c22 varchar(10))
Create Table Test3 ( C31 int, c32 varchar(10))
Create procedure p1
As
Begin
Select * from Test1
Select * from Test2
Select * from Test3
End
--------------------------
--Jus
October 26, 2009 at 9:03 am
look up
select...into
October 26, 2009 at 9:08 am
Tried that..It doesnot work.. Can anyone help me here...
October 26, 2009 at 9:10 am
What have you tried?
If the tables exist
insert table select field1, field2, from othertable.
October 26, 2009 at 9:21 am
am not sure the table names and clolumn names in each table. This new program should handle all..If Proc p1 returns 3 record sets, new program should be able to create those tables along with data dynamically....
October 26, 2009 at 10:06 am
you can't easily do this. If the procedure returns result sets, you need code specifically in there to create the table. I cannot execute a stored procedure, get multiple result sets and have tables created for those with a command.
If you have ADO code, you can get the results and then read the DataTable and create table, but it is not easy to do. You'd have to figure out the data types, etc.
October 26, 2009 at 11:00 am
Steve..first of all..can this be done using procedure?
No problem even if its tough to do this...but i want to know.is this really doable in a proc?
October 26, 2009 at 12:24 pm
Do you mean that if I have a proc
create procedure myproc
as
exec p1
....
return
and I somehow create table from the execution of P1? If so, no, I don't think this can be done. You don't have access to those result sets to consume them. You would need a client of some sort to consume them, or alter the code of P1 to put the data into a table.
October 26, 2009 at 12:47 pm
Thanks Steve..I guess we need some appliocation (.net) to accomplish the required functionality.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply