Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-24
2
Medium Priority
?
201 Views
Last Modified: 2010-04-21
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))  

0
Comment
Question by:thayduck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
dbanttari earned 200 total points
ID: 21860847
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
 

Author Closing Comment

by:thayduck
ID: 31470373
Worked like a charm. Thanks for your quick help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question