February 24, 2016 at 7:30 am
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
February 24, 2016 at 7:35 am
richieperkins (2/24/2016)
Dear Forum MembersI 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/
February 24, 2016 at 8:15 am
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);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply