running EXEC results into a table

if you dont need to create table with select * into... (which you cant with an exec proc) you need to create a table first... temp or actual is your choice...

suppose EXEC wk_GetPolicyData @SSN, @POLNUM, @EFFDATE returns three columns Col1 int, Col2 Varchar(100) and Col3 DateTime you can do

create table #T(Col1 int, Col2 Varchar(100) ,Col3 DateTime )
insert #T
EXEC wk_GetPolicyData @SSN, @POLNUM, @EFFDATE

if the destination table had different fields, how do i pipe the VALUES from the executed sp into the table? i tried the OUTPUT option but doesn't work

you should create the destination table as the same data structure returned from first one, the only other way I can think of is create destination table with Varchar(8000) for all columns ( I know its a overkill but just to be sure) and do a insert same way and then read data back from destination table while converting to appropriate datatype... but even then you will have to know how many columns are returning from your wk_GetPolicyData proc.

one more thing and angelIII will tell at me for that and you already said you dont want to use select into, is you can write a select into statement in wk_GetPolicyData proc before your final select, ....select into a physical table and then that table is available to you... just make sure you write drop physical table command in your wk_GetPolicyData proc before you do select into...

now if i wanted to UPDATE an existing table, how can i pipe the results for the update of, say Carrier_Risk_Number (from tblTEMP_tblIDIIncreaseUpload) like the following:

WHILE @@FETCH_STATUS = 0
BEGIN
      UPDATE tblTEMP_tblIDIIncreaseUpload SET PreUpdateStatus=Carrier_Risk_Number
          EXEC wk_GetPolicyData @soc_sec_no, @policy_no, @effective_date
    FETCH NEXT FROM curIterate INTO @soc_sec_no, @policy_no, @effective_date
END

is Carrier_Risk_Number field coming from wk_GetPolicyData?
then assuming you are returning two columns from  wk_GetPolicyData  Id and Carrier_Risk_Number
you will do something like


WHILE @@FETCH_STATUS = 0
BEGIN

insert into MyTemptable(ID int, Carrier_Risk_Number int)
EXEC wk_GetPolicyData @soc_sec_no, @policy_no, @effective_date

SET PreUpdateStatus=MyTemptable.Carrier_Risk_Number
From tblTEMP_tblIDIIncreaseUpload
Inner Join
          MyTemptable
ON     MyTemptable.ID=tblTEMP_tblIDIIncreaseUpload.ID

FETCH NEXT FROM curIterate INTO @soc_sec_no, @policy_no, @effective_date
END




-- and then

UPDATE tblTEMP_tblIDIIncreaseUpload

yes, Carrier_Risk_Number field comes from wk_GetPolicyData.

so there's no way of piping of the wk_GetPolicyData directly as an update to another table?

sorry for being stubborn. i vaguely remember an example some time ago. thanx!

with the exec wk_GetPolicyData you definetly need to capture the result set in a table (maybe temp or permenant) before you can use that data set for DML. now if Carrier_Risk_Number  is the ONLY field returned from the proc and you are Using an OUTPUT directive in the definition of wk_GetPolicyData proc then use you can capture this one value in a variable and use that to update tblTEMP_tblIDIIncreaseUpload .

Since you are leaning away from what I and other experts are saying, may I ask the reason why you think this is not a better idea, maybe we can come up with some other solution, if we know exactly what you want to do and how are application will behave if you go one way over other.

rw3admin

the reason is that i have to pipe/update between 27 and 32 fields, and wk returns over 50 fields (pretty complex calculations and not work replicating) and the process may change over time. just wanted to tidy up

also i forgot, rw3admin... just to be sure,
i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.

>>the process may change over time. just wanted to tidy up <<
just this line throws all design out the door, whatever you do right now is going to be a temp solution so my suggestion go for this design and when you are re-designing put your update statement in wk_GetPolicyData proc.

I dont understand this line
>>i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.<<

casue if you know data set returning from wk_GetPolicyData you will design your destination table accordingly to capture those fields.

Imran, AngelIII do you have anything to add to it?

rw3admin

thanx, but i can't modify anything in wk_GetPolicyData. i'm forbidden to tweak it.

what i meant by...
>>i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.<<
is that i want to insert data that is not in the same order as the columns and if, for example, i want to insert 2 columns into my temp table where the wk_GetPolicyData returns over 50.

thanx again!

thanx for your recommendation, rw3admin, and everyone else too. points to u guys...

why can't i split points here???

I dont know ... I have only one time asked a question on EE where I had to award points, and even that I messed up, angelIII and Imran are pretty good with it so maybe they can tell you how to split points.

rw3admin

so you think that the 'split points' option has been discontinued? i don't get it.

oh no not that....
check out following
https://www.experts-exchange.com/help.jsp#hi19