SQL Server Dynamic SQL Query

Question

I have a text file with about 400,000 records which have to be read, processed and inserted into a table. I'm using a stored procedure to do the same. The records are pipe separated as shown below

a | b | c .... 180 columns (1st record)
d | e | f .... 180 columns (2nd record)
.
.
.
x | y | z .....180 columns (4,00,00th record)

In the stored procedure, one insert statement was being fired for each record. I created a dynamic SQL query that would club 1,000 records in one insert but noticed that the execution time did not decrease. In fact, the SQL dynamic query created for a single record (includes isnull and cast functions for each column) takes more time than than the time taken to insert a single record into the table.

Is there a way I reduce the time taken to perform the task at hand?

EDIT The dynamic SQL query looks something like this (just a tiny snapshot)

CAST(GETDATE() AS VARCHAR(20)) + ''',' + 
CAST(@N_ER AS VARCHAR(20)) + ',' + 
CAST(@N_INSDE AS VARCHAR(20)) + ',' + 
CAST(@N_CODE AS VARCHAR(20)) + ',' + 
CAST(@NJOB_NUMBER AS VARCHAR(30)) + ',' + 
CAST(@NNUMBER AS VARCHAR(30)) + ',''' + 
ISNULL(DESTINATION,'') + ''',''' +  
ISNULL(@VPE_ID,'') + ''',''' + 
ISNULL(dbo.fn_NEW_CARD(@VAN),'') +

Or is there a way to improve the concatenation using some other set of functions maybe?

I'm not sure it can handle so many columns but have you tried the import wizard or SSIS? You could bulk insert into a stage table then use SQL to clean data. — EzLo
– EzLo, Commented Mar 28, 2018 at 9:02
@jarlh I haven't explicitly used the commit statement in my code. When the insert happens I guess it is committed. — Amit
– Amit, Commented Mar 28, 2018 at 9:02
@EzequielLópezPetrucci Once I bulk insert into a stage table, I'll have to take them in a cursor, do the processing and again insert them into the table. Or maybe fire an update query once the processing for a row is complete. But will it reduce execution time substantially? — Amit
– Amit, Commented Mar 28, 2018 at 9:15
I meant insert the data into the stage with the columns already separated. You can tell the wizard that the column delimiter is |, so you will just need to clean the column values and not identify each column. — EzLo
– EzLo, Commented Mar 28, 2018 at 9:28

john McTighe · Accepted Answer · 2018-03-28 14:28:34Z

1

Instead of using EXEC to run your Dynamic SQL - have you tried ExecuteSQL (with parameters) The advantage is that SQL can cache the query plan - which is a fairly significant saving on 400K inserts.

To be honest - SSIS is by far the best way to do it - Right click on the DB, select Tasks and Import Data then follow the wizard - you can even save the created package for later use in a Job.

answered Mar 28, 2018 at 14:28

john McTighe

1,2817 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyron78 Over a year ago

Yes, importing might do - or use VS to build a new package (OK, OK - not that easy on first try - but it IS manageable.) ;-)

john McTighe Over a year ago

just spotted dbo.fn_NEW_CARD() in the code - Functions like this are always a red-flag - I'd look at what this func is doing and try and inline it into your SQL - possibly into a temp table first...

Amit Over a year ago

@johnMcTighe Thank you very much for the useful suggestions.

Collectives™ on Stack Overflow

SQL Server Dynamic SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related