Solved

Check the next number and then insert query

Posted on 2016-08-08
10
56 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 49

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 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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 250 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

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)

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

726 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