SQL Improvement

jstevens
SilverLounger
Posts: 2328
Joined: 26 Jan 2010, 16:31
Location: Southern California

SQL Improvement

Post by jstevens »

Is there a more efficient way of updating or inserting to a table if the ID is unknown? I'm interested in updating an account for 12 months (201401 to 201412). Months with zero or null amounts are not stored in the table.

The code below is for one month ie 201401.

Code: Select all

DECLARE @ID int

Set @ID = convert(varchar,
                    (
                      Select Id
                      From budget
                      Where period = 201401   --yyyymm
                      And bunit = '45'
                      And dept_no = '202'
                      And acct_no = '400000'
                      And prod_no = 'NoProd'
                      And scenario = 'budget'
                    )
                  )
IF @ID <> ''
BEGIN
     UPDATE budget
     SET Amount = 1200
     WHERE Id = @ID
END
ELSE
BEGIN
INSERT INTO budget VALUES (201401, '45', '202', '400000', 'NoProd', 'Budget', 'Local', 1200)
END
Regards,
John

User avatar
HansV
Administrator
Posts: 69458
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL Improvement

Post by HansV »

Sorry, I don't know enough about T-SQL.
Regards,
Hans

jstevens
SilverLounger
Posts: 2328
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: SQL Improvement

Post by jstevens »

Hans,

That makes two of us. :grin:
Regards,
John

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: SQL Improvement

Post by Wendell »

I take it your code works as written, but you have to run it twelve times, one for each month. This article FOR-Each Loop may give you some hints. Also, you might want to look at SQL Server Central for some further advice.
Wendell
You can't see the view if you don't climb the mountain!

CData
3StarLounger
Posts: 257
Joined: 24 Dec 2015, 16:41

Re: SQL Improvement

Post by CData »

not a T guy either....but looking at your code it looks about as efficient as one would need:
If ID@ has value: Update
else
Insert

seems pretty efficient