• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Check the next number and then insert query

Hello,

I have  an insert query

 INSERT INTO table1 (
                ID
            ,AccountName
            , IsActive
            , IsPowerUser
         
        ) SELECT
                  @ID
            , UPPER(@AccountName)

How can I get the next id (datatype int) and then insert the values.
0
RIAS
Asked:
RIAS
  • 4
  • 4
  • 2
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
that can be something like this:
declare @id int
set @id = isnull((select max(id) from table1), 0)

Open in new window

but have you ever tried an identity column? it will handle this for you
0
 
RIASAuthor Commented:
Cheers!

but have you ever tried an identity column? it will handle this for you?
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you have a question? I just see a copy of my comment!

For info on identity column, check http://www.sqlteam.com/article/understanding-identity-columns
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
DBCC CHECKIDENT ( table1, NORESEED )

Open in new window

0
 
RIASAuthor Commented:
Vitor Montalvão , Thanks.
Anu suggestion on how do I modify my query above?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Even Sequence is available for SQL Server and sometimes is better option than Identity columns.
0
 
Éric MoreauSenior .Net ConsultantCommented:
but are you using an identity field or not?
0
 
RIASAuthor Commented:
yes, identity field
0
 
Éric MoreauSenior .Net ConsultantCommented:
if you are using an identity field, you don't have to provide the value, it will be filled automatically.

 INSERT INTO table1 (AccountName, IsActive, IsPowerUser)
SELECT UPPER(@AccountName), ...

Open in new window

0
 
RIASAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now