I have basic dbreader access against a SQL 2008 db but don't have the ability to create a new table. I'm often given a list of several thousand SSNs and need to find them in my tables. Typically I would upload this list of SSNs to a new table and then join it to the others. How would go about such a query without being able to create a table? Thanks
Can you create a temp table or table variable? You could use that in lue of a permanent table. Or you could create a derived table, CTE or use an IN clause.
Thanks. Is there a limit on the number of characters or values I could use for IN clause? I typically need to run thousand 3-4k of them at a time.
For 3-4k of values the best options is to put them in a temporary table or raw file in SSIS and then do a join with it. putting them in a IN clause will require some dynamic logic in SSIS and may cause variable length of sql string variable to overflow. How do you generally get list of SSNs? as a CSV or in some file?
quote:Originally posted by visakh16 How do you generally get list of SSNs? as a CSV or in some file?
Yes, I typically receive them as a csv file. I also don't have access to SSIS. Thanks
Similar to what Lamprey suggested: use Excel (or a regex text editor) to put brackets and single quotes around the SSNs. Then, copy it to SSMS window and wrap it into a values clause to create a virtual table and use it like any other table.
The performance may not be great if you have several thousand SSNs. Alternative would be table variable.