Hello
I have a long list e.g. (1,2,3...) which I want to use in a way of WHERE COL1 NOT IN (1,2,3...).
How can I store this in a temp variable and how can I reuse it?
I am a bit confused because it is not an actual table but a list.
Thanks!
Have you read the documentation about temp tables? At this point everyone is strongly advising you to do that or take a course?
1 Like
I read how to create a table and insert rows but not sure on how to insert columns/lists.
Also, I don't mind inserting the list as a row instead of a column, but would that work in WHERE [A] IN #MYTABLE ?
Thanks
Try it and see if it works. Many have answered similar questions for you before
- IN #mytable
- Creating temp table
Do research on these and let us know what you come across
1 Like
There is no concept of a 'temp' variable - variables, by definition are only in the scope of the batch where they are declared and used.
It seems you want to use a simple variable - using IN or NOT IN - and we have pointed out that this is not possible. This is not an issue with SQL Server - rather it is how SQL (the language) works. The language works with tables - and does not know anything about arrays or lists.
With that said - you can use a list in 2 ways:
- Convert the list to a table - using STRING_SPLIT or a custom script to split the list into rows.
- Dynamic SQL - this requires being able to modify the list if it has 'string' values and wrap each string in single-quotes.
Whether you use [NOT] IN or [NOT] EXISTS or LEFT JOIN or INNER JOIN depends on your requirements and testing to determine which one works better with your tables/indexes and provides the correct results.
There are many different ways to approach this type of problem - and we have shown many of them. Review those solutions and use the one that fits best for your situation.
hi
My idea # 1
Store the 1,2,3 in a temp table
keep using them wherever you want
Example
create table #Temp ( id int )
-- Put the list into the Temp Table
insert into #Temp values 1
insert into #Temp values 2
insert into #Temp values 3
-- Here you want to see the List Values
-- IN (1,2,3)
select * from Table1 join Temp on a.Col1 = B.id
-- Here you want to see the List Values are not there
-- NOT IN (1,2,3)
select * from Table1 join Temp on a.Col1 <> B.id
Are you Really New to SQL ..
I would be GLAD to
sit with you live ONE on ONE
using remote desktop
..
Please let me know
NO charges or anything .. FREE ..
Thanks for your offer, I appreciate the replies here.
The thing is my list is huge. It's not 1,2,3, it's rather 1,2,3,...,3800.
Obviously, I will need to contain that list in a line rather than a column, as it would require me to scroll endlessly to check the rest queries in the file.
Based on your input, I think it would make sense to convert the list into a table's row and then maybe pivot it to make in a single column table so that I use it in the IN #MyTable.
I also noticed that IN #MyTable does not work, it has to be IN (SELECT COL1 FROM #MyTable).
1,2,3,...,3800.
My idea is
These numbers can be generated very easily
writing small piece of code
There are also TALLY Tables
Oh it's example numbers. It is not a sequence, it's random numbers. It's like 13498592384,1234958234958,2349592348,239458239485,...,98234598345234 (total of 3,800 numbers).
I found the code below:
IN (SELECT convert(int, value) FROM string_split('1,2,3,4', ','))
I am trying to make the following work:
DECLARE @MyList varchar = '5,2,3,....4,' --4,000 numbers separated by comma
IN (SELECT convert(int, value) FROM string_split(@MyList, ','))
--OR
IN convert(int,string_split(@MyList, ','))
But I am not sure if they work. Any input please?
Try it to see which one works
1 Like
this is not obviously. fields like this should not be allowed. You may get this from an outside source, but to store like this is not recommended. It's not Sargable and just a huge nightmare. Whatever these values are, they should be stored individually in a column, then you can left join or join or whatever you need. That's what @jeffw8713 was explaining.
1 Like
where is this variable coming from. and if there are new values how do you go about adding it to @MyLIst? What will happen when you go to Cancun on vacation and a new value comes into play? You need a more resilient method to add to this list. a variable is not the way to go. You will need a process where in new values are added in an automated fashion
hi
My idea
was to duplicate what you are trying
Its working .. dont know why yours is not working
DECLARE @MyList varchar(20) = '5,2,3'
drop table #test
create table #test( id int )
insert into #test select 1
insert into #test select 2
select '@MyList', @MyList
select 'Sample Data', * from #test
select 'SQL Result', * from #test where id in (SELECT convert(int, value) FROM string_split(@MyList, ','))
No - you would not contain that list in a column...in SQL you create a table to hold those values. If this list is going to be reusable for various queries then putting it in a table makes the most sense - then you just JOIN to the table or use IN/EXISTS.
If you are constrained in this environment from creating any objects - it is going to be very difficult to do any work, as you are going to have to repeat code in every script. If that is the case, then you need to work with the DBA team to figure out a better alternative - one where you can be given access to create the objects needed for your work.
As we have stated - repeatedly - the construct is: IN (SELECT value FROM string_split(@myList, ',')). Or - if you want to avoid implicit conversions: IN (SELECT convert(int, value) FROM string_split(@myList, ','))
If you are not going to even attempt the proposed solutions...then I don't see how any of us can help you.
2 Likes
One always wants to help another fellow SQL Dev but at some point you got to cut the umbilical cord, you go to push the chick off the precipice and let her fly baby!
Tito - you now owe me a new keyboard and mouse 
And it just started snowing again...
hope you are not in Dallas. It's getting pretty bad there.
Lubbock, it is bad enough but nowhere near what is happening in Dallas
It's good that it works, thanks.
Is 'value' a built-in variable? I have not come across it. I know VALUES but not value.