Author |
Topic |
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-02 : 15:50:15
|
I have a subscribers table and categories table and a 'bridge' table for them. I know how to insert into one table but how do I do it into two tables at the same time? My Subscriber table insert has to complete before I even start the SubscibersCategories insert… do I make two separate calls or is there a way to make it into one statement (and eventually stored procedure)? How do I format the input parameter ("array"? Table?) for the Categories subscribed to?"Subscribers"id,firstname,lastname,email"Categories" (just data, has 10 rows already)Id,CategoryName"SubscribersCategories" (bridge for these two tables)Id,SubscriberId,CategoryIdHow do I insert a new Subscriber that wants to subscribe to Categories 1,2 and 3? (that's 3 new 'bridge' rows in SubscibersCategories), any articles out there explaining this?Thank you! |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-02 : 23:39:43
|
Write a stored procedure by passing input data of those tables and also CommaSeparatedValues (CSV) for Category id's.. After that parse CSV input and then insert into respective tables--Chandu |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-03 : 10:39:33
|
Thank you.Anyone have a link to an article on how to do this? |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-01-03 : 14:05:33
|
If you built the form correctly, on submit, you should have an array containing the categories. It's relatively straight-forward to process the array.A search on your language and checkboxes will probably get you an answer. |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-04 : 10:50:12
|
This array of pk parameters, do I just handle that as a comma delimited string varchar? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 10:53:30
|
Will you be passing categories of only one subscriber at a time?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-04 : 11:11:14
|
quote: Originally posted by visakh16 Will you be passing categories of only one subscriber at a time?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Yes. New user form submission OR current user category update. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 14:04:36
|
some thing likeCREATE PROC InsertSubscriberData@firstname varchar(100),@lastname varchar(100),@email varchar(100),@categorylist varchar(2000)ASDECLARE @SubscriberID intSELECT @SubscriberID = idFROM SubscribersWHERE firstname = @firstnameAND lastname = @lastnameIF @SubscriberID IS NULLBEGININSERT INTO Subscribers (firstname,lastname,email)VALUES(@firstname,@lastname,@email)SET @SubscriberID = SCOPE_IDENTITY()ENDSELECT Val INTO #CategoriesFROM dbo.ParseValues(@categorylist,',')INSERT Categories (CategoryName)SELECT c.ValFROM #Categories cWHERE NOT EXISTS(SELECT 1 FROM Categories WHERE categoryname= c.Val)INSERT SubscribersCategories (Subscriberd,CategoryId)SELECT @SubscriberID,cat.idFROM #Categories cINNER JOIN Categories catON cat.CategoryName = c.ValGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-25 : 16:46:06
|
quote: Originally posted by visakh16[..SELECT Val INTO #CategoriesFROM dbo.ParseValues(@categorylist,',')
Thanks, I finally got a chance to work on this and I'm trying to get it to work, I.m stuck on the Parser part:Question, where is the function dbo.ParseValues? Do I have to write it? I'm using SQL 2008R2Alsoquote: Originally posted by visakh16SELECT Val INTO #CategoriesFROM dbo.Split1(@categorylist,',')
What is Val above in reference to? the Category Table ID column?Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-26 : 00:21:50
|
quote: Originally posted by richdiaz99
quote: Originally posted by visakh16[..SELECT Val INTO #CategoriesFROM dbo.ParseValues(@categorylist,',')
Thanks, I finally got a chance to work on this and I'm trying to get it to work, I.m stuck on the Parser part:Question, where is the function dbo.ParseValues? Do I have to write it? I'm using SQL 2008R2Alsoquote: Originally posted by visakh16SELECT Val INTO #CategoriesFROM dbo.Split1(@categorylist,',')
What is Val above in reference to? the Category Table ID column?Thanks! yessee here fpr ParseValues functionVal is column returned by functionhttp://visakhm.blogspot.in/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|