Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akashenk
Posting Yak Master
111 Posts |
 Posted - 2006-09-19 : 11:38:47
|
| I have a table (UserRoles) that has fields user_id and role_id. I would like to be able to insert multiple rows into this table using a single stored procedure by passing two parameters, the user_id (@userId) and a comma delimited string representing the role_id for each individual record (@roleIds).I am able to parse the @roleIds input parameter into a table using (SELECT * FROM f_IterCharListToTable(@roleIds, DEFAULT)) where f_IterCharListToTable is a table function I have attained from http://www.sommarskog.se/arrays-in-sql.htmlHowever, I'm not sure of the syntax for inserting the multiple rows into the UserRoles table.Any ideas? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-19 : 11:46:40
|
[code]insert into table( . . . )select @userId, strfrom dbo.f_IterCharListToTable(@roleIds, DEFAULT)[/code]
KH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-19 : 12:03:39
|
| Make sure number of columns and their datatypes matchMadhivananFailing to plan is Planning to fail |
|
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2006-09-19 : 12:06:42
|
| Thanks, that did it... one thing though, can you explain the syntax:"str from dbo.f_IterCharListToTable(@roleIds, DEFAULT)"the dbo.f_IterCharListToTable(@roleIds, DEFAULT) table obviously doesn't have the same columns as I am inserting, so I didn't think it would work. |
|
|
|
|
|
|
Insert Multiple rows in one SP