How to Execute a Text File from SQL Query

Is it possible to execute a text file from SQL query?

use xp_cmdshell and sqlcmd

EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName

How to read text file containing SQL code and execute it

I'd do database creation via a SQL script which checks for the existence of tables/views/SPs/etc. before creating them, then I'd execute it in the VB application via ADO.NET. I'd ship it with the application in a subdirectory. It's not a big deal to read text files, or to execute a SQL string via ADO.NET.

I'd have a VERSION table in the database that identifies what DB schema version is installed, and when I shipped upgrade scripts which modified the DB, I would have them update the VERSION table. The first version you ship is 1.0, increment as appropriate thereafter.

All the SQL object creation/detection/versioning logic would be in SQL. That's by far the simplest way to do it on the client, it's the simplest thing to develop and to test before shipping (MS SQL Management Studio is a godsend), it's the simplest thing to diff against the previous version, store in source control, etc.

Incidentally, I would also have my application interact with the database strictly via stored procedures, and I would absolutely never, ever feed SQL any concatenated strings. All parameters going to SQL should be delivered via ADO.NET's SqlParameter mechanism, which is very cool because it makes for clean, readable code, and sanitizes all of your parameters for you. Ever use a DB application that crashed on apostrophes? They didn't sanitize their parameters.

Run sql query which is saved in text file using python

Try using .read()

Ex:

cursor = cnxn.cursor()
with open('C:\Python_Script_Test\INSERTS.txt','r') as inserts:
    query = inserts.read()
cursor.execute(query)

Run SQL Query from Txt File Python

1. for standalone - I create sql, then write it to a file
2. your solution - read sql from file. Pass it to read_sql() along with connection

temptable = "tempx"
sql = f"""select Year, count(*) as c 
                        from {temptable} 
                        where Month=1 
                        and Sharpe between 1 and 2 
                        and stock like '%%2%%'
                        group by Year"""
with open("script.sql","w") as f: f.write(sql)
# read sql script from file.  pass it to pandas with connection
with open("script.sql") as f: sql = f.read()
engine = sqlalchemy.create_engine('mysql+pymysql://sniffer:sniffer@127.0.0.1/sniffer')
conn = engine.connect()
print(pd.read_sql(sql, conn).to_string(index=False))

output

 Year    c
 2018  930
 2019  932
 2020  958

SQL Server Management Studio - Query using text file

--What you need to do is import the text file into a table in your SQL database, and then compare its values to the table you want to query (which I have called AutoIDTest in my example).

--Using your example data, I've put together the following code that accomplishes this process.

--1. I created a destination table for the text file's values called TextImport. I've called the test text file E:\TestData.txt. Additionally, I'm assuming this text file only has one column, autoID.

--2. Then, I imported the data into the destination table using the BULK INSERT statement.

--3. Finally, I compared the data in TextImport to the table from which you are seeking values using an INNER JOIN statement.

CREATE TABLE AutoIDTest ---Create test table.  Since your first column doesn't have a name, I'm calling it ID. I'm assuming AutoID and ID are both of type int.
    (
    ID int,
    AutoID int,
    Name varchar(25),
    Description varchar(50)
    )

INSERT INTO AutoIDTest -- Populate test table
VALUES
    ( 1, 102, 'Jackson', 'Description1'),
    ( 2, 241, 'Edward', 'Description2'),
    ( 3, 333, 'Timothy', 'Description3'),
    ( 4, 437, 'Nikky', 'Description4'),
    ( 5, 534, 'Jeremy', 'Description5')

CREATE TABLE TextImport --Create destination table for text file.
    (
    AutoID varchar(20),
    )

BULK INSERT TextImport --Load Data from text file into TextImport table
   FROM 'E:\TestData.txt'  ---The name and location of my test text file.
   WITH
      (
         ROWTERMINATOR ='\n'  
      );

SELECT   ---Produce Output Data
    ID,
    t1.AutoID,
    Name,
    Description
FROM
    AutoIDTest AS t1
INNER JOIN
    TextImport AS t2
    ON t1.autoID = cast(t2.autoID AS int) --convert varchar to int

OUTPUT

--    ID          AutoID      Name                      Description
--    --------- ----------- ------------------------- -------------------
--    1           102         Jackson                   Description1
--    3           333         Timothy                   Description3
--    5           534         Jeremy                    Description5

---

Related Topics