February 2, 2009 at 9:48 am
I need some help with a sql script that basically goes like this:
USE DATABASE [YOURDB] DELETE FROM YOURTABLE WHERE COLUMNNAME = mytextfilelist.txt
and here I have a text file list (seperated by CR). I'm not having much luck with the -i for input file. Is this possible? Thanks.
February 2, 2009 at 9:54 am
rbarrera (2/2/2009)
I need some help with a sql script that basically goes like this:USE DATABASE [YOURDB] DELETE FROM YOURTABLE WHERE COLUMNNAME = mytextfilelist.txt
and here I have a text file list (seperated by CR). I'm not having much luck with the -i for input file. Is this possible? Thanks.
Just a little vague on what you are trying to accomplish.
Please read the first article linked below in my signature block on how to assk for assistance. Follow its suggestions and you will get much better responses to your requests for assistance.
February 2, 2009 at 9:56 am
did you check the various fn_split solutions posted at SSC ?
e.g.
http://www.sqlservercentral.com/articles/T-SQL/63003/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2009 at 10:19 am
OK, I'm calling sqcmd from a batch file to delete rows in a table wherever the value in one column matches any items in my text file. Those items are seperateed by a carriage return, so one item per line. SQLCMD -i lets you execute a list of commands listed in some textfile. I want to use -i to substitute variables.
Here is the command line in my batch file:
REM 32 OR 64 BIT CHECK
FOR /F "TOKENS=3 DELIMS= " %%A IN ('SYSTEMINFO^|FINDSTR /I /C:"SYSTEM TYPE:"') DO (SET BITTYPE=%%A)
FOR /F "TOKENS=1 DELIMS=-" %%A IN ("%BITTYPE%") DO (SET BITTYPE2=%%A)
@ECHO OFF
IF /I '%BITTYPE2%'=='X86' SET PF=%PROGRAMFILES%
IF /I '%BITTYPE2%'=='X64' SET PF=%PROGRAMFILES(X86)%
SET SQLINSTANCE=SQLServerGoesHere
SET DATABASENAME=NameOfDataBaseGoesHere
[Mytable]=changed to protect the innocent
[Mycolumn]=changed to protect the innocent
MyVariable=changed to protect the innocent
"%PF%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "%SQLINSTANCE%" -Q "USE [%DATABASENAME%] DELETE FROM [Mytable] WHERE [Mycolumn] = '%MyVariable%'"
This script works just fine and dandy if %MyVariable% is one item. I want to replace
%MyVariable% with a list of items in a text file say mylist.txt and go through and delete all rows in the table where %MyVariable% matches anything in the list.
Mylist.txt opened up in notepad would show:
item1
item2
item3
item4
etc....
The formatting of the list satisfies the column constraints in [Mycolumn]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply