I am trying to run an insert statement but I keep getting message zero rows affected and nothing hap-CodePudding

I am trying to insert some data into a specific table. I created a temp table to create an identity column that could be used as part of my insert statement. Temp table was created fine and is working. I joined a few other tables to pull the data but for some reason, its not producing any results. Select statement is also returning zero results. I am not sure how to deal with this as I m still quite new to sql.

This is the query I have so far

create table #tmp1
    ([TImesheetID] [int] IDENTITY(1,1) not null,
    PersonID varchar(10), Orderid varchar (10))
    
    insert into #tmp1
    select Tr.PersonId, TR.OrderId from temp_runupload TR
    Left join Person_Identification PI on PI.PersonId=TR.PersonId
    group by TR.PersonId, TR.OrderId
    select * from #tmp1

    declare @yearweek varchar(6) ='202245'
    declare @runid varchar (2) ='86'

    set @yearweek=202245
    set @runid=80



`
insert into Timesheets (YearWeek, RunID, TimeSheetID, orderID, PersonID, PersonDetailRecord, TimeSheetType,
RegHours, OTHours, PayRate, BillRate, AmountField1, AmountField2, AmountField3, RateField1,RateField2, RateField3, PayCycle, CostField1, CostField2, InvoiceID, PersonWeek, Message,TSStatus, CompanyPO, ContactName, ApYearWeek, SeparateInvoice, CalledIn,TimeslipKey,SwipeTSDate,sICPBatchID,    dCommissionPercent,DoNotMail,InvoiceMsg,ETransfer)

    select distinct
        @yearweek
        ,@RunID
        ,TimeSheetID = right('000' cast(t1.timesheetid as varchar(4)),4)
        ,Orderid=t1.OrderID
        ,Personid=t1.personid
        ,DetailRecId = max(OPDR.DetailRecId)
        ,OPS.[TimeSheetType]
        ,RegHours = TR.reghrs
        ,OTHours = TR.othrs
        ,PayRate=isnull(OPS.PayRate,0)
        ,BillRate = TR.BillRate
        ,0--[AmountField1]
        ,0--[AmountField2]
        ,0--[AmountField3]
        ,0--[RateField1]
        ,0--[RateField2]
        ,0--[RateField3]
        ,PAY.[PayCycle]
        ,0--[CostField1]
        ,0--[CostField2]
        ,null --[InvoiceId]
        ,@yearweek --[PersonWeek]
        ,null--[Message]
        ,'O'--[TSStatus]
        ,OH.[CompanyPO]
        ,null --contact name
        -,@yearweek --[AppYearWeek]
        ,null -- [SeparateInvoice]
        ,'N' --[CalledIn]
        ,null --[TimeslipKey]
        ,null --[SwipeTSDate]
        ,null --[sICPBatchID]
        ,null --[dCommissionPercent]
        ,null --[DoNotMail]
        ,null --[InvoiceMsg]
        ,null
        from temp_runupload TR
        inner join Person_Identification PI on  PI.PersonId=TR.PersonId
        inner join #tmp1 T1 on PI.personid=T1.PersonID 
        inner join Order_Person_Detail_Record OPDR on OPDR.OrderId=TR.orderid
        inner join payroll_information PAY on PAY.personid=TR.personid
        inner join  Order_Pay_Structure OPS on OPS.orderid=TR.OrderId and OPS.paystructurecounter=OPDR.paystructurecounter
        inner join order_header OH on OH.orderid=OPDR.orderid 
            group by T1.timesheetid,T1.OrderId, T1.PersonId, ops.TimeSheetType,ops.PayRate, tr.BillRate, pay.PayCycle,
    oh.DefaultCompanyPO, tr.reghrs, TR.OTHrs

CodePudding user response：

Probably you're not getting a hit in one of your joined tables.

The way i debug these is to change all INNER JOINs to LEFT OUTER JOINs first. Then if you still don't get a hit, something is very wrong with the first table in your query.

If you get a hit, then one by one change it back to INNER JOIN until you stop getting rows. That will tell you which table that doesn't get a hit and you can go from where.

Another way is to use "Show query plan". The arrows will show you which of the tables contain zero rows