Post reply

Text File Input to a Query

  • June 25, 2009 at 11:15 am

    #131468

    I saw an article & discussion on this topic, but came away still not understanding how to read the file into the query. I have a query that looks like this:

    select Entry_Id,Asset_ID_,Serial_Number,Type,Item,Region,Site,Department

    from AST_ASSET

    where Serial_Number IN (

    'CND53004W7','CND53309R6','CND53309R9','CND53309RH','CND53309SH',

    'CND53606LK','CND53606M3','CND53606P5','CND6070Q4X','CND6070QWD',

    'CND60900T8','CND60900VG','CND60900WC','CND60900X3','CND60900XT',

    'CND6090V1R','CND6090V27','CND6090V2J','CND6090V3X','CND6090V8M',

    'CND62604KL','CND626053K','CND6273MNV','CND6273MP7','CND6273NBR'

    )

    order by REGION,SITE,DEPARTMENT

    If I were to take the exact same text and save it in a file, how would I read the file into a query?

    Thanks for any help with this.

  • June 25, 2009 at 11:35 am

    #1015505

    sqlcmd?

  • June 25, 2009 at 2:16 pm

    #1015594

    Please have a look at BOL, "OPENROWSET function" -> OPENROWSET (Transact-SQL)

    Example right out of BOL:

    SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', FORMATFILE = 'c:\test\values.fmt') AS a;


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 25, 2009 at 2:20 pm

    #1015596

    Well that is one option. I can't tell if he wants to exec the query from tsql in a file, or do what you posted.

    Another option - create a linked server to the file and query right out of it LOL

  • June 25, 2009 at 2:33 pm

    #1015605

    That's one of the OP's responsibilities: To tell us what he/she is looking for...

    You're right though, it's not obvious yet.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 25, 2009 at 2:49 pm

    #1015622

    Thanks for the replies. To clarify, I would like to execute the query in QA.

    I'm too much of a SQL newbie to understand the OPENROWSET examples I saw in BOL. I was thinking it would be fairly easy to do this in QA. All I have experience with so far is SELECT and UPDATE in QA, nothing fancy.

    I can continue to use the method I included in my post, just was hoping to expand my knowledge a bit. If it can be done in QA, an example would be a big help.

    Thanks again for taking the time to respond.

  • June 25, 2009 at 2:58 pm

    #1015632

    You can save query text in an editor like notepad. I usually use the .sql extension, and open the text file using the open dialog in QA. You can save queries directly from QA using the save dialog so you can reuse them again.

    That seems like what you were asking about, but I'm not sure I've read the question correctly.

    And then again, I might be wrong ...
    David Webb

  • June 25, 2009 at 3:21 pm

    #1015654

    David,

    I'm familiar with the idea of saving text in an editor, so that's not a problem. I actually did the procedure described by Anatol Romanov in a discussion of an article by rcarlson. After creating the text file with Excel and then editing it in Textpad, I couldn't figure out how to use it, so just cut & pasted the contents into the WHERE clause of my query.

    Like I said, it works, but using a file instead might come in handy if I have a lot more data to input to my SELECT query.

    Thanks again, David.

  • June 25, 2009 at 3:42 pm

    #1015667

    Sorry. I think I'm being exceptionally thick today, for some reason.

    The OPENROWSET idea is probably the easiest one and it would be well worth your time to search this site for examples. The idea would be to read in the rows of the text into a table and then use the table values in the IN clause instead of the hard codedd values, allowing you to change the query by changing the values in the text file.

    I use the following to load a table from excel:

    create table budget_invoices (

    invoice_number varchar(25) NOT NULL)

    insert budget_invoices select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\downloads\Gpecumt1.xls',

    'select * from [Sheet1$]')

    select invoice_number from invoices

    where invoice_number NOT IN (select invoice_number from budget_invoices)

    And then again, I might be wrong ...
    David Webb

  • June 25, 2009 at 4:29 pm

    #1015683

    Excellent. I'll plug some test data into a query cloned off of yours & see what happens. I will search for some more examples, also.

    Thanks, David.

Viewing 10 posts - 1 through 9 (of 9 total)

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