Insert data into SQL Server from stored procedure

Question

My knowledge in SQL not so far, please help me with my issue:

I have a script that generates some data. I need to insert this data into a table with following structure:

Variable_Sample (idsample, idvariable, value), idsample and idvariable - PK.

Here is the script:

Declare params cursor for
 Select distinct id_variable from [UIR_DB].[dbo].[Variable_Values]
open params
Declare @idparam int
Declare @csql nvarchar(max)  = ''
Declare @csql2 nvarchar(max)  = ''
Declare @i int = 1
fetch next from params into @idparam
while @@FETCH_STATUS = 0
begin
Select @csql2 = @csql2 + ', param' +LTrim(Str(@i))
Select @csql = @csql +  ' (Select value as param'+LTrim(Str(@i))+' from [UIR_DB].[dbo].[Variable_Values] where id_variable = '+LTrim(Str(@idparam))+') a'+LTrim(Str(@i))+' cross join'
Set @i = @i+1
fetch next from params into @idparam
end
Select @csql = '
SELECT id, CAST(SubString(Param,6,LEN(Param)-5) as int) param_id, param_val
FROM
(Select ROW_Number() over(order by '+SubString(@csql2,2,LEN(@csql2)-1)+') id, '+SubString(@csql2,2,LEN(@csql2)-1)+' from '+SubString(@csql,1,LEN(@csql)-11) + ') p
UNPIVOT
   (param_val FOR Param IN
      ('+SubString(@csql2,2,LEN(@csql2)-1)+')
)AS unpvt
'

print @csql
exec sp_executesql @csql
close params
deallocate params

Here is script result:

Script result .

So I need to put script result into table Variable_Sample(idsample, idvariable, value), where (idsample, idvariable) are the primary key of the table.

Yaroslav · Accepted Answer · 2013-05-05 20:59:10Z

2

You can add this to the end of the script, instead of the EXEC sp_executesql @csql:

CREATE TABLE #temptable (idsample INT, idvariable INT, value INT)
INSERT INTO #temptable EXEC @csql

SELECT idsample, idvariable, value FROM #temptable

DROP TABLE #temptable

answered May 5, 2013 at 20:59

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yaroslav Over a year ago

Great I could help, upvote if it was useful so other users can check it out too when looking for another alternative.

Johnno Nolan · Accepted Answer · 2013-05-05 20:55:59Z

1

I think all you need to is: Add an insert to @csql

e.g.

Insert into variable_sample(idsample, idvariable, value)
SELECT id, CAST(SubString(Param,6,LEN(Param)-5) as int) param_id, param_val
FROM
(Select ROW_Number() over(order by '+SubString(@csql2,2,LEN(@csql2)-1)+') id, '+SubString(@csql2,2,LEN(@csql2)-1)+' from '+SubString(@csql,1,LEN(@csql)-11) + ') p
UNPIVOT
   (param_val FOR Param IN
      ('+SubString(@csql2,2,LEN(@csql2)-1)+')
)AS unpvt

That's assuming that table already exsists.

answered May 5, 2013 at 20:55

Johnno Nolan

29.7k19 gold badges114 silver badges160 bronze badges

Collectives™ on Stack Overflow

Insert data into SQL Server from stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related