Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check the next number and then insert query

Posted on 2016-08-08
10
Medium Priority
?
60 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

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 52

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 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