SQL Server: Inserting a list of ints into a temporary table

I have a list of IDs in a text file like this:

24641985 ,
24641980 ,
24641979 ,
24641978 ,
24641976 ,
24641974 ,
...
...
24641972 ,
24641971 ,
24641970 ,
24641968 ,
24641965)

There's tens of thousands of them.

Now I need to know which ids are in this list, that do not correspond to an ID in my table.

I guess I should put them into a temporary table, then say something like:

select theId 
  from #tempIdCollection
 开发者_开发问答where theId not in (select customerId from customers)

Problem is I don't know how to get them into the temp table!

Can anyone help? This doesn't have to be efficient. I've just got to run it once. Any solution suggestions welcome!

Thanks in advance!

-Ev

I'd use a table variable. You declare it just like a regular variable.

declare @tempThings table (items int)
insert @tempThings values (1)

Have a "permanent temp" table, also known as an "inbox" table. Just a simple tabled named something like "temp_bunchOfKeys".

Your basic sequence is:

1) Truncate temp_bunchOfKeys

2) BCP the text file into temp_bunchOfKeys

3) Your sql is then:

select theId 
  from Temp_BunchOfKeys
 where theId not in (select customerId from customers)

I had the same problem but with strings instead of integers, and solved it by using a split function (see code below) that returns a table variable with the list content. Modify the function to suit your purpose.

Example of how to call the function

create table #t (Id int, Value varchar(64))
insert into #t (Id, Value)
select Id, Item
from dbo.fnSplit('24641978, 24641976, ... 24641972, 24641971', ',')
/*Do your own stuff*/
drop table #t

Function

if object_id(N'dbo.fnSplit', N'TF') is not null
    drop function dbo.fnSplit
GO

create function dbo.fnSplit(@string varchar(max), @delimiter char(1))
returns @temptable table (Id int, Item varchar(8000))
as
begin
    -- NB! len() does a rtrim() (ex. len('2 ') = 1)
    if ( len( @string ) < 1 or @string is null ) return

    declare @idx int
    declare @slice varchar(8000)
    declare @stringLength int
    declare @counter int ; set @counter = 1

    set @idx = charindex( @delimiter, @string )

    while @idx!= 0
    begin
        set @slice = ltrim( rtrim( left(@string, @idx - 1)))
        set @slice = replace( replace(@slice, char(10), ''), char(13), '')
        insert into @temptable(Id, Item) values(@counter, @slice)

        -- To handle trailing blanks use datalength()
        set @stringLength = datalength(@string)
        set @string = right( @string, (@stringLength - @idx) )
        set @idx = charindex( @delimiter, @string )
        set @counter = @counter + 1
    end

    -- What's left after the last delimiter
    set @slice = ltrim(rtrim(@string))
    set @slice = replace( replace(@slice, char(10), ''), char(13), '')
    insert into @temptable(Id, Item) values(@counter, @slice)

return
end
GO

You can copy paste all those ids from text file to a excel file. Then use import from excel feature in the Sql server to create a table out of that excel file. Quite simple really. Let me know if you need more specific instructions.