February 11, 2015 at 2:35 am
Hi I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma seperated parameter and then I have a function which converts this parameter to a table with seperate rows.
for eg : a,b,c,d
converted to result table
result
a
b
c
d
I want to insert each row of the result table into another table. Please can someone tell me how to do that
for eg the table after the funtion is :
CREATE TABLE #result
(
Subject varchar(100)
)
insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')
so the pseudo code is something like
for each row in #result
insert row into another table
February 11, 2015 at 2:46 am
This article might provide you with the answer http://www.sqlservercentral.com/articles/Tally+Table/72993/
It provides a very efficient string splitter that return a Table.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 11, 2015 at 2:48 am
I already have a spitter function mate...please have a read through my question again...thanks.
February 11, 2015 at 3:02 am
Using a string is a bit wrong here,
Using a table valued parameter https://msdn.microsoft.com/en-us/library/bb510489.aspx would be a better solution in my book.
February 11, 2015 at 3:08 am
Why is it wrong can you explain?
I think there is nothing wrong in sending a comma seperated list.
My question is to insert each row of the table into another table. So if I have a table valued parameter ..i still have to go through each row of that right?
February 11, 2015 at 3:17 am
nwtsqlserv (2/11/2015)
I already have a spitter function mate...please have a read through my question again...thanks.
Why would you loop through a record set if the output is already a record set just do an INSERT INTO <table> like below
DECLARE @String varchar(100) = 'A,B,C,D'
INSERT INTO MyTable
SELECT *
FROM Reporting.SplitParam(@String,',')
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 11, 2015 at 3:24 am
That is what my function does ..insert the comma separated string into a table ...for eg in ur case its MyTable..
Now I want to insert each row of this Mytable into another table.
I have another table with lot of columns. I just want to populate one of the columns with Mytable.
February 11, 2015 at 3:26 am
nwtsqlserv (2/11/2015)
Why is it wrong can you explain?I think there is nothing wrong in sending a comma seperated list.
Other than going through the unnecessary overhead of concatenating the string on the client side and splitting it up on the server side ( + invoking the internals of latching / logging / locking etc.. ) nothing at all.
Microsoft added table valued parameters to the sql server product for a very good reason, to pass a set of data ( which you do have ) to sqlserver in a more performant ( and easier ) way.
My question is to insert each row of the table into another table. So if I have a table valued parameter ..i still have to go through each row of that right?
Nope, you will have a set of data. Your TSQL code will simply be:
INSERT INTO <DestinationTable>
SELECT blah
FROM @<TableValuedParameter>
No looping is needed.
February 11, 2015 at 3:33 am
yeah thats sound good. I will use table valued parameter.
using cursor is it an old approach??
something like this
CREATE TABLE #result
(
Subject varchar(100)
)
Create Table #result2
(
Subject varchar(100)
)
insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')
DECLARE @ColExpir varchar(100)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT subject
FROM #result
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #result2 values (@ColExpir)
FETCH NEXT FROM @MyCursor
INTO @ColExpir
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
select * from #result2
February 11, 2015 at 3:38 am
This is the point I was trying to make why cant you just do this
CREATE TABLE #result
(
[Subject] varchar(100)
)
Create Table #result2
(
[Subject] varchar(100)
)
insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')
INSERT INTO #Result2 ([Subject])
SELECT [Subject]
FROM #result
Cursors are very expensive, and inefficient in all but a few applications.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 11, 2015 at 3:44 am
Yup, cursors and any form of looping / iterative is to be avoided in SQL if at all possible ( and that advice hasn't changed since year dot )
There are any number of articles on the whys and wherefores, google for the term "RBAR SQL" and you should find some good resources.
February 11, 2015 at 4:30 am
thanks
February 11, 2015 at 4:57 am
Hi I have one more question. I am using table valued parameter to pass the whole list to the stored proc. But now I want to insert this table valued parameter into another table, but I want to insert id of another table which is inside this stored proc.
so basically there is an insert done on tableA and I have to pick the unique id of this tableA and insert that unique id to myTable to which I pass the table valued parameter. How can I do that?
February 11, 2015 at 5:29 am
You don't need a table value parameter to pass your value. You already have it in a comma-delimited string, so just use the splitter against it. If you have Jeff's DelimitedSplit8K function, it'll be efficient and simple to use. If not, the articles in my signature are well worth your time to read and implement. Here's an example of splitting a parameter @p into a table:
DECLARE @p varchar(200) = '1,145,2,3,55,859';
SELECT s.Item
FROM util.dbo.DelimitedSplit8K(@p, ',') s;
You can fire your INSERT statement using the query and it'll populate your temp table.
February 11, 2015 at 6:46 am
nwtsqlserv (2/11/2015)
Hi I have one more question. I am using table valued parameter to pass the whole list to the stored proc. But now I want to insert this table valued parameter into another table, but I want to insert id of another table which is inside this stored proc.so basically there is an insert done on tableA and I have to pick the unique id of this tableA and insert that unique id to myTable to which I pass the table valued parameter. How can I do that?
Not entirely with you on this, you may need to give a worked example , but i think you should investigate the OUTPUT clause.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply