October 21, 2013 at 2:23 pm
Hi,
I just find good sp I can reuse for my purposes (actually I need to insert its output to the table ( table aleady exists so record will be added to it).
Thinking how I can shorten my code.
I tried to use this syntax and didn't work, plus my sp has multi params, so I need to deal with single quotes separately.
;with t as (
EXEC sp_022 'alpha', 'bravo', '1/1/2011'
)
insert into tMain select c1, c2, c3 from t
What would be the best approach to do this, I can rely on this sp, it never changed, so I i'm not afraid to use inside my code.
THanks
M
October 21, 2013 at 3:01 pm
You can do the following:
[font="Courier New"]insert into tMain
(c1, c2, c3)
EXEC sp_022 'alpha', 'bravo', '1/1/2011'[/font]
October 21, 2013 at 4:10 pm
Tx a lot, it works,
is it possible to operate only on few columns from sp output ?
I don't need all and don't want to do extra temp table.
Best
M
October 22, 2013 at 7:40 am
mario17 (10/21/2013)
Tx a lot, it works,is it possible to operate only on few columns from sp output ?
I don't need all and don't want to do extra temp table.
Best
M
Nope. The stored proc returns values that are defined within the proc. When you execute it, you get all the columns. In essence you are trying to insert more columns than the destination table has. I believe you will get a "Column name or number of supplied values does not match table definition" error.
_______________________________________________________________
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/
October 22, 2013 at 9:02 am
Create a #temp table with all the columns of the proc's output.
Insert the output into the temp table, then insert the subset of columns into the destination table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply