SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query against list of rows without a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 07/15/2013 :  14:48:19  Show Profile  Reply with Quote
Hi

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

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/15/2013 :  15:20:36  Show Profile  Reply with Quote
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.
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 07/15/2013 :  15:27:02  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  01:05:48  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 07/16/2013 :  10:11:08  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 07/16/2013 :  10:33:54  Show Profile  Reply with Quote
quote:
Originally posted by evanburen

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.
---- Example:
SELECT * FROM 
(
	VALUES
	('123-45-6789'),
	('223-45-6789'),
	('323-45-6789')
)t(ssn)

Edited by - James K on 07/16/2013 10:34:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  13:37:37  Show Profile  Reply with Quote
quote:
Originally posted by evanburen

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


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 table

http://bradsruminations.blogspot.in/2011/01/so-you-want-to-read-csv-files-huh.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 07/16/2013 :  15:32:56  Show Profile  Reply with Quote
thanks for the great ideas
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  01:33:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000