April 20, 2023 at 7:32 pm
hi all,
i have to compare 9 times same scheme of table but each table keeps data for different device.
my purpose is to list all the tables and then with cursor to build my queries.the need is to check if the table contains null values in all fields.
i run one query to count all the records of the table ,then one more with a where clause all fields to be null , if the records are equal i need to delete the records of the table.
sql server is very old uses query analyzer , i attache also my code , any help suggestion more than welcome and appreciated.
April 20, 2023 at 7:36 pm
declare @nm as varchar(50),@nm2 varchar(50), @cnt1 int,@cnt2 int
--select @nm='T_30767_AP10MinData9'
select TABLE_NAME into #pina
from information_schema.tables
where Table_Type = 'BASE TABLE' and table_name like '%AP10MinData9%'
declare curs1 cursor FORWARD_ONLY for select TABLE_NAME from #pina
open curs1
FETCH NEXT FROM curs1 into @nm
WHILE @@FETCH_STATUS = 0
BEGIN
set @cnt1 = 0
set @cnt2 = 0
exec('select count(*) as count into ##temp from '+@nm)
set @cnt1 = (select * from ##temp)
--select @cnt1
exec ('select count (*) as count1 into ##temp1 from '+@nm +' Where
Blds_BladeA_ProportionalValveAFeedback_Avg is null and
Blds_BladeA_ProportionalValveAFeedback_Max is null and
ConditIOning_ConverterCooling_ConverterInletTemp_Max is null and
ConditIOning_ConverterCooling_ConverterInletTemp_Min is null and
ConditIOning_ConverterCooling_ConverterInletTemp_Std is null and
ConditIOning_ConverterCooling_ConverterInternalFanSpeed_Avg is null and
ConditIOning_ConverterCooling_ConverterInternalFanSpeed_Max is null and
ConditIOning_ConverterCooling_ConverterInternalFanSpeed_Min is null and
ConditIOning_ConverterCooling_ConverterInternalFanSpeed_Std is null and
ConditIOning_ConverterCooling_ConverterPanelAirHumidity_Avg is null and
ConditIOning_ConverterCooling_ConverterPanelAirHumidity_Max is null and
ConditIOning_ConverterCooling_ConverterPanelAirHumidity_Min is null and
ConditIOning_ConverterCooling_ConverterPanelAirHumidity_Std is null and
ConditIOning_ConverterCooling_ConverterPanelAirTemp_Avg is null and
ConditIOning_ConverterCooling_ConverterPanelAirTemp_Max is null and
ConditIOning_ConverterCooling_ConverterPanelAirTemp_Min is null and
ConditIOning_NacelleCooling_NacelleFanSpeed_Avg is null and
ConditIOning_NacelleCooling_NacelleFanSpeed_Max is null and
ConditIOning_NacelleCooling_NacelleFanSpeed_Min is null and
ConditIOning_NacelleCooling_NacelleFanSpeed_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule1FanSpeed_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule1FanSpeed_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule1FanSpeed_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule1FanSpeed_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule1FilterRemainingValue_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule1FilterRemainingValue_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule1FilterRemainingValue_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule1FilterRemainingValue_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule2FanSpeed_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule2FanSpeed_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule2FanSpeed_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule2FanSpeed_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule2FilterRemainingValue_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule2FilterRemainingValue_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule2FilterRemainingValue_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule2FilterRemainingValue_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule3FanSpeed_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule3FanSpeed_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule3FanSpeed_Minu is null and
ConditIOning_TrbCooling_TowerCoolingModule3FanSpeed_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule3FilterRemainingValue_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule3FilterRemainingValue_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule3FilterRemainingValue_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule3FilterRemainingValue_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule4FanSpeed_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule4FanSpeed_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule4FanSpeed_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule4FanSpeed_Std is null and
ConditIOning_TrbCooling_TowerCoolingModule4FilterRemainingValue_Avg is null and
ConditIOning_TrbCooling_TowerCoolingModule4FilterRemainingValue_Max is null and
ConditIOning_TrbCooling_TowerCoolingModule4FilterRemainingValue_Min is null and
ConditIOning_TrbCooling_TowerCoolingModule4FilterRemainingValue_Std is null and
Cont_ProdCntl_Stat_PLCOvrBoostStatMax is null and
Gear_CoolingWater_FansSpeed_Avg is null and
Gear_CoolingWater_FansSpeed_Max is null and
Gear_CoolingWater_FansSpeed_Min is null and
Gear_CoolingWater_FansSpeed_Std is null and
Gear_Filter_BeforeOfflineFilterOilPress_Avg is null and
Gear_Filter_BeforeOfflineFilterOilPress_Max is null and
Gear_Filter_BeforeOfflineFilterOilPress_Min is null and
Gear_Filter_BeforeOfflineFilterOilPress_Std is null and
Gear_Filter_InlineFilterOilTemp_Avg is null and
Gear_Filter_InlineFilterOilTemp_Max is null and
Gear_Filter_InlineFilterOilTemp_Min is null and
Gear_Filter_InlineFilterOilTemp_Std is null and
Gear_Filter_OfflineFilterOilTemp_Avg is null and
Gear_Filter_OfflineFilterOilTemp_Max is null and
Gear_Filter_OfflineFilterOilTemp_Min is null and
Gear_Filter_OfflineFilterOilTemp_Std is null and
Gear_Oil_Meas_ODMFeBin1Count is null and
Gear_Oil_Meas_ODMFeBin2Count is null and
Gear_Oil_Meas_ODMFeBin3Count is null and
Gear_Oil_Meas_ODMFeBin4Count is null and
Gear_Oil_Meas_ODMFeBin5Count is null and
Gear_Oil_Meas_ODMNonFeBin1Count is null and
Gear_Oil_Meas_ODMNonFeBin2Count is null and
Gear_Oil_Meas_ODMNonFeBin3Count is null and
Gear_Oil_Meas_ODMNonFeBin4Count is null and
Gear_Oil_Meas_ODMSensorCurrentBITSts is null and
Gear_Oil_Meas_ODMSensorCurrentErrCount is null and
Gear_Oil_Meas_ODMTotFeCount is null and
Gear_Oil_Meas_ODMTotNonFeCount is null and
Gen_CoolingWater_FansSpeed_Avg is null and
Gen_CoolingWater_FansSpeed_Max is null and
Gen_CoolingWater_FansSpeed_Min is null and
Gen_CoolingWater_FansSpeed_Std is null and
--Gen_shftCurrent_GenshftCurrent1_Avg is null and
--Gen_shftCurrent_GenshftCurrent1_Max is null and
--Gen_shftCurrent_GenshftCurrent1_Min is null and
--Gen_shftCurrent_GenshftCurrent1_Std is null and
--Gen_shftCurrent_GenshftCurrent2_Avg is null and
--Gen_shftCurrent_GenshftCurrent2_Max is null and
--Gen_shftCurrent_GenshftCurrent2_Min is null and
--Gen_shftCurrent_GenshftCurrent2_Std is null and
--Gen_shftCurrent_GenshftCurrentCharge1_Avg is null and
--Gen_shftCurrent_GenshftCurrentCharge1_Max is null and
--Gen_shftCurrent_GenshftCurrentCharge1_Min is null and
--Gen_shftCurrent_GenshftCurrentCharge1_Std is null and
--Gen_shftCurrent_GenshftCurrentCharge2_Avg is null and
--Gen_shftCurrent_GenshftCurrentCharge2_Max is null and
--Gen_shftCurrent_GenshftCurrentCharge2_Min is null and
--Gen_shftCurrent_GenshftCurrentCharge2_Std is null and
Grd_FlexPwr_Temp_PscHkHarmFilterPanelTemp is null and
HVTrafo_Fans_FanSpeed_Avg is null and
HVTrafo_Fans_FanSpeed_Max is null and
HVTrafo_Fans_FanSpeed_Min is null and
HVTrafo_Fans_FanSpeed_Std is null and
Hyd_Oil_Leakage_Avg is null and
Hyd_Oil_Leakage_Flow is null and
Hyd_Oil_Leakage_Max is null and
Hyd_Oil_Leakage_Min is null and
Hyd_Oil_Leakage_Std is null and
Hyd_Sts_HighPressFilterPressDrop_Avg is null and
Hyd_Sts_HighPressFilterPressDrop_Max is null and
Hyd_Sts_HighPressFilterPressDrop_Min is null and
Hyd_Sts_HighPressFilterPressDrop_Std is null and
Hyd_Sts_OfflineFilterPressDrop_Avg is null and
Hyd_Sts_OfflineFilterPressDrop_Max is null and
Hyd_Sts_OfflineFilterPressDrop_Min is null and
Hyd_Sts_OfflineFilterPressDrop_Std is null and
-- Id is null and
-- PCTimeStamp is null and
-- PPMTimeStamp is null and
Prod_DrvdFig_ActProdGain is null and
--Prod_DrvdFig_LDO_ValidationToggleOutCountDelta is null and
Prod_DrvdFig_ReactProdGain is null and
Prod_TotAccumulated_ExtendedHWOActPwr is null and
Prod_TotAccumulated_ExtendedHWOReactPwr is null and
--ProtocolVersionId is null and
Rtr_Sts_GenTachoSpeed2_Avg is null and
Rtr_Sts_GenTachoSpeed2_Max is null and
Rtr_Sts_GenTachoSpeed2_Min is null and
Rtr_Sts_GenTachoSpeed2_Std is null and
SafetySys_Hub_HubRateGyroMeasuredRtrSpeed_Avg is null and
SafetySys_Hub_HubRateGyroMeasuredRtrSpeed_Max is null and
SafetySys_Hub_HubRateGyroMeasuredRtrSpeed_Min is null and
SafetySys_Hub_HubRateGyroMeasuredRtrSpeed_Std is null and
SafetySys_HubInSafeMode_BallValvesClosed is null and
SafetySys_RtrSpeedMainshftSpeed_Avg is null and
SafetySys_RtrSpeedMainshftSpeed_Max is null and
SafetySys_RtrSpeedMainshftSpeed_Min is null and
SafetySys_RtrSpeedMainshftSpeed_Std is null and
Sys_Logs_AvailIEC_CategoryForcedOutage is null and
Sys_Logs_AvailIEC_CategoryForceMajeure is null and
Sys_Logs_AvailIEC_CategoryFullPerformance is null and
Sys_Logs_AvailIEC_CategoryInFrmtionUnavailable is null and
Sys_Logs_AvailIEC_CategoryOptionalMostActInPeriod is null and
Sys_Logs_AvailIEC_CategoryOptionalTimeActInPeriod is null and
Sys_Logs_AvailIEC_CategoryOutOfElectricalSpecification is null and
Sys_Logs_AvailIEC_CategoryOutOfEnvironmentalSpecification is null and
Sys_Logs_AvailIEC_CategoryPartialPerformance is null and
Sys_Logs_AvailIEC_CategoryPlannedCorrectiveAction is null and
Sys_Logs_AvailIEC_CategoryRequestedShutDwn is null and
Sys_Logs_AvailIEC_CategoryScheduledMaintenance is null and
Sys_Logs_AvailIEC_CategorySuspended is null and
Sys_Logs_AvailIEC_CategoryTechnicalStandby is null and
Sys_Logs_FirstActSupervisionIdIEC is null and
Tower_ForeAftTowerDamping_ColPitchPosRefOffset_Avg is null and
Tower_ForeAftTowerDamping_ColPitchPosRefOffset_Max is null and
Tower_ForeAftTowerDamping_ColPitchPosRefOffset_Min is null and
Tower_ForeAftTowerDamping_ColPitchPosRefOffset_Std is null and
Tower_Sts_TowerDoorOpenCounter is null and
Tower_Temp_Temp_Avg is null and
Tower_Temp_Temp_Max is null and
Tower_Temp_Temp_Min is null and
-- TTimeStamp is null and
Blds_BladeA_ProportionalValveAFeedback_Min is null and
Blds_BladeA_ProportionalValveAFeedback_Std is null and
Blds_BladeB_ProportionalValveBFeedback_Avg is null and
Blds_BladeB_ProportionalValveBFeedback_Max is null and
Blds_BladeB_ProportionalValveBFeedback_Min is null and
Blds_BladeB_ProportionalValveBFeedback_Std is null and
Blds_BladeC_ProportionalValveCFeedback_Avg is null and
Blds_BladeC_ProportionalValveCFeedback_Max is null and
Blds_BladeC_ProportionalValveCFeedback_Min is null and
Blds_BladeC_ProportionalValveCFeedback_Std is null and
Blds_BladeLoads_PiSVBladeEdgeLoadOscLvlA_Std is null and
Blds_BladeLoads_PiSVBladeEdgeLoadOscLvlB_Std is null and
Blds_BladeLoads_PiSVBladeEdgeLoadOscLvlC_Std is null and
ConditIOning_ConverterChopperCooling_ChopperOvrheatTemp_Avg is null and
ConditIOning_ConverterChopperCooling_ChopperOvrheatTemp_Max is null and
ConditIOning_ConverterChopperCooling_ChopperOvrheatTemp_Min is null and
ConditIOning_ConverterChopperCooling_ChopperOvrheatTemp_Std is null and
ConditIOning_ConverterChopperCooling_ChopperTemp_Avg is null and
ConditIOning_ConverterChopperCooling_ChopperTemp_Max is null and
ConditIOning_ConverterChopperCooling_ChopperTemp_Min is null and
ConditIOning_ConverterChopperCooling_ChopperTemp_Std is null and
ConditIOning_ConverterCooling_ConverterExternalFanSpeed_Avg is null and
ConditIOning_ConverterCooling_ConverterExternalFanSpeed_Std is null and
ConditIOning_ConverterCooling_ConverterInletTemp_Avg is null')
set @cnt2 = (select * from ##temp1)
if (@cnt1 = @cnt2)
exec ('delete from '+ @nm)
else
break;
drop table ##temp
drop table ##temp1
drop table #pina
FETCH NEXT FROM curs1
END
deallocate curs1
April 20, 2023 at 8:32 pm
This is a much shorter template and is thus easier to understand. It's also probably more efficient, because it will abort as soon as it finds ANY row that contains something that isn't NULL.
IF NOT EXISTS
(
SELECT * FROM <yourtable>
EXCEPT
SELECT NULL, NULL, ...., NULL -- I'm not going to bother to write all the NULLs.
)
TRUNCATE TABLE <yourtable>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 20, 2023 at 8:39 pm
Thanks a lot !
this block i will put it in the cursor loop .. if i am correct...!
April 21, 2023 at 3:45 pm
yes is sql server 2000 !! Amazing stuff !!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply