Query Help

I have a table with 5 columns.   i am trying to insert data into this table, but one column needs a value that is derived from another table and another column needs to have the next highest available interger in that column added.

the 5 columns are

CalculateDateTime (datetime) -  value needed is a constant null
AccountStartBalance(interger)- value needed is a constant integer with a value of 0
AccountServiceKey - The value needs to be the next highest available in that column value incremented by the value of 1
ServiceOption(nvarchar) - value needs to be a blank or no value but not null either
Accountkey -  This needs to be derived from another table.....   Select Accountkey from billingmaster where accountnumber = '999999999'

the result from the last column query would be '11111'
I dont have a clue of how to construct the insert statement but i am guessing it would be.

insert into billingsecondary
values (NULL,0,I need help, I need help, I need help);
jamesmetcalf74Asked:
Who is Participating?
 
Brad FeatherstoneConnect With a Mentor Commented:
Be lazy - There is a way to have SQL Server auto magically do a lot of the work for you.

It all depends on how you define the columns of the table:


declare table billingsecondary
(
      CalculateDateTime datetime default null      
,      AccountStartBalance int default 0
,      AccountServiceKey bigint identity(1,1)
,      ServiceOption nvarchar(<<whatever length>>) default ''
,      Accountkey <<notype>>
);
go

-- look up the account key
declare @Accountkey <<notype>>;
Select @Accountkey = Accountkey from billingmaster where accountnumber = '999999999';

-- push in a record
insert into billingsecondary (ServiceOption, Accountkey) values ('Shoe Super Shine', @Accountkey);

Since yo did not specifically load the following columns, they will contain either the defined default value or, in the case of the identity column, the next integer value
  • CalculateDateTime
  • AccountStartBalance
  • AccountServiceKey



If this is the first ever insert into the table, the record will contain
  • CalculateDateTime: NULL
  • AccountStartBalance: 0
  • AccountServiceKey: 1
  • ServiceOption: Shoe Super Shine
  • Accountkey: <<what ever value you looked up>>

The second insert into the table, the record will contain
  • CalculateDateTime: NULL
  • AccountStartBalance: 0
  • AccountServiceKey: 2
  • ServiceOption: Shoe Super Shine
  • Accountkey: <<what ever value you looked up>>
0
 
Bill BachPresidentCommented:
Would this work?

insert into billingsecondary
values (NULL,0,1+(SELECT TOP 1 AccountServiceKey FROM billingsecondary), '', (Select TOP 1 Accountkey from billingmaster where accountnumber = '999999999'));
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You'll want to lock the table as you get the max value, otherwise if two INSERTs ran at the same time, they could both get the same AccountServiceKey number:

SELECT NULL AS CalculateDateTime, 0 AS AccountStartBalance,
    AccountServiceKey_Max AS AccountServiceKey,
    '' AS ServiceOption, bm.AccountKey AS AccountKey
FROM dbo.billingsecondary bs
CROSS JOIN (
    /* lock the table to guarantee that the max value is not read at the same time by diff queries */
    SELECT MAX(AccountServiceKey) AS AccountServiceKey_Max
    FROM dbo.billingsecondary WITH (TABLOCK)
) AS cj1
LEFT OUTER JOIN dbo.billingmaster bm ON bm.accountnumber = '999999999'
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.