SQL Scripting - how to program input

Posted on 2014-07-11
Last Modified: 2014-07-31
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?
Question by:SeTech
    LVL 5

    Accepted Solution

    If you have a list of policies that can be extracted from a table with a common theme, you can use a Nested SQL statement.   A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query.

    A simple example of SQL nested query

    SELECT Model FROM Product
    WHERE ManufacturerID
    IN (SELECT ManufacturerID FROM Manufacturer WHERE Manufacturer = 'Dell')

    The nested query (withinin the IN clause) will select all models from the Product table manufactured by Dell:

    Inspiron B120
    Inspiron B130
    Inspiron E1705


    Author Comment

    Bob -- that looks like it will work. Thanks Much
    LVL 26

    Expert Comment

    Starting SQL 2005 the varchar allows MAX size:

    Declare @value_list varchar(MAX)='<values list here>'

    The limit is 2GB.
    LVL 68

    Expert Comment

    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
    LVL 47

    Expert Comment

    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?
    LVL 5

    Expert Comment

    by:Bob Bender
    Thanks for the vote of confidence  :D

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how the fundamental information of how to create a table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now