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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to insert into two tables (id,array of id's) ?

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,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!

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
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/





Yes. New user form submission OR current user category update.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 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/

Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 2013-01-25 : 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!
Go to Top of Page

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 #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/

Go to Top of Page
   

- Advertisement -