February 25, 2013 at 2:14 pm
Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when i run it:
UPDATE [janinetestdata].[dbo].[EDTE] set ED_THREE = CORRECTED VALUE WHERE zBarcode = 3636 and PERSON_NUMBER = 3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VALUE'.
I printed the statement to check if was an error there, but it printed just fine. I am new to this dynamic query; I have been trying for a few days and can't get it to work. Basically the gist of this program is to check if the command is
UPDATE
then it should set the
ColumnTo Fix
with the value of the
CorrectedValue
.
Below is my code. Any assistance would be greatly appreciated.
USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/
--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT nvarchar(900)
DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX
FROM [janinetestdata].[dbo].[TEST66]
OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six
WHILE @@FETCH_STATUS = 0
BEGIN
/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = '
+ CAST(@CorrectedValue AS nVarchar(15)) + ' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and '
+ 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7))
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Education Table*/
END/*end of Fourteen and Over Commands*/
--execute sp_executesql @CORRECTION_STATEMENT
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six
END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO
February 25, 2013 at 2:39 pm
jancas08 (2/25/2013)
Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when i run it:UPDATE [janinetestdata].[dbo].[EDTE] set ED_THREE = CORRECTED VALUE WHERE zBarcode = 3636 and PERSON_NUMBER = 3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VALUE'.
It is hard to help with this without table structure and some sample data but here is a quick takeaway from what you posted.
in your updates statement quoted above you have the words "CORRECTED VALUE". that is where the incorrect syntax is coming from. If "CORRECTED VALUE" is the string that you are trying to update ED_THREE to then possible changing you query to this might help. note the extra quotes around + CAST(@CorrectedValue AS nVarchar(15)) +
USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/
--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT nvarchar(900)
DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX
FROM [janinetestdata].[dbo].[TEST66]
OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six
WHILE @@FETCH_STATUS = 0
BEGIN
/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = '''
+ CAST(@CorrectedValue AS nVarchar(15)) + ''' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and '
+ 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7))
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Education Table*/
END/*end of Fourteen and Over Commands*/
--execute sp_executesql @CORRECTION_STATEMENT
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six
END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO
February 25, 2013 at 2:45 pm
Thanks, for your help. It works....
February 25, 2013 at 3:33 pm
You could get rid of the cursor here too. Something like this should be pretty close.
DECLARE @CORRECTION_STATEMENT nvarchar(max)
select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + Columns_To_Fix + ' = '''
+ Corrected_Value + ''' WHERE zBarcode = ' + zBarcode + ' and '
+ 'PERSON_NUMBER = ' + Person_No + ';'
from [janinetestdata].[dbo].[TEST66]
where Command = 'UPDATE'
and Person_No is not null
and Columns_To_Fix is not null
and zBarcode is not null
exec sp_executesql @CORRECTION_STATEMENT
_______________________________________________________________
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/
February 26, 2013 at 7:44 am
Thanks, I will try that now.
February 26, 2013 at 12:36 pm
I tried your way Lange; the only thing is that I can't view the selected data. For example, say the query ends up with 9 rows of data but only row would show because print can only show one row. But it does the execution behind the scenes. Thanks tho.
February 27, 2013 at 8:02 am
jancas08 (2/26/2013)
I tried your way Lange; the only thing is that I can't view the selected data. For example, say the query ends up with 9 rows of data but only row would show because print can only show one row. But it does the execution behind the scenes. Thanks tho.
Not quite sure what you mean here.
_______________________________________________________________
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/
February 27, 2013 at 9:40 am
For example, the select statements finds 10 rows that contain 'UPDATE', it would do the sql execution on the 10 rows that contain 'UPDATE'. But, the use of printing the statement will only show the last row that contain 'UPDATE' because print can't multiple rows in a single instance.
February 27, 2013 at 10:04 am
jancas08 (2/27/2013)
For example, the select statements finds 10 rows that contain 'UPDATE', it would do the sql execution on the 10 rows that contain 'UPDATE'. But, the use of printing the statement will only show the last row that contain 'UPDATE' because print can't multiple rows in a single instance.
Still not really sure what you are saying. There is only 1 row of dynamic sql now, it contains multiple statements. The code I posted did not have a print statement because there is no loop.
print @CORRECTION_STATEMENT
That will show the entire sql that is going to be executed. It will contain multiple statements now instead of one at a time.
_______________________________________________________________
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/
February 27, 2013 at 10:18 am
Okay, but its showing only one though. I don't know why. I will show you the one using cursors versus the select state
Below is the cursors
USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/
--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(5),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT Nvarchar(900), @Cluster varchar(15), @HNUM varchar(15),
@District varchar(25), @URBAN_RURAL varchar(100), @Final_Result_Code varchar(25),
@HL_Three varchar(10), @HL_Four varchar(10), @HL_Five varchar(10), @HL_Six varchar(10),
@HL_Seven varchar(10), @HL_SevenB varchar(10),@HHBarcode varchar (10), @Q1_5 varchar(10), @Q1_5_KNS varchar(10),
@Q1_7 varchar(10), @Q1_8 varchar(10), @Q1_9 varchar(10), @Q1_9_2 varchar(10), @Q1_10a varchar(10),
@Q1_10b varchar(10), @Q1_10c varchar(10), @Q1_10_Total varchar(10), @Q1_11 varchar(10), @Q1_12 varchar(10),
@Q1_13 varchar(10), @Q1_14 varchar(10), @Q1_15 varchar(10),@Q1_15a varchar(10),
@Q1_16 varchar(10),@Q1_17 varchar(10),@Q1_18 varchar(10),@Q1_19 varchar(10),
@Q1_20 varchar(10),@Q1_21 varchar(10),@Q1_22 varchar(50),@Q1_23 varchar(50),
@Q1_24 varchar(10),@Q1_25 varchar(10),@Q1_26 varchar(10),@Q1_27 varchar(10),
@Q1_28_MAIN varchar(10), @Q1_28_OTHER varchar(10), @Q1_28_PREVIOUS varchar(10),
@Q1_29_SEASONAL varchar(10), @Q1_29_YEARROUND varchar(10),@Q1_30_MAIN varchar(10),
@Q1_30_OTHER varchar(10), @Q1_30_PREVIOUS varchar(10), @Q1_31_MAIN varchar(10),
@Q1_31_OTHER varchar(10), @Q1_31_PREVIOUS varchar(10), @Q1_32_MAIN varchar(10),
@Q1_32_OTHER varchar(10), @Q1_32_TOTAL varchar(10),@Q1_33_MAINvarchar(10),
@Q1_33_OTHER varchar(10), @Q1_33_TOTAL varchar(10), @Q1_35 varchar(10), @Q1_37 varchar(10),
@Q1_38 varchar(10),@Q1_39 varchar(10), @Q1_40 varchar(10), @Q1_41 varchar(10), @Q1_41_DKNS varchar(10),
@Q1_41a varchar(10), @Q1_41b varchar(10), @Q1_41b_DKNS varchar(10), @Q1_42 varchar(10),
@Q1_42_DKNS varchar(10), @Q1_43 varchar(10), @Q1_44 varchar(10)
DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX , Cluster, HHNUM , District , URBAN_RURAL, Final_Result_Code,
HL_THREE, HL_FOUR, HL_FIVE, HL_SIX, HL_SEVEN , HL_SEVENB , HHBarcode, Q1_5,
Q1_5_KNS , Q1_7, Q1_8, Q1_9, Q1_9_2, Q1_10a, Q1_10b, Q1_10c, Q1_10_Total, Q1_11,
Q1_12, Q1_13 , Q1_14 , Q1_15, Q1_15a, Q1_16, Q1_17, Q1_18, Q1_19, Q1_20,
Q1_21 , Q1_22, Q1_23 , Q1_24 , Q1_25, Q1_26, Q1_27, Q1_28_MAIN, Q1_28_OTHER,
Q1_28_PREVIOUS, Q1_29_SEASONAL, Q1_29_YEARROUND, Q1_30_MAIN ,Q1_30_OTHER,
Q1_30_PREVIOUS, Q1_31_MAIN, Q1_31_OTHER, Q1_31_PREVIOUS, Q1_32_MAIN , Q1_32_OTHER
,Q1_32_TOTAL, Q1_33_MAIN, Q1_33_OTHER, Q1_33_TOTAL, Q1_35, Q1_37 , Q1_38 , Q1_39
,Q1_40, Q1_41, Q1_41_DKNS,Q1_41a,Q1_41b,Q1_41b_DKNS, Q1_42,Q1_42_DKNS,Q1_43 ,Q1_44
FROM [janinetestdata].[dbo].[TEST]
OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six ,@Cluster , @HNUM ,
@District , @URBAN_RURAL , @Final_Result_Code ,
@HL_Three, @HL_Four, @HL_Five , @HL_Six ,
@HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS ,
@Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a,
@Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 ,
@Q1_13 , @Q1_14 , @Q1_15 ,@Q1_15a,
@Q1_16 ,@Q1_17 ,@Q1_18,@Q1_19,
@Q1_20 ,@Q1_21 ,@Q1_22,@Q1_23,
@Q1_24 ,@Q1_25 ,@Q1_26 ,@Q1_27,
@Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS,
@Q1_29_SEASONAL , @Q1_29_YEARROUND ,@Q1_30_MAIN,
@Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN,
@Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN ,
@Q1_32_OTHER , @Q1_32_TOTAL ,@Q1_33_MAIN,
@Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37,
@Q1_38 ,@Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS ,
@Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42,
@Q1_42_DKNS , @Q1_43 , @Q1_44
WHILE @@FETCH_STATUS = 0
BEGIN
/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(15)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ' and '
+ 'PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Education Table*/
/*Beginning of Insert Command for Education Table*/
else if @Command = 'INSERT' and @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES('+
coalesce(CAST(@ED_THREE AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Four AS nVarchar(10)),'99999') + ','
+ coalesce(CAST(@ED_Five AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Six AS nVarchar(10)),'99999')+ ','
+ coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ',' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ')'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Education Table*/
/*Beginning of Delete Command for Education Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END
END
END/*End of Delete Command for Education Table*/
END
/*End of Education Commands*/
/*Beginning of HH_ALL Commands*/
else if @ErrorMessage LIKE 'HH%'
BEGIN/*Beginning of Update Command for HH_ALL Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'Person_No = ' +coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for HH_ALL Table*/
/*Beginning of Insert Command for HH_ALL Table*/
else if @Command = 'INSERT'and @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES('+
coalesce(CAST(@BatchNo AS nVarchar(8)),'99999') + ' , ' + coalesce(CAST(@Cluster AS nVarchar (10)),'99999') + ' , '
+ coalesce(CAST(@HNUM AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , '
+ coalesce(CAST(@District AS nVarchar(10)),'99999')+ ' , ' +
coalesce(CAST(@URBAN_RURAL AS nVarchar(10)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(10)),'99999')
+ ' , ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ' )'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END /*End of INSERT Command for HH_ALL Table*/
/*Beginning of Delete Command for HH_ALL Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = '
+ coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Delete Command for HH_ALL Table*/
END
END /*End Else*/
END/*end of HH_ALL Commands*/
/*Beginning of Household Listing Commands*/
else if @ErrorMessage LIKE 'HL%'
BEGIN/*Beginning of Update Command for Household Listing Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_LISTING] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Household Listing Table*/
/*Beginning of Insert Command for Household Listing Table*/
else if @Command = 'INSERT'and @Person_Number is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('''+
coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@HL_Three AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HL_Four AS nVarchar (10)),'99999')
+ ' , ' + coalesce(CAST(@HL_Five AS nVarchar(10)),'99999') + ' , '+ coalesce(CAST(@HL_Six AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@HL_Seven AS nVarchar (10)),'99999')
+ ' , ' + coalesce(CAST(@HL_SevenB AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' )'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Household Listing Table*/
/*Beginning of Delete Command for Household Listing Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Delete Command for Household Listing Table*/
END
END/*end else*/
END /*end of Household Listing Commands*/
/*Beginning of Fourteen and Over Commands*/
else if @ErrorMessage LIKE 'O14%'
BEGIN/*Beginning of Update Command for Fourteen and Over Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set@CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Fourteen and Over Table*/
/*Beginning of Insert Command for Fourteen and Over Table*/
else if @Command = 'INSERT'and @Person_Number is not null and @Q1_8 is not null and @Q1_23 is not null and @Q1_22 is not null and
@Q1_30_MAIN is not null and @Q1_30_OTHER is not null and @Q1_30_PREVIOUS is not null and @Q1_31_MAIN is not null and @Q1_31_OTHER is not null
and @Q1_31_PREVIOUS is not null and @Q1_39 is not null and @Q1_40 is not null and @zBarcode is not null
BEGIN
set@CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES('+
coalesce(CAST(@BatchNo AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , ' + coalesce(CAST(@HHBarcode AS nVarchar(7)),'99999')
+ ' , ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@District AS nVarchar(7)),'99999') + ' , '+ coalesce(CAST(@URBAN_RURAL AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Cluster AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@HNUM AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_5 AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_5_KNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_7 AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_8 AS nVarchar(7)),'99999') + ''' , '+
coalesce(CAST(@Q1_9 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_9_2 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10b AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_10c AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10_Total AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_11 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_12 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_13 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_14 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_15 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_15a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_16 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_17 AS nVarchar(7)),'99999') + ''' , ' +coalesce(CAST(@Q1_18 AS nVarchar(7)),'99999') + ' , '+
coalesce(CAST(@Q1_19 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_20 AS nVarchar(7)),'99999') + ' , ' +coalesce(CAST(@Q1_21 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_22 AS nVarchar(7)),'99999')+ ''' , '''
+ coalesce(CAST(@Q1_23 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_24 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_25 AS nVarchar(7)),'99999') + ' , ' +coalesce(CAST(@Q1_26 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_27 AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_28_MAIN AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_PREVIOUS AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_29_SEASONAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_29_YEARROUND AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_30_MAIN AS nVarchar(7)),'99999') +''' , ''' +
coalesce(CAST(@Q1_30_OTHER AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_30_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ''' + coalesce(CAST(@Q1_31_MAIN AS nVarchar(7)),'99999') + ''' , ''' +
coalesce(CAST(@Q1_31_OTHER AS nVarchar(7)),'99999') + ''' , '''+ coalesce(CAST(@Q1_31_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ' + coalesce(CAST(@Q1_32_MAIN AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_32_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_32_TOTAL AS nVarchar(7)),'99999') + ' , ' +coalesce(CAST(@Q1_33_MAIN AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_33_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_TOTAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_35 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_37 AS nVarchar(7)),'99999') + ' , '
+ coalesce(CAST(@Q1_38 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_39 AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_40 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_41 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_41_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b_DKNS AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_42 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_42_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_43 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_44 AS nVarchar(7)),'99999') + ')'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Fourteen and Over Table*/
/*Beginning of Delete Command for Fourteen and Over Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set@CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Delete Command for Fourteen and Over Table*/
END/*end else*/
END
END/*end of Fourteen and Over Commands*/
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six ,
@Cluster , @HNUM ,
@District , @URBAN_RURAL , @Final_Result_Code ,
@HL_Three, @HL_Four, @HL_Five , @HL_Six ,
@HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS ,
@Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a,
@Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 ,
@Q1_13 , @Q1_14 , @Q1_15 ,@Q1_15a,
@Q1_16 ,@Q1_17 ,@Q1_18,@Q1_19,
@Q1_20 ,@Q1_21 ,@Q1_22,@Q1_23,
@Q1_24 ,@Q1_25 ,@Q1_26 ,@Q1_27,
@Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS,
@Q1_29_SEASONAL , @Q1_29_YEARROUND ,@Q1_30_MAIN,
@Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN,
@Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN ,
@Q1_32_OTHER , @Q1_32_TOTAL ,@Q1_33_MAIN,
@Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37,
@Q1_38 ,@Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS ,
@Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42,
@Q1_42_DKNS , @Q1_43 , @Q1_44
END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO
It prints out
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 1 WHERE zBarcode = 15394 and PERSON_NUMBER = 3
(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 2 WHERE zBarcode = 567 and PERSON_NUMBER = 1
(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2
(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,4,6,2,4398)
(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 4
(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 5
(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6
(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15110 and Person_No = 1
(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 20657 and Person_No = 1
(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1
(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36144 , 2 , 3 , 21227 , 5 , 6 , 8 , 1 )
(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )
(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'
(3 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'
(3 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )
(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 21098 and PERSON_NUMBER = '3'
(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'
(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 63 WHERE zBarcode = 51415 and Person_No = '2'
(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'
(1 row(s) affected)
INSERT INTO [janinetestdata].[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)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
(1 row(s) affected)
INSERT INTO [janinetestdata].[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)
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3667 and Person_No = '1'
(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'
(0 row(s) affected)
The reason some of them said 0 rows affected because the data had been already deleted; I have ran the program like 10 times.
Below is the one using your syntax
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/
--Variables declaration section
DECLARE @CORRECTION_STATEMENT nvarchar(max)
/*Beginning of Update Command for Education Table*/
select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set '
+ coalesce(CAST(Columns_To_Fix AS Varchar(50)),'99999')+ ' = '
+ coalesce(CAST(Corrected_Value AS Varchar(15)),'99999')+
' WHERE zBarcode = ' + coalesce(CAST(zBarcode AS Varchar(15)),'99999')+ ' and '
+ 'PERSON_NUMBER = ' + coalesce(CAST(Person_No AS Varchar(7)),'99999')
from [janinetestdata].[dbo].[TEST]
where Command = 'UPDATE'
and Person_No is not null
and Columns_To_Fix is not null
and zBarcode is not null
and ERRORMESSAGE LIKE 'ED%'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2
(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6
(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2
(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1
(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = 3
(3 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'
(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'
(1 row(s) affected)
INSERT INTO [janinetestdata].[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)
(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'
(0 row(s) affected)
As you can see the results, differ; your syntax only outputs the last result of the select statement.
February 27, 2013 at 10:55 am
The code you posted has changed significantly from the previous version. Your original code had only 1 statement inside a cursor. The recent code you posted has lots more logic in it. The code I posted is only going to handle your first statement. I would bet this process is pretty slow if you have much data in these tables.
I would be willing to help you but you need to first help me. I need to have ddl and sample data along with the business rules of what you are trying to do here. Please take a few minutes to read the article found at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
February 27, 2013 at 10:59 am
Im new to sql. Where can I find the ddl?
February 27, 2013 at 11:13 am
jancas08 (2/27/2013)
Im new to sql. Where can I find the ddl?
Right click the table(s) or other objects in SSMS. The script as --> To new window.
What I am talking about here is the script to generate your table(s) in my system so I can work. Does that help?
_______________________________________________________________
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/
February 27, 2013 at 12:09 pm
USE [janinetestdata]
GO
/****** Object: Table [dbo].[EDUCATION] Script Date: 02/27/2013 13:05:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EDUCATION](
[ED_THREE] [float] NULL,
[ED_FOUR] [float] NULL,
[ED_FIVE] [float] NULL,
[ED_SIX] [float] NULL,
[PERSON_NUMBER] [float] NULL,
[zBarcode] [float] NULL
) ON [PRIMARY]
GO
USE [janinetestdata]
GO
/****** Object: Table [dbo].[FOURTEEN_ALL] Script Date: 02/27/2013 13:05:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FOURTEEN_ALL](
[BatchNo] [float] NULL,
[zBarcode] [float] NULL,
[HHBarcode] [float] NULL,
[Person_No] [float] NULL,
[District] [float] NULL,
[URBAN_RURAL] [float] NULL,
[Cluster] [float] NULL,
[HHNUM] [float] NULL,
[Final_Result_Code] [float] NULL,
[Q1_5] [float] NULL,
[Q1_5_DKNS] [float] NULL,
[Q1_7] [float] NULL,
[Q1_8] [varchar](42) NULL,
[Q1_9] [float] NULL,
[Q1_9_2] [float] NULL,
[Q1_10a] [float] NULL,
[Q1_10b] [float] NULL,
[Q1_10c] [float] NULL,
[Q1_10_Total] [float] NULL,
[Q1_11] [float] NULL,
[Q1_12] [float] NULL,
[Q1_13] [float] NULL,
[Q1_14] [float] NULL,
[Q1_15] [float] NULL,
[Q1_15a] [float] NULL,
[Q1_16] [float] NULL,
[Q1_17] [varchar](70) NULL,
[Q1_18] [float] NULL,
[Q1_19] [float] NULL,
[Q1_20] [float] NULL,
[Q1_21] [float] NULL,
[Q1_22] [varchar](30) NULL,
[Q1_23] [varchar](30) NULL,
[Q1_24] [float] NULL,
[Q1_25] [float] NULL,
[Q1_26] [float] NULL,
[Q1_27] [float] NULL,
[Q1_28_MAIN] [float] NULL,
[Q1_28_OTHER] [float] NULL,
[Q1_28_PREVIOUS] [float] NULL,
[Q1_29_SEASONAL] [float] NULL,
[Q1_29_YEARROUND] [float] NULL,
[Q1_30_MAIN] [varchar](2) NULL,
[Q1_30_OTHER] [varchar](2) NULL,
[Q1_30_PREVIOUS] [varchar](2) NULL,
[Q1_31_MAIN] [varchar](2) NULL,
[Q1_31_OTHER] [varchar](2) NULL,
[Q1_31_PREVIOUS] [varchar](2) NULL,
[Q1_32_MAIN] [float] NULL,
[Q1_32_OTHER] [float] NULL,
[Q1_32_TOTAL] [float] NULL,
[Q1_33_MAIN] [float] NULL,
[Q1_33_OTHER] [float] NULL,
[Q1_33_TOTAL] [float] NULL,
[Q1_35] [float] NULL,
[Q1_37] [float] NULL,
[Q1_38] [float] NULL,
[Q1_39] [varchar](1) NULL,
[Q1_40] [varchar](1) NULL,
[Q1_41] [float] NULL,
[Q1_41_DKNS] [float] NULL,
[Q1_41a] [float] NULL,
[Q1_41b] [float] NULL,
[Q1_41b_DKNS] [float] NULL,
[Q1_42] [float] NULL,
[Q1_42_DKNS] [float] NULL,
[Q1_43] [float] NULL,
[Q1_44] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [janinetestdata]
GO
/****** Object: Table [dbo].[HH_ALL] Script Date: 02/27/2013 13:06:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HH_ALL](
[BatchNo] [float] NULL,
[Cluster] [float] NULL,
[HHNUM] [float] NULL,
[zBarcode] [float] NULL,
[District] [float] NULL,
[URBAN_RURAL] [float] NULL,
[Final_Result_Code] [float] NULL,
[Person_No] [float] NULL
) ON [PRIMARY]
GO
USE [janinetestdata]
GO
/****** Object: Table [dbo].[HH_LISTING] Script Date: 02/27/2013 13:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HH_LISTING](
[PERSON_NUMBER] [varchar](2) NULL,
[HL_THREE] [float] NULL,
[HL_FOUR] [float] NULL,
[HL_FIVE] [float] NULL,
[HL_SIX] [float] NULL,
[HL_SEVEN] [float] NULL,
[HL_SEVENB] [float] NULL,
[zBarcode] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [janinetestdata]
GO
/****** Object: Table [dbo].[TEST] Script Date: 02/27/2013 13:08:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST](
[ERRORMESSAGE] [varchar](196) NULL,
[BATCH_NO] [float] NULL,
[zBarcode] [float] NULL,
[Incorrect_Value] [float] NULL,
[Columns_To_Fix] [varchar](17) NULL,
[Person_No] [float] NULL,
[Corrected_Value] [varchar](15) NULL,
[Err_Description] [varchar](302) NULL,
[Command] [varchar](29) NULL,
[ED_THREE] [float] NULL,
[ED_FOUR] [float] NULL,
[ED_FIVE] [float] NULL,
[ED_SIX] [float] NULL,
[Cluster] [float] NULL,
[HHNUM] [float] NULL,
[District] [float] NULL,
[URBAN_RURAL] [float] NULL,
[Final_Result_Code] [float] NULL,
[HL_THREE] [float] NULL,
[HL_FOUR] [float] NULL,
[HL_FIVE] [float] NULL,
[HL_SIX] [float] NULL,
[HL_SEVEN] [float] NULL,
[HL_SEVENB] [float] NULL,
[HHBarcode] [float] NULL,
[Q1_5] [float] NULL,
[Q1_5_KNS] [float] NULL,
[Q1_7] [float] NULL,
[Q1_8] [float] NULL,
[Q1_9] [float] NULL,
[Q1_9_2] [float] NULL,
[Q1_10a] [float] NULL,
[Q1_10b] [float] NULL,
[Q1_10c] [float] NULL,
[Q1_10_Total] [float] NULL,
[Q1_11] [float] NULL,
[Q1_12] [float] NULL,
[Q1_13] [float] NULL,
[Q1_14] [float] NULL,
[Q1_15] [float] NULL,
[Q1_15a] [float] NULL,
[Q1_16] [float] NULL,
[Q1_17] [float] NULL,
[Q1_18] [float] NULL,
[Q1_19] [float] NULL,
[Q1_20] [float] NULL,
[Q1_21] [float] NULL,
[Q1_22] [float] NULL,
[Q1_23] [float] NULL,
[Q1_24] [float] NULL,
[Q1_25] [float] NULL,
[Q1_26] [float] NULL,
[Q1_27] [float] NULL,
[Q1_28_MAIN] [float] NULL,
[Q1_28_OTHER] [float] NULL,
[Q1_28_PREVIOUS] [float] NULL,
[Q1_29_SEASONAL] [float] NULL,
[Q1_29_YEARROUND] [float] NULL,
[Q1_30_MAIN] [float] NULL,
[Q1_30_OTHER] [float] NULL,
[Q1_30_PREVIOUS] [float] NULL,
[Q1_31_MAIN] [float] NULL,
[Q1_31_OTHER] [float] NULL,
[Q1_31_PREVIOUS] [float] NULL,
[Q1_32_MAIN] [float] NULL,
[Q1_32_OTHER] [float] NULL,
[Q1_32_TOTAL] [float] NULL,
[Q1_33_MAIN] [float] NULL,
[Q1_33_OTHER] [float] NULL,
[Q1_33_TOTAL] [float] NULL,
[Q1_35] [float] NULL,
[Q1_37] [float] NULL,
[Q1_38] [float] NULL,
[Q1_39] [float] NULL,
[Q1_40] [float] NULL,
[Q1_41] [float] NULL,
[Q1_41_DKNS] [float] NULL,
[Q1_41a] [float] NULL,
[Q1_41b] [float] NULL,
[Q1_41b_DKNS] [float] NULL,
[Q1_42] [float] NULL,
[Q1_42_DKNS] [float] NULL,
[Q1_43] [float] NULL,
[Q1_44] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
February 27, 2013 at 12:26 pm
INSERT INTO [janinetestdata].[dbo].[EDUCATION]
([ED_THREE]
,[ED_FOUR]
,[ED_FIVE]
,[ED_SIX]
,[PERSON_NUMBER]
,[zBarcode])
VALUES
(<ED_THREE, float,>
,<ED_FOUR, float,>
,<ED_FIVE, float,>
,<ED_SIX, float,>
,<PERSON_NUMBER, float,>
,<zBarcode, float,>)
GO
INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL]
([BatchNo]
,[zBarcode]
,[HHBarcode]
,[Person_No]
,[District]
,[URBAN_RURAL]
,[Cluster]
,[HHNUM]
,[Final_Result_Code]
,[Q1_5]
,[Q1_5_DKNS]
,[Q1_7]
,[Q1_8]
,[Q1_9]
,[Q1_9_2]
,[Q1_10a]
,[Q1_10b]
,[Q1_10c]
,[Q1_10_Total]
,[Q1_11]
,[Q1_12]
,[Q1_13]
,[Q1_14]
,[Q1_15]
,[Q1_15a]
,[Q1_16]
,[Q1_17]
,[Q1_18]
,[Q1_19]
,[Q1_20]
,[Q1_21]
,[Q1_22]
,[Q1_23]
,[Q1_24]
,[Q1_25]
,[Q1_26]
,[Q1_27]
,[Q1_28_MAIN]
,[Q1_28_OTHER]
,[Q1_28_PREVIOUS]
,[Q1_29_SEASONAL]
,[Q1_29_YEARROUND]
,[Q1_30_MAIN]
,[Q1_30_OTHER]
,[Q1_30_PREVIOUS]
,[Q1_31_MAIN]
,[Q1_31_OTHER]
,[Q1_31_PREVIOUS]
,[Q1_32_MAIN]
,[Q1_32_OTHER]
,[Q1_32_TOTAL]
,[Q1_33_MAIN]
,[Q1_33_OTHER]
,[Q1_33_TOTAL]
,[Q1_35]
,[Q1_37]
,[Q1_38]
,[Q1_39]
,[Q1_40]
,[Q1_41]
,[Q1_41_DKNS]
,[Q1_41a]
,[Q1_41b]
,[Q1_41b_DKNS]
,[Q1_42]
,[Q1_42_DKNS]
,[Q1_43]
,[Q1_44])
VALUES
(<BatchNo, float,>
,<zBarcode, float,>
,<HHBarcode, float,>
,<Person_No, float,>
,<District, float,>
,<URBAN_RURAL, float,>
,<Cluster, float,>
,<HHNUM, float,>
,<Final_Result_Code, float,>
,<Q1_5, float,>
,<Q1_5_DKNS, float,>
,<Q1_7, float,>
,<Q1_8, varchar(42),>
,<Q1_9, float,>
,<Q1_9_2, float,>
,<Q1_10a, float,>
,<Q1_10b, float,>
,<Q1_10c, float,>
,<Q1_10_Total, float,>
,<Q1_11, float,>
,<Q1_12, float,>
,<Q1_13, float,>
,<Q1_14, float,>
,<Q1_15, float,>
,<Q1_15a, float,>
,<Q1_16, float,>
,<Q1_17, varchar(70),>
,<Q1_18, float,>
,<Q1_19, float,>
,<Q1_20, float,>
,<Q1_21, float,>
,<Q1_22, varchar(30),>
,<Q1_23, varchar(30),>
,<Q1_24, float,>
,<Q1_25, float,>
,<Q1_26, float,>
,<Q1_27, float,>
,<Q1_28_MAIN, float,>
,<Q1_28_OTHER, float,>
,<Q1_28_PREVIOUS, float,>
,<Q1_29_SEASONAL, float,>
,<Q1_29_YEARROUND, float,>
,<Q1_30_MAIN, varchar(2),>
,<Q1_30_OTHER, varchar(2),>
,<Q1_30_PREVIOUS, varchar(2),>
,<Q1_31_MAIN, varchar(2),>
,<Q1_31_OTHER, varchar(2),>
,<Q1_31_PREVIOUS, varchar(2),>
,<Q1_32_MAIN, float,>
,<Q1_32_OTHER, float,>
,<Q1_32_TOTAL, float,>
,<Q1_33_MAIN, float,>
,<Q1_33_OTHER, float,>
,<Q1_33_TOTAL, float,>
,<Q1_35, float,>
,<Q1_37, float,>
,<Q1_38, float,>
,<Q1_39, varchar(1),>
,<Q1_40, varchar(1),>
,<Q1_41, float,>
,<Q1_41_DKNS, float,>
,<Q1_41a, float,>
,<Q1_41b, float,>
,<Q1_41b_DKNS, float,>
,<Q1_42, float,>
,<Q1_42_DKNS, float,>
,<Q1_43, float,>
,<Q1_44, float,>)
GO
INSERT INTO [janinetestdata].[dbo].[HH_ALL]
([BatchNo]
,[Cluster]
,[HHNUM]
,[zBarcode]
,[District]
,[URBAN_RURAL]
,[Final_Result_Code]
,[Person_No])
VALUES
(<BatchNo, float,>
,<Cluster, float,>
,<HHNUM, float,>
,<zBarcode, float,>
,<District, float,>
,<URBAN_RURAL, float,>
,<Final_Result_Code, float,>
,<Person_No, float,>)
GO
INSERT INTO [janinetestdata].[dbo].[HH_LISTING]
([PERSON_NUMBER]
,[HL_THREE]
,[HL_FOUR]
,[HL_FIVE]
,[HL_SIX]
,[HL_SEVEN]
,[HL_SEVENB]
,[zBarcode])
VALUES
(<PERSON_NUMBER, varchar(2),>
,<HL_THREE, float,>
,<HL_FOUR, float,>
,<HL_FIVE, float,>
,<HL_SIX, float,>
,<HL_SEVEN, float,>
,<HL_SEVENB, float,>
,<zBarcode, float,>)
GO
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply