Reputation: 245
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
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