0

I want to update database from a .sql file which contain some commands like update and edit and delete.

I try hard to pass the .sql file to the procedure to make it run.

This is my code which only shows me the file instead of running it:

DECLARE @filePath nvarchar(1000)
DECLARE @command nvarchar(1000)
DECLARE @query nvarchar(1000)

SET @filePath = 'd:\amz\update_price001.sql'
SET @command = '(SELECT * FROM OPENROWSET(BULK '''+@filePath+''', SINGLE_CLOB) AS Contents)'

SELECT @command
SET @query = @command

EXEC sp_executesql @query

This is the result of the select @command:

(SELECT * FROM OPENROWSET(BULK 'd:\amz\update_price001.sql', SINGLE_CLOB) AS Contents)

but the result of the EXEC sp_executesql @query is the filecontent - not the execution result.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AlMounkez
  • 67
  • 3
  • The last command is just taking the SQL in @command and executing it, giving the contents of the file - as you say. You need to add a further step which takes this result and then executes it again - to get what you want. It's made a bit tricky as you'd probably need to load this into a table and execute each command one at a time. – HSS Dec 19 '22 at 08:50

0 Answers0