Sometimes it might be necessary to read an arbitrary SQL query from a file and execute it. Here is an example that does the following:
- Read query from a file (specified by a parameter)
- Send the query to a sub-project
- The sub-project executes the query and exports the result into a text file (also specified by a parameter)
Unpack the example and run export-data.morph in EasyMorph. When executed successfully it should create a text file named “export.csv” in the project folder.
export-data.zip (241.4 KB)
Hello, i have a similar problem.
I have a database function that returns a SQL statement.
I would like to execute this via Database-Command.
something like this:
DECLARE @SQLSTATEMENT CHAR(100);
SET @SQLSTATEMENT = execute (GetSQLStatement(‘MySchemaName’, ‘MyTableName’))
Does that work too?
Yes, you can try to use a similar approach.
I have a scenario where I want to build several sql stored procedures from one database to new database. I have the scripts all in one text file (appended together). I was just going into the new database, pasting the script and executing it. It works great in SSMS, however when I try to use Easymorph and paste that same script in as a custom query, it give me an error:
Error: ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.
Incorrect syntax near ‘GO’.
What I’d love to do is have Easymorph read my .sql file from a path Ex. C:\em\CreateTable.sql and then execute it. Is this something Easymorph can do?
Ex of .sql file:
DROP Procedure if exists [dbo].[sp1]
CREATE PROCEDURE [dbo].[sp1]
select getdate() as getdate1
DROP Procedure if exists [dbo].[sp2]
CREATE PROCEDURE [dbo].[sp2]
select getdate() as getdate2
Any help would be awesome, thanks for reading.
The GO command isn’t a Transact-SQL statement, but a special command recognized by several MS utilities including SQL Server Management Studio code editor.
And it’s not supported by EasyMorph.
EasyMorph supports executing a batch of SQL statements when those statements are separated by a semicolon. But it seems that this approach won’t work with several stored procedures or with stored procedures, prepended with a DROP PROCEDURE command.
As a workaround, you can try to execute your SQL file with the “Run program” action and the “sqlcmd” command line utility.