insert into table variable

Question

insert into table variable

riya_dave

Hall of Fame

Points: 3106
More actions
April 26, 2012 at 9:39 pm

#259989

DECLARE @Table TABLE(
[ID] int,
[CostA] real,
[CostB] real,
[CostC] real
)
INSERT INTO @Table
EXECUTE [dbo].[CostProcedure]
@RootId = @Rootid
@Rate = @Rate
can i do something like this,i am not sure,i can insert sp with param like this?

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

riya_dave (4/26/2012)
DECLARE @Table TABLE(
[ID] int,
[CostA] real,
[CostB] real,
[CostC] real
)
INSERT INTO @Table
EXECUTE [dbo].[CostProcedure]
@RootId = @Rootid
@Rate = @Rate
can i do something like this,i am not sure,i can insert sp with param like this?

Try it and find out. Just be sure that the result set returned from the procedure has 4 columns.

vinu512 SSCoach Points: 15729 More actions · Answer 2

Yes you can do that...if the SP does what you are asking it to do

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

CapnHector SSCoach Points: 16143 More actions · Answer 3

you can but as lynn said make sure you are returning 4 columns. i would also explicitly define the order (INSERT INTO bla (id,col1,col2) ...) get rid of any mistakes in setting up the table var.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

riya_dave Hall of Fame Points: 3106 More actions · Answer 4

this is not working,

i am calling another sp.

but its not getting into @temp table.

insert into @temp7

EXEC pPortfolioDashboard

@Portfolios = @AdjustPortfolio,

@FromDate = @FromDate,

@ToDate = @ToDate,

whats wrong,how to set values like (col1,col2)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Define 'not working'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

riya_dave (4/27/2012)
this is not working,
i am calling another sp.
but its not getting into @temp table.
insert into @temp7
EXEC pPortfolioDashboard
@Portfolios = @AdjustPortfolio,
@FromDate = @FromDate,
@ToDate = @ToDate,
whats wrong,how to set values like (col1,col2)

Of course it isn't getting into @temp, you are doing the insert into @temp7.

riya_dave Hall of Fame Points: 3106 More actions · Answer 7

riya_dave

Hall of Fame

Points: 3106

April 27, 2012 at 1:05 pm

#1480636

i mean @temp7 ,its not getting into @temp7

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

riya_dave (4/27/2012)
i mean @temp7 ,its not getting into @temp7

Then, as Gail asked, define "not working." Are you getting an error message? If so, how about showing us the error message.

riya_dave Hall of Fame Points: 3106 More actions · Answer 9

i am not getting any error msg,the query is executing indefinite time,

if i put insert into @temp7

without it i am getting data

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

Just means you have a very slow query. Nothing else wrong.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

riya_dave (4/27/2012)
i am not getting any error msg,the query is executing indefinite time,
if i put insert into @temp7
without it i am getting data

How do you know that the data isn't getting into the table @temp7?

riya_dave Hall of Fame Points: 3106 More actions · Answer 12

but without it i am getting data into seconds ,and with it its running more than 7 minutes ,still not getting data

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 13

riya_dave (4/27/2012)
but without it i am getting data into seconds ,and with it its running more than 7 minutes ,still not getting data

You really don't want to hear my next comment, because you will just ignore it any way and go on doing what ever it is you do. So, with that in mind, try this:

After the INSERT INTO @temp7 EXEC (rest of your code for the proc)

select * from @temp7 -- verify that data was inserted into @temp7

riya_dave Hall of Fame Points: 3106 More actions · Answer 14

riya_dave

Hall of Fame

Points: 3106

April 27, 2012 at 1:34 pm

#1480654

i am doing it already.still not getting anything