Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

167 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

167 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
52326 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

167 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

3873 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
52326 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

167 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000