SQL Scripting - how to program input
I have a script that I run on a SQL Server 2008 and it works fine. My problem or need is that the script is a query that does a condition on the select with a where X in (a, b, c, d, e,). Specifically I want to search a table where the rows are a, b, c, d, e, etc. (a,b,c,d,e are policy numbers). As I state the script works when I manually input the policy numbers and my problem is that I can copy/paste the policy numbers into the In ( ) but I am limited to 8000 characters which is the max amount of characters allowed. I have 1000's of policy numbers that are all 16 characters.
Any ideas that would allow me to input all my policy numbers at once?
Any ideas that would allow me to input all my policy numbers at once?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Starting SQL 2005 the varchar allows MAX size:
Declare @value_list varchar(MAX)='<values list here>'
The limit is 2GB.
Declare @value_list varchar(MAX)='<values list here>'
The limit is 2GB.
Load the policy numbers into a keyed temp table and join to it.
CREATE TABLE #policy_numbers (
policy_number char(16) PRIMARY KEY
)
INSERT INTO #policy_numbers
SELECT ...list of policy#s...
SELECT dt.*
FROM data_table dt
INNER JOIN #policy_numbers pn ON
pn.policy_number = dt.policy_number
...
CREATE TABLE #policy_numbers (
policy_number char(16) PRIMARY KEY
)
INSERT INTO #policy_numbers
SELECT ...list of policy#s...
SELECT dt.*
FROM data_table dt
INNER JOIN #policy_numbers pn ON
pn.policy_number = dt.policy_number
...
What is the volatility of the parameter? (do you change it often or infrequently or rarely)
Where do you copy/paste the policy numbers from?
Where do you copy/paste the policy numbers from?
Thanks for the vote of confidence :D
ASKER