By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

SQL Stored Procedure Insert List of variables

P: 55
Hi,

I am running a stored procedure where I pass in a large list of variables e.g.

ALTER PROCEDURE dbo.Insert(@Variable1 varchar,
@Variable2 varchar,.....etc.

What I would like to know, is there a possible way to loop around all the variables, I think there is 16 of them, and insert them into a table with a new row for each variable in the list??

Many thanks in advance.
Feb 2 '09 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
What you could do is to concatenate all the variables into a single variable separated by a comma (or a bar).

Then use this technique to convert it into a result set. Then you can do a single INSERT command.

Happy coding!

-- CK
Feb 2 '09 #2

P: 55
I changed my way of doing this and now have a varchar variable with the three values that I want to insert into the table all separated by a comma e.g.

DECLARE @Row1 varchar(10)

SET @Row1 = (SELECT Col1 + ',' + Col2 + ',' + Col3 FROM Table)

IF NOT(@Row1 = NULL)

BEGIN
INSERT INTO Table2
(ID, Col1, Col2, Col3)
VALUES (@ID, *********sticking point*********

END

As you can see from the code I want to split the @Row1 variable to separate them into three different inserts.

Many thanks.
Feb 3 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Are you planning to do something like this:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Table2
  2.                      (ID, Col1)
  3.          VALUES (@ID, @FirstColumnInRow1)
  4.  
  5. INSERT INTO Table2
  6.                       (ID, Col2)
  7.           VALUES (@ID, @SecondColumnInRow1)
  8.  
  9. INSERT INTO Table2
  10.                       (ID, Col3)
  11.           VALUES (@ID, @ThirdColumnInRow1)
  12.  
That would mean you'll have rows with at least one empty CO1, COL2 and COL3.

Is that what you're trying to do?


-- CK
Feb 3 '09 #4

P: 55
Yes thats pretty much it
Feb 4 '09 #5

ck9663
Expert 2.5K+
P: 2,878
The link I gave you will help you convert your string into a some sort of table that you can use to do a INSERT INTO...SELECT

-- CK
Feb 4 '09 #6

P: 55
Thanks for your help I got it working with the link you provided
Feb 5 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.