CODEWITHSUNDEEP
sql-server
rdvij
rdvij

Reputation: 45

Executing large SQL Queries

I am trying to execute a dynamically created query using FORMATMESSAGE SQL function, and when I do that I get an error

Incorrect syntax near '.'

which I am assuming is because of the trimmed SQL query created.

set @sql = FORMATMESSAGE('insert into %s (imported_on, source_filename, md5_hash %s) select GETDATE(), ''%s'', ''%s'' %s from %s',
                  @target_tablename, @columns, @source_filename, 'TODO', @columns, @source_table);
exec (@sql);

There is a long list of columns that @columns variable holds ~ 300 (columns) and length goes beyond 4000. Variables @columns and @sql both are of type nvarchar(max).

I have seen other posts that recommend splitting to multiple variables, but also mention nvarchar(max) should work. I am clueless on how to proceed if I was not to split the string.

Thanks

Upvotes: 0

Views: 309

Answers (1)

Squirrel
Squirrel

Reputation: 24763

Perform the string concatenation yourself.

declare @sql nvarchar(max);

set @sql = convert(nvarchar(max), '')
         + 'insert into ' + @target_tablename + '(imported_on, ....
exec (@sql);

Upvotes: 1

Related Questions