Need help with dynamic sql query

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 3:05 pm

  #1591762

  Yup its just inserting a new row. I can't change the structure of the tables because I don't have the 'authority' to do so. I am just to implement the code for insertion deletion and updates.

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 3:10 pm

  #1591767

  declare @SQL varchar(max)

  --First is the updates

  select @SQL = stuff ((

  select

  'Update dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED*' then 'Education'

  when ERRORMESSAGE LIKE 'O14*' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL*' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL*' then 'HH_ALL'

  end

  + ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

  + ''' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

  from test

  where Command = 'UPDATE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  --Now we append the deletes

  select @SQL = @SQL + stuff ((

  select

  'DELETE dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED*' then 'Education'

  when ERRORMESSAGE LIKE 'O14*' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL*' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL*' then 'HH_ALL'

  end

  + ' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

  from test

  where Command = 'UPDATE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  select @SQL

  --exec sp_execute @SQL

  I modified your code a little bit because the errormessage would be long like this:

  O14 ERROR 2_3: The person cluster variable( 39) does not match that on the HH record ( 49)

  So, I had to change it to like; but when I do this it says :

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  NULL

  (1 row(s) affected)

  How can it be only 1 row and it should be multiple rows affected?

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  February 27, 2013 at 3:26 pm

  #1591772

  You have the wrong wildcard in your LIKE. The asterisk is from Access. SQL Server use percent.

  when ERRORMESSAGE LIKE 'ED%' then 'Education'

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  February 27, 2013 at 3:27 pm

  #1591773

  jancas08 (2/27/2013)

  ---

  Yup its just inserting a new row. I can't change the structure of the tables because I don't have the 'authority' to do so. I am just to implement the code for insertion deletion and updates.

  I feel your pain there. Sadly sometimes we are left to plug our nose and plod ahead dealing the garbage somebody else left behind. 😛

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 3:32 pm

  #1591776

  Thanks, I know right. Sigh.....Okay, I have to bother you one more time.

  It outputs this:

  [p]

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Update dbo.Education set ED_THREE = '1' where zBarcode = 15394 and PERSON_NUMBER = 3;Update dbo.Education set ED_THREE = '2' where zBarcode = 567 and PERSON_NUMBER = 1;Update dbo.Education set ED_THREE = '3' where zBarcode = 567 and PERSON_NUMBER = 2;Updat

  (1 row(s) affected)

  [/p]

  I can't see the rest of commands and i left @sql intialized to max

• jshahan

  SSCarpal Tunnel

  Points: 4622

  More actions

  February 27, 2013 at 3:50 pm

  #1591783

  deleted for lack of relevance...

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 3:52 pm

  #1591784

  Okay this is what I have done. An update of everything. I am still not able see the entirety of the command it cuts off the screen.

  declare @SQL varchar(max)

  --First is the updates

  select @SQL = stuff ((

  select

  'Update dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

  end

  + ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

  + ''' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

  from test

  where Command = 'UPDATE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  select @SQL

  --Now we append the deletes

  select @SQL = @SQL + stuff ((

  select

  'DELETE dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

  end

  + ' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

  from test

  where Command = 'DELETE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  --Now we append the deletes

  select @SQL = @SQL + stuff ((

  select

  'INSERT INTO dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education' +

  +'VALUES('+

  coalesce(CAST(ED_THREE AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Four AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Five AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Six AS varchar(10)),'99999')+ ','

  + coalesce(CAST(Person_No AS varchar(7)),'99999') + ','

  + coalesce(CAST(zBarcode AS varchar(15)),'99999')+ ')'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL' +

  'VALUES('+

  coalesce(CAST(Batch_No AS varchar(7)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , ' + coalesce(CAST(HHBarcode AS varchar(7)),'99999')

  + ' , ''' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(District AS varchar(7)),'99999') + ' , '+ coalesce(CAST(URBAN_RURAL AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Cluster AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(HHNUM AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_5 AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_5_KNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_7 AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_8 AS varchar(7)),'99999') + ''' , '+

  coalesce(CAST(Q1_9 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_9_2 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10b AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_10c AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10_Total AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_11 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_12 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_13 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_14 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_15 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_15a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_16 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_17 AS varchar(7)),'99999') + ''' , ' +coalesce(CAST(Q1_18 AS varchar(7)),'99999') + ' , '+

  coalesce(CAST(Q1_19 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_20 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_21 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_22 AS varchar(7)),'99999')+ ''' , '''

  + coalesce(CAST(Q1_23 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_24 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_25 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_26 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_27 AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_28_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_PREVIOUS AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_29_SEASONAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_29_YEARROUND AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_30_MAIN AS varchar(7)),'99999') +''' , ''' +

  coalesce(CAST(Q1_30_OTHER AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_30_PREVIOUS AS varchar(7)),'99999')+ ''' , ''' + coalesce(CAST(Q1_31_MAIN AS varchar(7)),'99999') + ''' , ''' +

  coalesce(CAST(Q1_31_OTHER AS varchar(7)),'99999') + ''' , '''+ coalesce(CAST(Q1_31_PREVIOUS AS varchar(7)),'99999')+ ''' , ' + coalesce(CAST(Q1_32_MAIN AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_32_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_32_TOTAL AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_33_MAIN AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_33_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_TOTAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_35 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_37 AS varchar(7)),'99999') + ' , '

  + coalesce(CAST(Q1_38 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_39 AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_40 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_41 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_41_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b_DKNS AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_42 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_42_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_43 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_44 AS varchar(7)),'99999') + ')'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'+ 'VALUES('''+

  coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(HL_Three AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_Four AS varchar (10)),'99999')

  + ' , ' + coalesce(CAST(HL_Five AS varchar(10)),'99999') + ' , '+ coalesce(CAST(HL_Six AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(HL_Seven AS varchar (10)),'99999')

  + ' , ' + coalesce(CAST(HL_SevenB AS varchar (10)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' )'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'+ 'VALUES('+

  coalesce(CAST(BATCH_NO AS varchar(8)),'99999') + ' , ' + coalesce(CAST(Cluster AS varchar (10)),'99999') + ' , '

  + coalesce(CAST(HHNUM AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , '

  + coalesce(CAST(District AS varchar(10)),'99999')+ ' , ' +

  coalesce(CAST(URBAN_RURAL AS varchar(10)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(10)),'99999')

  + ' , ' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ' )'

  end

  + ';' as MyVal

  from test

  where Command = 'INSERT'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  select @SQL

  --exec sp_execute @SQL

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 4:01 pm

  #1591789

  Lange I got it.....I got it....here is the final....

  declare @SQL varchar(max)

  --First is the updates

  select @SQL = stuff ((

  select

  'Update dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

  end

  + ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

  + ''' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal

  from test

  where Command = 'UPDATE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  --select @SQL

  --Now we append the deletes

  select @SQL = @SQL + stuff ((

  select

  'DELETE dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

  end

  + ' where zBarcode = ' + cast(zBarcode as varchar(20))

  + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal

  from test

  where Command = 'DELETE'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  --Now we append the deletes

  select @SQL = @SQL + stuff ((

  select

  'INSERT INTO dbo.' +

  case

  when ERRORMESSAGE LIKE 'ED%' then 'Education' +

  +' VALUES('+

  coalesce(CAST(ED_THREE AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Four AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Five AS varchar(10)),'99999') + ','

  + coalesce(CAST(ED_Six AS varchar(10)),'99999')+ ','

  + coalesce(CAST(Person_No AS varchar(7)),'99999') + ','

  + coalesce(CAST(zBarcode AS varchar(15)),'99999')+ ')'

  when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL' +

  ' VALUES('+

  coalesce(CAST(Batch_No AS varchar(7)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , ' + coalesce(CAST(HHBarcode AS varchar(7)),'99999')

  + ' , ''' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(District AS varchar(7)),'99999') + ' , '+ coalesce(CAST(URBAN_RURAL AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Cluster AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(HHNUM AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_5 AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_5_KNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_7 AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_8 AS varchar(7)),'99999') + ''' , '+

  coalesce(CAST(Q1_9 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_9_2 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10b AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_10c AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10_Total AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_11 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_12 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_13 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_14 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_15 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_15a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_16 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_17 AS varchar(7)),'99999') + ''' , ' +coalesce(CAST(Q1_18 AS varchar(7)),'99999') + ' , '+

  coalesce(CAST(Q1_19 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_20 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_21 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_22 AS varchar(7)),'99999')+ ''' , '''

  + coalesce(CAST(Q1_23 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_24 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_25 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_26 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_27 AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_28_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_PREVIOUS AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_29_SEASONAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_29_YEARROUND AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_30_MAIN AS varchar(7)),'99999') +''' , ''' +

  coalesce(CAST(Q1_30_OTHER AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_30_PREVIOUS AS varchar(7)),'99999')+ ''' , ''' + coalesce(CAST(Q1_31_MAIN AS varchar(7)),'99999') + ''' , ''' +

  coalesce(CAST(Q1_31_OTHER AS varchar(7)),'99999') + ''' , '''+ coalesce(CAST(Q1_31_PREVIOUS AS varchar(7)),'99999')+ ''' , ' + coalesce(CAST(Q1_32_MAIN AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_32_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_32_TOTAL AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_33_MAIN AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_33_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_TOTAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_35 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_37 AS varchar(7)),'99999') + ' , '

  + coalesce(CAST(Q1_38 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_39 AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_40 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_41 AS varchar(7)),'99999') + ' , ' +

  coalesce(CAST(Q1_41_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b_DKNS AS varchar(7)),'99999')

  + ' , ' + coalesce(CAST(Q1_42 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_42_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_43 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_44 AS varchar(7)),'99999') + ')'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'+ ' VALUES('''+

  coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(HL_Three AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_Four AS varchar (10)),'99999')

  + ' , ' + coalesce(CAST(HL_Five AS varchar(10)),'99999') + ' , '+ coalesce(CAST(HL_Six AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(HL_Seven AS varchar (10)),'99999')

  + ' , ' + coalesce(CAST(HL_SevenB AS varchar (10)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' )'

  when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'+ ' VALUES('+

  coalesce(CAST(BATCH_NO AS varchar(8)),'99999') + ' , ' + coalesce(CAST(Cluster AS varchar (10)),'99999') + ' , '

  + coalesce(CAST(HHNUM AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , '

  + coalesce(CAST(District AS varchar(10)),'99999')+ ' , ' +

  coalesce(CAST(URBAN_RURAL AS varchar(10)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(10)),'99999')

  + ' , ' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ' )'

  end

  + ';' + CHAR(13) as MyVal

  from test

  where Command = 'INSERT'

  for XML PATH(''), type

  ).value('.','varchar(max)'), 1, 0, '')

  print @SQL

  --select @SQL

  --exec sp_execute @SQL

  it outputs this:

  Update dbo.Education set ED_THREE = '1' where zBarcode = 15394 and PERSON_NUMBER = 3;

  Update dbo.Education set ED_THREE = '2' where zBarcode = 567 and PERSON_NUMBER = 1;

  Update dbo.Education set ED_THREE = '3' where zBarcode = 567 and PERSON_NUMBER = 2;

  Update dbo.HH_LISTING set HL_FOUR = '3' where zBarcode = 21555 and PERSON_NUMBER = 3;

  Update dbo.HH_LISTING set HL_FOUR = '3' where zBarcode = 21555 and PERSON_NUMBER = 3;

  Update dbo.FOURTEEN_ALL set Q1_42_DKNS = '63' where zBarcode = 51415 and PERSON_NUMBER = 2;

  Update dbo.FOURTEEN_ALL set Q1_42_DKNS = '67' where zBarcode = 50913 and PERSON_NUMBER = 2;

  DELETE dbo.Education where zBarcode = 20725 and PERSON_NUMBER = 4;

  DELETE dbo.Education where zBarcode = 20725 and PERSON_NUMBER = 5;

  DELETE dbo.Education where zBarcode = 20725 and PERSON_NUMBER = 6;

  DELETE dbo.HH_LISTING where zBarcode = 21098 and PERSON_NUMBER = 3;

  DELETE dbo.HH_LISTING where zBarcode = 24723 and PERSON_NUMBER = 1;

  DELETE dbo.FOURTEEN_ALL where zBarcode = 3667 and PERSON_NUMBER = 1;

  DELETE dbo.FOURTEEN_ALL where zBarcode = 3674 and PERSON_NUMBER = 1;

  INSERT INTO dbo.Education VALUES(2,3,4,6,2,4398);

  INSERT INTO dbo.Education VALUES(2,3,7,5,3,4237);

  INSERT INTO dbo.HH_LISTING VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 );

  INSERT INTO dbo.HH_LISTING VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 );

  INSERT INTO dbo.FOURTEEN_ALL VALUES(36122 , 21593 , 3 , '1' , 4 , 4 , 7 , 9 , 3 , 4 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5);

  INSERT INTO dbo.FOURTEEN_ALL VALUES(36124 , 22019 , 2 , '1' , 2 , 4 , 7 , 9 , 4 , 6 , 4 , 6 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5);

  INSERT INTO dbo.Education VALUES(99999,99999,99999,99999,5,78798);

  Thanks so much.....:-D

• Jeff Moden

  SSC Guru

  Points: 1003869

  More actions

  February 27, 2013 at 8:17 pm

  #1591823

  I dunno... seems to me that if it's iimportant enough to go through all of this to get the printing right, then instaqd of display it on the screen, it should be saved in a log table.

  And +10,000 to Sean for seeing this one through.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jancas08

  SSC Veteran

  Points: 266

  More actions

  February 27, 2013 at 8:41 pm

  #1591828

  I know right Lange helped me every step of the way.thanks for the tip about the log file will have to ask my boss if he wants that.

  A big thanks to all of you that have helped me

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  February 28, 2013 at 7:19 am

  #1591974

  Glad you got it sorted out.

  And thanks Jeff for the extra points. 😉

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/