?
Solved

Check the next number and then insert query

Posted on 2016-08-08
10
Medium Priority
?
59 Views
Last Modified: 2016-08-08
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
Comment
Question by:RIAS
[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
  • 4
  • 4
  • 2
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41747153
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
 

Author Comment

by:RIAS
ID: 41747154
Cheers!

but have you ever tried an identity column? it will handle this for you?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41747164
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41747166
Try this:
DBCC CHECKIDENT ( table1, NORESEED )

Open in new window

0
 

Author Comment

by:RIAS
ID: 41747169
Vitor Montalvão , Thanks.
Anu suggestion on how do I modify my query above?
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41747170
Even Sequence is available for SQL Server and sometimes is better option than Identity columns.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41747180
but are you using an identity field or not?
0
 

Author Comment

by:RIAS
ID: 41747182
yes, identity field
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 total points
ID: 41747186
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
 

Author Closing Comment

by:RIAS
ID: 41747220
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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