Monir Error During Insert

Question

Post reply

Monir Error During Insert

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
November 26, 2009 at 2:37 pm

#96774

Hello experts,
I’m having a challenge in inserting a row in one of the table. I do some checks and then insert data in a temp table ‘#TempFirstLevelChangeDetail’ and then insert into perminant table ‘FirstLevelChangeDetail’. My code does the insert but I don’t know for what reason in the field ‘DbTotal’ It insert ‘*’ when I do insert using the following insert statement.
-----------------------------------------------------------
insert into dbo.FirstLevelChangeDetail
select CapturedDate, DbTotal, DbAdded, DbDeleted, TbTotal, TbAdded, TbDeleted, ColTotal, ColAdded, ColDeleted, ColLengthChanged, ColTypeChanged
from #TempFirstLevelChangeDetail
------------------------------------------------------------
Can anybody see what is wrong? For reference here is my full code.
--------------------------------------------------------------
Create table #TempFirstLevelChangeDetail
(
CapturedDate smalldatetime,
DbTotal tinyint,
DbAdded tinyint,
DbDeleted tinyint,
TbTotal tinyint,
TbAdded tinyint,
TbDeleted tinyint,
ColTotal tinyint,
ColAdded tinyint,
ColDeleted tinyint,
ColLengthChanged tinyint,
ColTypeChanged tinyint
)
--Variable declaraion to hold dates and number of databases
Declare @TodayCapturedDate smalldatetime,
@YesterdayCapturedDate smalldatetime,
@TodayDatabaseTotal smallint,
@YesterdayDatabaseTotal smallint
--TODAY'S DATE
select top 1 @TodayCapturedDate = captureddate
from TotalDatabase
order by captureddate desc
print @TodayCapturedDate
--TODAY'S TOTAL NUMBER OF DATABASES
select @TodayDatabaseTotal = count(Dbname)
from TotalDatabase
where captureddate = (select distinct top 1 captureddate from TotalDatabase
where captureddate != (select todaydate from #CompareDates)
order by captureddate desc)
print @TodayDatabaseTotal
--YESTERDAY'S DATE
select distinct top 1 @YesterdayCapturedDate = captureddate
from TotalDatabase
where captureddate != (select todaydate from #CompareDates)
order by captureddate desc
print @YesterdayCapturedDate
--YESTERDAY'S TOTAL NUMBER OF DATABASES
select @YesterdayDatabaseTotal = count(Dbname)
from TotalDatabase
where captureddate = @YesterdayCapturedDate
print @YesterdayDatabaseTotal
if (@TodayDatabaseTotal) = (@YesterdayDatabaseTotal)
begin
print 'Both (Today & Yesterday) database are same'
insert into #TempFirstLevelChangeDetail(CapturedDate, DbTotal, DbAdded, DbDeleted, TbTotal, TbAdded, TbDeleted, ColTotal, ColAdded, ColDeleted, ColLengthChanged, ColTypeChanged)
values(@TodayCapturedDate, @TodayDatabaseTotal, '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
end
if (@TodayDatabaseTotal) > (@YesterdayDatabaseTotal)
begin
print 'Today No Of database are more'
insert into #TempFirstLevelChangeDetail(CapturedDate, DbTotal, DbAdded, DbDeleted, TbTotal, TbAdded, TbDeleted, ColTotal, ColAdded, ColDeleted, ColLengthChanged, ColTypeChanged)
values(@TodayCapturedDate, @TodayDatabaseTotal, '1', '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
end
if (@YesterdayDatabaseTotal) > (@TodayDatabaseTotal)
begin
print 'Yesterday No Of database are more'
insert into #TempFirstLevelChangeDetail(CapturedDate, DbTotal, DbAdded, DbDeleted, TbTotal, TbAdded, TbDeleted, ColTotal, ColAdded, ColDeleted, ColLengthChanged, ColTypeChanged)
values(@TodayCapturedDate, @TodayDatabaseTotal, '0', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
end
Go
insert into dbo.FirstLevelChangeDetail
select CapturedDate, DbTotal, DbAdded, DbDeleted, TbTotal, TbAdded, TbDeleted, ColTotal, ColAdded, ColDeleted, ColLengthChanged, ColTypeChanged
from #TempFirstLevelChangeDetail
----------------------------------------------------------------
Thanks a lot in advance.