asked
How to store all the values of a particular column of a table
Hello Experts, I am writing a store procedure to eliminate the column values which has value lesser than or equal to 1752. My requirement is to eliminate that particular column values whose value is lesser than or equal to 1752 from source table and load the remaining data to target table. My developed code so far--- CREATE PROCEDURE [dbo].[LOAD_PP_COST_CENTER] AS BEGIN BEGIN TRY SET NOCOUNT ON; DECLARE @Rows_Passed int, @Table_Count int, @Rows_error int, @Min_Year int ; SET @Rows_error=0; -- To delete the previous data in the table. TRUNCATE TABLE dbo.D_PP_COST_CENTER ; --- To load the data from STAGE.D_PP_COST_CENTER table to dbo.PP_COST_CENTER table. INSERT INTO dbo.D_PP_COST_CENTER ( CC_ID, CC_NAME, CC_MANAGER, CC_MANAGER_SSN, AREA_NAME, DISTRICT_NAME, DIVISION_NAME, CO, CO_NAME, CC_LOC_CODE, CC_TYPE, CC_DATE_FROM, CC_DATE_TO, CC_REVIEW_MONTH, OPERATING_UNIT_ID, OPERATING_UNIT_NAME, CC_GEOGRAPHIC_AREA, AREA_GEOGRAPHIC_AREA, DISTRICT_GEOGRAPHIC_AREA, INACTIVE_CC, CC_MGR_ID, CC_MGR_FNDUSR, AREA_MGR_ID, AREA_MGR_FNDUSR, DISTRICT_MGR_ID, DISTRICT_MGR_FNDUSR, DIVISION_MGR_ID, DIVISION_MGR_FNDUSR, GLOBAL_REGION ) SELECT COST_CENTER_ID, COST_CENTER_NAME, COST_CENTER_MANAGER, cast(COST_CENTER_MANAGER_SSN as int), AREA_NAME, DISTRICT_NAME, DIVISION_NAME, cast(CO as int), CO_NAME, CC_LOC_CODE, CC_TYPE, cast(COST_CENTER_DATE_FROM as datetime), cast(COST_CENTER_DATE_TO as datetime), cast(COST_CENTER_REVIEW_MONTH as datetime), OPERATING_UNIT_ID, OPERATING_UNIT_NAME, CC_GEOGRAPHIC_AREA, AREA_GEOGRAPHIC_AREA, DISTRICT_GEOGRAPHIC_AREA, INACTIVE_CC, CC_MGR_ID, CC_MGR_FNDUSR, AREA_MGR_ID, AREA_MGR_FNDUSR, DISTRICT_MGR_ID, DISTRICT_MGR_FNDUSR, DIVISION_MGR_ID, DIVISION_MGR_FNDUSR, GLOBAL_REGION FROM STAGE.PP_COST_CENTER ; --To get all the year values of COST_CENTER_DATE_FROM column and store those values in a variable set @Min_Year =(select distinct YEAR(COST_CENTER_DATE_FROM) from [STAGE].[PP_COST_CENTER] ) ; --To get the count of records passed from the STAGE.PP_COST_CENTER table. Select @Rows_Passed = count(*) from STAGE.PP_COST_CENTER; -- Inserting number of records processed , error records count into the Last run control table Insert into LAST_RUN_CTRL_TBL values('dbo.D_PP_COST_CENTER', GETDATE(), @Rows_Passed, @Rows_error, GETDATE(),'SUCCESS'); select @Table_Count=count(*) from [dbo].[LAST_RUN_CTRL_TBL] where TABLENAME='dbo.D_PP_COST_CENTER' if(@Table_Count < 1) BEGIN Insert into [dbo].[ERROR_TABLE] ([ERROR_NUMBER], ERROR_DESCRIPTION, SOURCE_TABLE, [ERROR_LINE], ERROR_TIME) values (301, 'There is no default record in dbo.LAST_RUN_CTRL_TBL','[dbo].[D_PP_COST_CENTER]',ERROR_LINE(),GETDATE()); Insert into LAST_RUN_CTRL_TBL values('dbo.D_PP_COST_CENTER', GETDATE(), 0, 1, GETDATE(),'FAILED'); END END TRY BEGIN CATCH if(@Min_Year <= 1752) Begin Insert into [dbo].[ERROR_TABLE] ([ERROR_NUMBER], ERROR_DESCRIPTION, SOURCE_TABLE, [ERROR_LINE], ERROR_TIME) values (301, 'Year should not be lesser than 1753','[STAGE].[PP_COST_CENTER]',ERROR_LINE(),GETDATE()); -- Inserting number of records processed , error records count into the Last run control table Insert into LAST_RUN_CTRL_TBL values('DBO.D_PP_COST_CENTER', GETDATE(), 0, 1, GETDATE(),'FAILED'); END END CATCH END
@rkvvrkvarma Is the database in question a SQL Server database? If so then how do you expect the staging table to have any values in the column named **COST\_CENTER\_DATE\_FROM** earlier than 1753? I already mentioned in my previous comment that the smallest datetime value which can be stored in SQL Server is Jan 1 1753. If your staging table includes the rows with values like this then you cannot cast the values as datetime in your select statement, you ought to cast as datetime2. Please let me know if you still need any help.
@rkvvrkvarma The requirements are not clear. Do you simply need to insert the data from **STAGE.PP\_COST\_CENTER** staging table into **dbo.D\_PP\_COST\_CENTER**, including only those rows which have the year part of the **COST\_CENTER\_DATE\_FROM** values greater than 1752? All of them should, because otherwise you will get the error trying to cast this column as datetime. In SQL Server, the smallest date which can be stored in datetime data type is Jan 1 1753. This means that there is no way to insert the rows if the staging table contains any values in the date from column which represent the date earlier than that. If there are such rows then you need to use the cast to some other data type, not datetime. Also, these 2 lines are plain wrong: -- To get all the year values of COST_CENTER_DATE_FROM -- column and store those values in a variable set @Min_Year = (select distinct YEAR(COST_CENTER_DATE_FROM) from [STAGE].[PP_COST_CENTER] ); This attempt to set the variable value will never work if there are multiple values in the sub-select. What do you want those 2 lines of code to accomplish? Please elaborate.
Yes !! I want to store all the values greater than Jan 1 1753 of COST_CENTER_DATE_FROM column along with other columns in a target table. Source - STAGE.PP_COST_CENTER Target - dbo.D_PP_COST_CENTER
18 People are following this question.
