Sincronize values. Confusing - 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)

Sincronize values. Confusing

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 13:48:49

Hello,

I have two tables as follows:

[Labels] > LabelId (PK), LabelName

[LabelsInPosts] > LabelId (PK), PostId (PK)

I am trying to do the following:

1. Two parameters are given:
- LabelsList (NVARCHAR)
> LabelsList has the format: Label1,Label2,Label3,Label4

- PostId (UNIQUEIDENTIFIER)

2. Synchronize LabelsList with LabelsInPosts.
This means that for the given postId:

a) If a label from LabelsList is not associated with postId then
add it to LabelsInPosts.
Note: If the label do no exist in Labels table then it should
be added to it.

b) If a label from LabelsList is associated with the given postId
but it is not in LabelsList then it should be removed from
LabelsInPosts.
Note: If the removed label is not associated with any other
posts in LabelsInPosts then it should be removed from
Labels table.

Basically this is a synchronization.

Could someone, please, help me out?

I am completely lost in how to pull this out.

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 13:55:44

[code]
insert into LabelsInPosts(LabelId, PostId)
select a.LabelId, @PostId
from Labels a inner join CSVTable(@LabelsList) b
on a.LabelId = b.stringval
left join LabelsInPosts c
on a.LabelId = c.LabelId
where c.LabelId is null

insert into Labels(LabelId, LabelName)
select a.LabelId, NULL
from LabelsInPosts a inner join CSVTable(@LabelsList) b
on a.LabelId = b.stringval
left join Labels c
on a.LabelId = c.LabelId
where c.LabelId is null
[/code]
CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 14:21:53

Hi,

Trying to understand your code now. :-)

I was also trying to use the following code:

-- Declare position parameters
DECLARE @NextPosition SMALLINT,
@LastPosition SMALLINT

-- Set next position to 0
SELECT @NextPosition = 0

-- Loop through each label
WHILE @NextPosition <= DATALENGTH(@LabelsList)
BEGIN
SELECT @LastPosition = @NextPosition,
@NextPosition = CASE
WHEN CHARINDEX(',', @LabelsList, @LastPosition + 1) = 0 THEN DATALENGTH(@LabelsList) + 1
ELSE CHARINDEX(',', @LabelsList, @LastPosition + 1)
END
SELECT SUBSTRING(@LabelsList, @LastPosition + 1, @NextPosition - @LastPosition - 1)
END

This function was given by Peso in:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Should I use CSVTable instead?

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 21:52:54

quote:

Originally posted by khtan


insert into LabelsInPosts(LabelId, PostId)
select	a.LabelId, @PostId
from	Labels a inner join CSVTable(@LabelsList) b
	on	a.LabelId	= b.stringval
	left join LabelsInPosts c
	on	a.LabelId	= c.LabelId
where	c.LabelId	is null

insert into Labels(LabelId, LabelName)
select	a.LabelId, NULL
from	LabelsInPosts a inner join CSVTable(@LabelsList) b
	on	a.LabelId	= b.stringval
	left join Labels c
	on	a.LabelId	= c.LabelId
where	c.LabelId	is null

CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

Hi,

I am having a few problems while trying this code.

How does this code removes a label from LabelInPosts when it is not in LabelsList anymore. And if there are not any other post associated to that label then it should be also removed from Labels. (b) in my original post.

Maybe I am not thinking this right. Am I?

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 22:05:13

"Should I use CSVTable instead?"
You can use the CSVTable or Peter's fnParseList. Both CSVTable and fnParseList will works for you but not fnParseString.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 22:16:05

"And if there are not any other post associated to that label then it should be also removed from Labels. (b) in my original post."


delete  b
from    CSVTable(@LabelsList) a inner join Labels b
	on	a.stringval = b.LabelId
	left join LabelsInPosts c
        on      a.stringval = c.LabelId
where   c.LabelId is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources