SQL Improvement

[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

[HansV]

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

[jstevens]

Hans,

That makes two of us.

[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.

[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