insert multiple rows along with static values

Question

I'm very new to SQL. More familiar with procedural programming

I currently have local variables such as names that change within a loop. However, I also execute a function that returns many rows. Right now I loop through each row to insert the values into a result table.

How do I put date in a table with values (name, cname, val1, val2) when I have multiple rows?

example case:

set @name = 'test'
select cname, val1, val2 from myfunc(@name)

returns:

-----------------------
| cname | val1  | val2 |
------------------------
|'atest'| 1.3   | 42.0 |
|'btest'| 1.58  | 6.87 |
------------------------

and I'd like result table (@restable) to be:

-----------------------------
name  | cname | val1  | val2 |
-----------------------------
'test'|'atest'| 1.3   | 42.0 |
'test'|'btest'| 1.58  | 6.87 |
------------------------------

Note I still need @restable for more statements after this operation.

If you find yourself using loops in SQL you're probably doing it wrong. Loops are natural when you're used to procedural programming but with SQL you'll get much better performance if you can teach yourself to think in sets. It isn't easy but at the simplest level you can think of it as venn diagrams of sets of data. — Mike D.
– Mike D., Commented Sep 26, 2014 at 15:07

mohan111 · Accepted Answer · 2014-09-26 15:06:33Z

2

declare @t table(cname varchar(10),val1 decimal(10,2),val2 decimal(10,2))
insert into @t (cname,val1,val2) values ('atest',1.3,42.0)
insert into @t (cname,val1,val2) values ('btest',1.58,6.87)
select * from @t

declare @tt table(name varchar(10), cname varchar(10),val1 varchar(10),val2 decimal(10,2))
insert into @tt(name,cname,val1,val2)
select (select 'test')name, cname,val1,val2  from @t
select * from @tt

answered Sep 26, 2014 at 15:06

mohan111

8,9054 gold badges35 silver badges61 bronze badges

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

1 Comment

aperture Over a year ago

Thanks! This did answer my question, I failed when trying to insert because I included 'values' but this post shows you can just directly insert into.

paparazzo · Accepted Answer · 2014-09-26 15:12:14Z

1

I know this works with a table
Not sure about a function
If the function is returning a table this should work

set @name = 'test'
select @name as [name], cname, val1, val2 from myfunc(@name)

answered Sep 26, 2014 at 15:12

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

3 Comments

aperture Over a year ago

Hi Blam. That seems to give the right result, but doing "insert into @restable values" seems to fail to put that set into the table.

paparazzo Over a year ago

Show the insert. Because I don't think you should have the term 'values' in that insert.

aperture Over a year ago

You're right, I should not have had the insert, and noticed from mohan111's post that it was not included.

Collectives™ on Stack Overflow

insert multiple rows along with static values

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related