compare list values with with db ms sql 2008

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 2008 Forums

Transact-SQL (2008)

compare list values with with db ms sql 2008

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-06-21 : 05:54:36

Hi there,

I need to compare a list values with a db column.

Example : list ='12,5,6,8' and I need to know which of these values doesnt exist in db column values. How would I do that?

DbColumnKeys

1

10

100

1000

1001

1002

1003

1004

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 06:08:14

You'll need to split the list into values in a table.
See
http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/

;with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@s+',')
union all
select i=j+1, j=charindex(',',@s+',',j+1) from csvtbl
where charindex(',',@s+',',j+1) <> 0
) ,
cte as
(
select val = substring(@s,i,j-i)
from csvtbl
)
select *
from cte
where val not in (select DbColumnKeys from tbl)
where

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-06-21 : 07:07:36

good one...Thank you v m

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources