Wayne E. Pfeffer
Wayne E. Pfeffer

Reputation: 245

Query Optimization -- I have a query that needs a 2nd set of eyes

I have inherited a query that seems to be a bit of a mess, or at least when I look at it I think that there must be a better way of doing it. The query:

select distinct  'INSERT INTO table1 SELECT '''+ACCT_NUM +''',* FROM table2 where library=''' + isnull(library,'') + '''' +
CASE
    when CO is not NULL then ' and CO=''' +CO + ''''
    else isnull(CO,'')
END +
CASE
    when ACCTNO is not NULL then  CASE WHEN LEN(acctNO)>5 then ' and ACCTNO=''' +ACCTNO + '''' else ' and ACCTNO like ''' +rtrim(ACCTNO) +'%'+ '''' END
    else isnull(ACCTNO,'')
END +
CASE
    when FILEDN is not NULL then ' and coalesce(nullif(PRMSTE,''''),ACCSTE)=''' +FILEDN + ''''
    else isnull(FILEDN,'')
END +
CASE
    when LOCNUM is not NULL then ' and LOCNUM=''' +LOCNUM + ''''
    else isnull(LOCNUM,'')
END MySQL
INTO #temp
from table3

It generates a table of INSERT statements that are then looped through and executed via sp_executesql. Any help would be appreciated. I've been looking at this query now for over 2 months and I just can't seem to wrap my head around a better way of doing it.

Specifically I would like to see this taken down to one 'INSERT' statement or a 'SELECT ... INTO ...'

Upvotes: 0

Views: 31

Answers (1)

blorkfish
blorkfish

Reputation: 22824

I think you need to completely throw this sql away and start from scratch.
Generating a table of insert statements and then executing them - really ?
Very very bad idea.

Insert the VALUES you need into a temporary table, not INSERT statements.

I would suggest doing some research on temporary tables - have a look at this article as a start: http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server

Upvotes: 2

Related Questions