insert help! - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

insert help!

Author

Topic

alooma
Starting Member

8 Posts

Posted - 2008-04-12 : 16:20:39

hi
i have 3 tables like this

tblPost(postID int,postName char(50))
1
|
|
8
tblPostTag(postTagID int,postID int,tagID int)
8
|
|
1
tblTag(tagID int,tagName char(50))

i want to insert post="i like rock!" with tags="music,favorite,life"
how can do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 16:33:46

Any reason for not using IDENTITY columns in the three tables?

E 12°55'05.25"
N 56°04'39.16"

alooma
Starting Member

8 Posts

Posted - 2008-04-12 : 17:25:22

i have to use identity columns because of my design.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 17:40:19

The three table layouts posted originally don't have IDENTITY columns...

E 12°55'05.25"
N 56°04'39.16"

alooma
Starting Member

8 Posts

Posted - 2008-04-12 : 17:41:38

if you can make any design for
tag--infinity-> <-infinity--post
form
i can use it.

alooma
Starting Member

8 Posts

Posted - 2008-04-12 : 17:44:52

sorry!
postID for tblPost
postTagID for tblPostTag
tagID for tblTag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 17:53:08

Try something similar to this

CREATE PROCEDURE dbo.uspMyPostInsert
(
	@postName VARCHAR(50),
	@tagName VARCHAR(MAX)
)
AS

SET NOCOUNT ON

DECLARE	@postID INT

INSERT	tblPost
	(
		postName
	)
VALUES	(
		@postName
	)

SET	@postID = SCOPE_IDENTITY()

DECLARE	@Tags TABLE (tagID INT, tagName VARCHAR(50))

INSERT	@Tags
	(
		tagName
	)
SELECT	Data
FROM	dbo.fnParseList(',', @tagName)

INSERT		tblTag
		(
			tagName
		)
SELECT		t.tagName
FROM		@Tags AS t
LEFT JOIN	tblTag AS x ON x.tagName = t.tagname
WHERE		x.tagID IS NULL

UPDATE		d
SET		d.tagID = t.tagID
FROM		@Tags AS d
INNER JOIN	tblTag AS t ON t.tagName = d.tagName

INSERT	tblPostTag
	(
		postID,
		tagID
	)
SELECT	@postID,
	tagID
FROM	@Tags

Using these tables

tblPost(postID int identity(1, 1),postName char(50))
tblPostTag(postTagID int identity(1, 1),postID int,tagID int) 
tblTag(tagID int identity(1, 1),tagName char(50))

E 12°55'05.25"
N 56°04'39.16"

alooma
Starting Member

8 Posts

Posted - 2008-04-12 : 18:21:54

thank u i will try it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources