Reading and executing SQL from a file

1

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)
image

1 Like
2

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’))
execute (@SQLSTATEMENT);

Does that work too?

brest regards,

Adrian

3

Hi Adrian,

Yes, you can try to use a similar approach.

4

ok, i got it :grin:

Anmerkung%20(1)
Anmerkung (1).png910×1021 182 KB

2 Likes
5

Hi,

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]
GO
CREATE PROCEDURE [dbo].[sp1]
AS
select getdate() as getdate1
GO

DROP Procedure if exists [dbo].[sp2]
GO
CREATE PROCEDURE [dbo].[sp2]
AS
select getdate() as getdate2
GO

Any help would be awesome, thanks for reading.

6

Hi @EasyMorphNewbie,

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.