SQL statement help

M

Mabrito

Supreme [H]ardness
Joined
Dec 24, 2004
Messages
7,004
I have this SQL SELECT statement:

INSERT INTO customer (custFirstName, custMiddleInitial, custLastName, custAddress1, custAddress2, custCity, custState, custZip, custBusiness, custEmailAddress, custID, custUserName, custPassword, custStatus) VALUES('Bob' , 'A ', 'Smith' , '423 W Summit St' , 'NA' , 'Chicago' , 'IL' , '60641' , 'bob.org' , 'bob@test.com', 1
, 'bob' , 'enter','active')

For the custID, I want it to automatically do a count of the current customer ID's in the table and then increment that count by 1, and use that for the value.

To do the count I know its this statement: SELECT count(custID) from customer

Then I want to take that output and add one to it and use that number in the INSERT INTO statement. This is for a web application I am doing. I know I can do two database calls, doing the count SELECT statement first and storing the value in a variable. Then use that variable for the custID field in the INSERT INTO statement. I wondering though if there is some type of subquery or something I can do where I can make it one statement.
 
you can nest queries

eg

select * from table1
where 4 < select somenum from table2;
 
More likely what he really wants is a serial/auto-increment data-type or sequence on the custID field.
 
eloj said:
More likely what he really wants is a serial/auto-increment data-type or sequence on the custID field.
Click to expand...

Yeah that is exactly what I need. Is there a way in SQL Server that does auto incrementing on a field?
 
I assume so. Try declaring it simply as "SERIAL", and omit it from your INSERT INTO.
 
In SQL 2005 just click on CustID and right click. Go to properties. There should be a field name identity increment. Set it to true.

Every time you do an insert, it will automatically increment the CustID.
 
NickTheNut said:
Is this what you're looking for: http://www.tizag.com/sqlTutorial/sqlcreate.php

Code: 
CREATE TABLE employees
(
id INT(3) NOT NULL AUTO_INCREMENT,
Lastname VARCHAR(50),
Firstname VARCHAR(25),
Title VARCHAR(10) DEFAULT 'crew' NULL
);

That help?
Click to expand...

With SQL Server use "IDENTITY" instead of "AUTO_INCREMENT". The length of "3" for the "id" column is also optional (personally, I'd omit it anyways).
 
PTNL said:
With SQL Server use "IDENTITY" instead of "AUTO_INCREMENT". The length of "3" for the "id" column is also optional (personally, I'd omit it anyways).
Click to expand...

I thought it might be wrong, wasn't sure. I don't know MSSQL myself, but knew he was basically looking for whatever the MySQL equivalent might be.

Also Pwyl_The_Destroyer, in MySQL if you were to delete the last row and then create a new row, its ID would be the last ID created + 1. It does not look at the last rows ID, instead it just looks at the last ID created, whether it still exists or not.

I don't know for sure, but I'd assume this behavior is the same in MSSQL.
 
Right, if you're using an identity field. The op was wanting to use count+1 for the new record's ID. I wasn't sure if he realized why it would be a bad idea :)
 
NickTheNut said:
I thought it might be wrong, wasn't sure. I don't know MSSQL myself, but knew he was basically looking for whatever the MySQL equivalent might be.

Also Pwyl_The_Destroyer, in MySQL if you were to delete the last row and then create a new row, its ID would be the last ID created + 1. It does not look at the last rows ID, instead it just looks at the last ID created, whether it still exists or not.

I don't know for sure, but I'd assume this behavior is the same in MSSQL.
Click to expand...

Pwyl_The_Destroyer said:
Right, if you're using an identity field. The op was wanting to use count+1 for the new record's ID. I wasn't sure if he realized why it would be a bad idea :)
Click to expand...

Lazy_Moron -- Basically, all of us are hinting at the same underlying suggestion. Let the DBMS manage number incrementing. SQL Server already does this, you just have to tell it with the IDENTITY keyword from either a script or in the Management interface. No need to re-invent the wheel here.
 
whilst identity is probalby the way to go yu could always use


select max(custid) +1 from customers

using SQL server you simply goto the table right click design click th e column name and in column proerties set as identity specification with increment of 1
then you dont need it in the insert statement
 
Yakyb said:
whilst identity is probalby the way to go yu could always use


select max(custid) +1 from customers

using SQL server you simply goto the table right click design click th e column name and in column proerties set as identity specification with increment of 1
then you dont need it in the insert statement
Click to expand...
Creating a concurrency insert issue is the whole reason behind skipping this programmatic method. Let the DBMS system manage incrementing numbers, and move on to the next task.
 
or you could use the @@identity in your insert statement, which will auto-increment and int identity field for you.
INSERT INTO customer (custFirstName, custMiddleInitial, custLastName, custAddress1, custAddress2, custCity, custState, custZip, custBusiness, custEmailAddress, custID, custUserName, custPassword, custStatus) VALUES('Bob' , 'A ', 'Smith' , '423 W Summit St' , 'NA' , 'Chicago' , 'IL' , '60641' , 'bob.org' , 'bob@test.com', @@identity
, 'bob' , 'enter','active')
Click to expand...
Andy
 
You must log in or register to reply here.
Back
Top