Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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) ?
 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
2241 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
52326 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
52326 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
52326 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  
 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