SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to insert into two tables (id,array of id's) ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richdiaz99
Starting Member

22 Posts

Posted - 01/02/2013 :  15:50:15  Show Profile  Reply with Quote
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
2170 Posts

Posted - 01/02/2013 :  23:39:43  Show Profile  Reply with Quote
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 - 01/03/2013 :  10:39:33  Show Profile  Reply with Quote
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 - 01/03/2013 :  14:05:33  Show Profile  Reply with Quote
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 - 01/04/2013 :  10:50:12  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/04/2013 :  10:53:30  Show Profile  Reply with Quote
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 - 01/04/2013 :  11:11:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/04/2013 :  14:04:36  Show Profile  Reply with Quote
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 - 01/25/2013 :  16:46:06  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/26/2013 :  00:21:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000