Solved

Check the next number and then insert query

Posted on 2016-08-08
10
53 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
  • 4
  • 4
  • 2
10 Comments
 
LVL 69

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 69

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
 
LVL 46

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 46

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 69

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 69

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now