I have a need to create insert statement where more that 500 columns playing vital role.
My Current approach is-
DECLARE @InsertString VARCHAR(max);
SET @InsertString = 'Insert into IP_TEST (Date_Created, and so on till 500+ columns .....values (';
''''+ case when a.Date_Created is null then '' else Replace( a.Date_Created,'''', '''''') end + ''''+ ','+
''''+ case when a.Universal_ID is null then '' else Replace( a.Universal_ID,'''', '''''') end + ''''+ ','+
''''+ case when TestAppAssessComments is null then '' else Replace( TestAppAssessComments,'''', '''''') end + ''''+ ');'
With following above approach not able to get insert statement for more that 500 columns.
Is there any other suggested approach where i can get all insert statement in one stroke.
Currently i am getting below error-
The query processor ran out of stack space during query optimization. Please simplify the query.
Please suggest, many thanks.!
- Edited by Maggy111 Tuesday, October 22, 2013 7:49 AM
is Date_Created a date datatype and Universal_ID a numeric? If so, why do you handle them as a string?
Why as dynamic SQL instead of a direct INSERT statement?
You could use sp_executeSQL for yor dyn SQL together with parameter, that would make it easier.
Olaf Helper[ Blog] [ Xing] [ MVP]
I guess CURSOR + procedure + Pivot would be a considerable solution for your requirement.
Iterate original table by a cursor and pivot one row in each iteration then iterate that pivoted set, pass each row of this set into a procedure to handle process like " case ... when ...".
Please mark this as answer if it helps with this issue!
Have you tried using Generated Script task to create the DML?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence