| Author |
Topic |
|
evanburen
Posting Yak Master
167 Posts |
 Posted - 2013-07-15 : 14:48:19
|
| HiI 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 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-15 : 15:20:36
|
| 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. |
 |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-07-15 : 15:27:02
|
quote:
Originally posted by Lamprey
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 01:05:48
|
quote:
Originally posted by evanburen
quote:
Originally posted by Lamprey
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?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-07-16 : 10:11:08
|
quote:
Originally posted by visakh16How 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-16 : 10:33:54
|
quote:
Originally posted by evanburen
quote:
Originally posted by visakh16How 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. ---- Example:SELECT * FROM ( VALUES ('123-45-6789'), ('223-45-6789'), ('323-45-6789'))t(ssn) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 13:37:37
|
quote:
Originally posted by evanburen
quote:
Originally posted by visakh16How 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
Either use earlier suggestion or if you want to completely automate it write a t-sql procedure/ code using OPENROWSET to populate a table from file data and then use it for join to your main tablehttp://bradsruminations.blogspot.in/2011/01/so-you-want-to-read-csv-files-huh.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-07-16 : 15:32:56
|
| thanks for the great ideas |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 01:33:34
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Query against list of rows without a table