Using SQL Insert and want some fields to be inserted with zero balance

How would I get all 'SUM' (sumcurf, sumcurfe, sumcurfea etc.) fields to be inserted with zero balances?
I know  I could run a update after the table is rebuilt to zero out these fields but why not do it as you rebuild table.



truncate table dbo.tbl_ps_combine2

insert INTO dbo.tbl_ps_combine2

select [Functional Reporting], ExpenseLevel3, costelement, OffAcname, [Cost Ctr], per, obcur, LEVEL3, LEVEL4, LEVEL5, LEVEL6, LEVEL7, LEVEL8, Alias,
                      abbreviate, acctdesc, , sumcurfean, sumcurfeanc, sumprev3f, sumprev3fe, sumprev3fea, sumprev3fean, sumprev3feanc,
                       sumytdf, sumytdfe, sumytdfea, sumytdfean, sumytdfeanc, objcurrsum, counter
FROM         dbo.tbl_ps_combine1  
WHERE (per = @pmonth) AND (([Functional Reporting] = @pfunction) OR (@pfunction IS NULL))
AND ((LEVEL3 = @plevel3)  OR (@plevel3 IS NULL)) AND ((LEVEL4 = @plevel4) OR (@plevel4 IS NULL)) AND ((LEVEL5 = @plevel5) OR (@plevel5 IS NULL))
AND ((LEVEL6 = @plevel6) OR (@plevel6 IS NULL)) AND ((LEVEL7 = @plevel7) OR (@plevel7 IS NULL)) AND ((LEVEL8 = @plevel8) OR (@plevel8 IS NULL))  

thayduckProgrammer AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbanttariConnect With a Mentor Commented:
You have to use a field list in your INSERT clause, then put literals in the SELECT:

insert INTO dbo.tbl_ps_combine2 ([Functional Reporting], ExpenseLevel3, costelement, OffAcname, [Cost Ctr], per, obcur, LEVEL3, LEVEL4, LEVEL5, LEVEL6, LEVEL7, LEVEL8, Alias,
                      abbreviate, acctdesc, , sumcurfean, sumcurfeanc, sumprev3f, sumprev3fe, sumprev3fea, sumprev3fean, sumprev3feanc,
                       sumytdf, sumytdfe, sumytdfea, sumytdfean, sumytdfeanc, objcurrsum, counter, sumcurf, sumcurfe, sumcurfea)

select [Functional Reporting], ExpenseLevel3, costelement, OffAcname, [Cost Ctr], per, obcur, LEVEL3, LEVEL4, LEVEL5, LEVEL6, LEVEL7, LEVEL8, Alias,
                      abbreviate, acctdesc, , sumcurfean, sumcurfeanc, sumprev3f, sumprev3fe, sumprev3fea, sumprev3fean, sumprev3feanc,
                       sumytdf, sumytdfe, sumytdfea, sumytdfean, sumytdfeanc, objcurrsum, counter
,0 ,0 ,0
FROM         dbo.tbl_ps_combine1  
WHERE (per = @pmonth) AND (([Functional Reporting] = @pfunction) OR (@pfunction IS NULL))
AND ((LEVEL3 = @plevel3)  OR (@plevel3 IS NULL)) AND ((LEVEL4 = @plevel4) OR (@plevel4 IS NULL)) AND ((LEVEL5 = @plevel5) OR (@plevel5 IS NULL))
AND ((LEVEL6 = @plevel6) OR (@plevel6 IS NULL)) AND ((LEVEL7 = @plevel7) OR (@plevel7 IS NULL)) AND ((LEVEL8 = @plevel8) OR (@plevel8 IS NULL))  
0
 
thayduckProgrammer AnalystAuthor Commented:
Worked like a charm. Thanks for your quick help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.