Where statment and external txt file

• richieperkins

  SSC Journeyman

  Points: 82

  More actions

  February 24, 2016 at 7:30 am

  #324539

  Dear Forum Members

  I am new to SQL done the first administrator course but as a platform support engineer and not a DBA I do find some of the more simple tasks difficult. I am looking to create a txt file of numbers a single column with no front or rear space and I would like to run the below command so that the value in the WHERE statement is pulled from the list in the txt document.

  I see there is OPENROWSET and BULK but im not sure how to work this in the below query, any help would be gratefully received?

  Update dbo.AeFSState

  set FSState = 'ReleaseWait'

  WHERE UID = '195670917';

  I would like the number in WHERE UID = '19567917'; to be pulled from my txt file something like

  Update dbo.AeFSState

  set FSState = 'ReleaseWait'

  WHERE UID = 'c:\list\12345.txt';

  Richie

• Sean Lange

  SSC Guru

  Points: 286601

  More actions

  February 24, 2016 at 7:35 am

  #1860720

  richieperkins (2/24/2016)

  ---

  Dear Forum Members

  I am new to SQL done the first administrator course but as a platform support engineer and not a DBA I do find some of the more simple tasks difficult. I am looking to create a txt file of numbers a single column with no front or rear space and I would like to run the below command so that the value in the WHERE statement is pulled from the list in the txt document.

  I see there is OPENROWSET and BULK but im not sure how to work this in the below query, any help would be gratefully received?

  Update dbo.AeFSState

  set FSState = 'ReleaseWait'

  WHERE UID = '195670917';

  I would like the number in WHERE UID = '19567917'; to be pulled from my txt file something like

  Update dbo.AeFSState

  set FSState = 'ReleaseWait'

  WHERE UID = 'c:\list\12345.txt';

  Richie

  Hi and welcome to the forums. In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 24, 2016 at 8:15 am

  #1860734

  Using OPENROWSET is a bad idea when combined with an update (if at all possible). You should import the values from the text file into a table. To do that, you simply use BULK INSERT. Remember that when you're referring the file, the path should be written as a path for the server and not your local computer.

  This is an example that might not work, but it should give you an idea.

  CREATE TABLE #UIDs( UID int);

  BULK INSERT tempdb..#UIDs

  FROM 'c:\list\12345.txt'

  WITH

  (

  ,CODEPAGE = 'RAW'

  ,DATAFILETYPE = 'char'

  );

  Update dbo.AeFSState

  set FSState = 'ReleaseWait'

  WHERE UID IN (SELECT u.UID FROM #UIDs);

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2