Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Minor Error During Insert

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    How did you find out 'DbTotal’ has the value '*' after the insert? DbTotal is a tinyint, '*' is not a valid tinyint.

    I guess empty (NULL) columns are displayed with a '*' in whatever tool you are using to see what is in the table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    28
    Thanks a lot Wim for your input. I've resolve the problem by making all the data types same in Temp and permanent table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •