Post reply

Query DB using text file data for the search criteria.

  • February 4, 2010 at 3:57 pm

    #220267

    The following works, but I've got 10,000 user Id's to match with names.

    select user_id, firstname, lastname, from users where user_id in ('abbotts3','abbottt4')

    The following is what I want to do:

    select user_id, firstname, lastname, from users where user_id = 'next line in C:\TEMP\accounts.txt'

    I have no VB script experience! Is this possible from just a query window. I can manually dump the window results to file, or find a way to end the script with it all appending to file. That would be sweet.

    -Steve

  • February 5, 2010 at 8:07 am

    #1114465

    What is the format of the text file? I'd look into dumping the contents into a table using bcp/bulk insert and then use a join.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 5, 2010 at 9:51 am

    #1114577

    Got with our DBA and came up with this:

    select email, user_id, firstname, lastname, student_id, from users where user_id in

    (SELECT a.* FROM OPENROWSET( BULK 'F:\NAMES.txt', FORMATFILE = 'F:\NAMES.fmt') as a)

    The NAMES.txt file is a single column list of entries that needs to live on the server, (where the SQL service accnt can get to it), not where the client tools are running from.

    The NAMES.fmt has some XML code to tell the script to limit the field size and drop to the next line. It needs to be in the same place.

    Contents of FORMATFILE:

    <?xml version="1.0"?>

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <RECORD>

    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="24"/>

    </RECORD>

    <ROW>

    <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>

    </ROW>

    </BCPFORMAT>

    -Steve

  • February 7, 2010 at 11:40 pm

    #1115294

    You can dump the results into a temp table using bulk insert like

    create table #temp(names varchar(100))

    BULK insert #temp from 'c:ames.txt'

    select * from #temp

    select a.users

    from yourtable a inner join #temp t on t.names = a.names

    --Divya

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply