| Author |
 Topic  |
|
|
richdiaz99
Starting Member
22 Posts |
 Posted - 01/02/2013 : 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,CategoryId
How 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!
|
Edited by - richdiaz99 on 01/02/2013 15:54:01
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1681 Posts |
Posted - 01/02/2013 : 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 - 01/03/2013 : 10:39:33
|
Thank you.
Anyone have a link to an article on how to do this? |
|
|
|
erikhaselhofer
Starting Member
14 Posts |
Posted - 01/03/2013 : 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 - 01/04/2013 : 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
India
47969 Posts |
Posted - 01/04/2013 : 10:53:30
|
Will you be passing categories of only one subscriber at a time?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 01/04/2013 : 11:11:14
|
quote:
Originally posted by visakh16
Will you be passing categories of only one subscriber at a time?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Yes. New user form submission OR current user category update. |
Edited by - richdiaz99 on 01/04/2013 11:15:59 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47969 Posts |
Posted - 01/04/2013 : 14:04:36
|
some thing like
CREATE PROC InsertSubscriberData
@firstname varchar(100),
@lastname varchar(100),
@email varchar(100),
@categorylist varchar(2000)
AS
DECLARE @SubscriberID int
SELECT @SubscriberID = id
FROM Subscribers
WHERE firstname = @firstname
AND lastname = @lastname
IF @SubscriberID IS NULL
BEGIN
INSERT INTO Subscribers (firstname,lastname,email)
VALUES(@firstname,@lastname,@email)
SET @SubscriberID = SCOPE_IDENTITY()
END
SELECT Val INTO #Categories
FROM dbo.ParseValues(@categorylist,',')
INSERT Categories (CategoryName)
SELECT c.Val
FROM #Categories c
WHERE NOT EXISTS(SELECT 1 FROM Categories WHERE categoryname= c.Val)
INSERT SubscribersCategories (Subscriberd,CategoryId)
SELECT @SubscriberID,
cat.id
FROM #Categories c
INNER JOIN Categories cat
ON cat.CategoryName = c.Val
GO
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 01/25/2013 : 16:46:06
|
quote:
Originally posted by visakh16
[..
SELECT Val INTO #Categories
FROM 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 2008R2
Also
quote:
Originally posted by visakh16
SELECT Val INTO #Categories
FROM dbo.Split1(@categorylist,',')
What is Val above in reference to? the Category Table ID column?
Thanks! |
Edited by - richdiaz99 on 01/25/2013 17:19:28 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47969 Posts |
Posted - 01/26/2013 : 00:21:50
|
quote:
Originally posted by richdiaz99
quote:
Originally posted by visakh16
[..
SELECT Val INTO #Categories
FROM 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 2008R2
Also
quote:
Originally posted by visakh16
SELECT Val INTO #Categories
FROM dbo.Split1(@categorylist,',')
What is Val above in reference to? the Category Table ID column?
Thanks!
yes
see here fpr ParseValues function
Val is column returned by function
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
how to insert into two tables (id,array of id's) ?
