How to insert auto increment?

Hi Expert,
How can I do this without enter 100 time every day?
insert into [tablename] (column1) values(1)
insert into [tablename] (column1) values(2)
....
insert into [tablename] (column1) values(100)
Thank you all for reply-MN
MN wrote:
> Hi Expert,
> How can I do this without enter 100 time every day?
> insert into [tablename] (column1) values(1)
> insert into [tablename] (column1) values(2)
> ...
> insert into [tablename] (column1) values(100)
> Thank you all for reply-MN
Why do you want to generate 100 rows per day if that's the only column?
Just increment a single value instead. If there are other columns
involved then maybe you can use an IDENTITY column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi David,
Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
value is vary. And there are no other column involved except IDENTITY column.
How can I do that? Regards-MN
"David Portas" wrote:

> MN wrote:
> Why do you want to generate 100 rows per day if that's the only column?
> Just increment a single value instead. If there are other columns
> involved then maybe you can use an IDENTITY column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||MN wrote:
> Hi David,
> Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
> value is vary. And there are no other column involved except IDENTITY column.
> How can I do that? Regards-MN
Don't. There are two good reasons. 1. It's inefficient (because a
single row will do the same thing). 2. It may be unreliable (an
IDENTITY sequence can have gaps so the maximum value doesn't
necessarily match the number of rows).
Instead, use a single row:
CREATE TABLE tbl (x INTEGER PRIMARY KEY DEFAULT (1) CHECK (x=1) /*
single row constraint */, col1 INTEGER NOT NULL);
INSERT INTO tbl (col1) VALUES (0);
GO
Then keep updating it like this:
UPDATE tbl SET col1 = col1 + 100 ;
In case you do find it useful again, you can populate a table with
default values only or an IDENTITY column only using the DEFAULT VALUES
clause:
INSERT INTO tbl DEFAULT VALUES;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Tue, 4 Apr 2006 13:16:01 -0700, MN wrote:

>Hi Expert,
>How can I do this without enter 100 time every day?
>insert into [tablename] (column1) values(1)
>insert into [tablename] (column1) values(2)
>...
>insert into [tablename] (column1) values(100)
>Thank you all for reply-MN
Hi MN,
I'd very much like to know why you need to do that....
But the asnwer is: make a permanent table of numbers in your database
(see http://www.aspfaq.com/show.asp?id=2516), and use that:
INSERT INTO tablename (column1)
SELECT Numbers.Number
FROM dbo.Numbers
WHERE Numbers.Number BETWEEN 1 AND 100
Hugo Kornelis, SQL Server MVP
sql

How to increment record id in an insert stored procedure

Hello everyone,
I have a problem. I need to insert records from table2 to table1. In
table1 there is a record id which serves as the key of the table. Each time
a record needs to be inserted, a record id is created. This record id is th
e
max(recordid)+1. I wrote the following stored procedure to insert records.
This stored procedure will insert the ten records from table2 into table1.
The only problem is that the recordcount is same for all ten records, instea
d
of incrementing. Should I store the table id in a different table? Any
response will be greatly appreciated.
Begin Transaction
(Select @.recordcount = (max(record)+1) from [dbo].[table1])
Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
field5, field6)
Select @.recordcount, field1, field2, field3, field4, field5, field6 from
[dbo].[table2]
select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
if @.errnum <> 0 GOTO sqlerror
Commit TransactionLook up IDENTITY in Books Online, that does all the work for you.
Jacco Schalkwijk
SQL Server MVP
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:765A9768-316A-4446-82A6-58737F5040CB@.microsoft.com...
> Hello everyone,
> I have a problem. I need to insert records from table2 to table1. In
> table1 there is a record id which serves as the key of the table. Each
> time
> a record needs to be inserted, a record id is created. This record id is
> the
> max(recordid)+1. I wrote the following stored procedure to insert records.
> This stored procedure will insert the ten records from table2 into table1.
> The only problem is that the recordcount is same for all ten records,
> instead
> of incrementing. Should I store the table id in a different table? Any
> response will be greatly appreciated.
> Begin Transaction
> (Select @.recordcount = (max(record)+1) from [dbo].[table1])
> Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
> field5, field6)
> Select @.recordcount, field1, field2, field3, field4, field5, field6 from
> [dbo].[table2]
> select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
> if @.errnum <> 0 GOTO sqlerror
> Commit Transaction
>

How to increment ID sequentially?

I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?

Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.

|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||

Hi mychucky,

You would have to use 'identity seed' property in your table design window in your SQL Server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||

ZLA:

Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Thanks so much! I got it working now. I would never have found the solution if without your link.

How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

how to increment an integer

HI ALL
i am a newbie in SQL.tell me how to increment an integer variable, i dont want autoincrement.Where is this integer? In a table in a variable, ...?
In a table: UPDATE MYTAB SET INT1=INT1+1 WHERE <some condition>;
In a variable: v_int:=v_int+1;
In a loop: FOR i1 IN 1..n LOOP...
In a query: SELECT INT1 + 1 INTO v_int from MYTAB...;

:confused:sql

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to increment a column with varchar data type

hi,
is there any method for incrementing the varchar field
i have an emp table with columns
create table emprecord(eno varchar(10), ename varchar(20))
i cant give eno column with identity field because identity column must
be of data type int, bigint, smallint, tinyint, or decimal or numeric
insert into emprecord values ('MH1001','satish')
insert into emprecord values ('MH1002','rehman')
my problem is there any method --when i pass only the ename the eno
field should be automatically incremented as MH1003 --each time it has
to check for the highest eno ie.., MH1002 is my highest eno in the
table emprecord
i think there is sequence option in MS Sql server-- if so how to use
sequence
pls help me
thanks
satishTry a search about "SQL Server identity values", but it supports integer
values only. You might need to make appropriate modifications on your table.
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com ?...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||You could use a computed column instead|||satish
create table #t
(
rowid int not null identity(1,1) primary key,
empno AS 'MH'+CAST(rowid AS VARCHAR(10)),
empname VARCHAR (50)
)
insert into #t(empname) VALUES ('Smith')
insert into #t (empname)VALUES ('Clinton')
insert into #t (empname) VALUES ('Brown')
select empno,empname from #t
drop table #t
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||there are no identity columns defined in my table in that case how to
do
this is my table
create table emprecord(eno varchar(10), ename varchar(20))
i always pass only ename|||It's better to break empno into empPrefix (varchar(2)) and empSerial (int).
or, if the table structure cannot be modified, I will opt to use stored
procedure (and/or other programming means).
create table #MyTempEmpRecord06041402
(
empno varchar(10),
empname VARCHAR (50)
)
go
-- Assumes that @.myEmpnoPrefix is always having 2 characters
create procedure #MyInsertEmpname06041402
@.myEmpnoPrefix varchar(2),
@.myEmpname varchar(50)
as
insert #MyTempEmpRecord06041402
select @.myEmpnoPrefix
+ (select convert(varchar(8), convert(int, isnull(max(substring(empno,
1 + len(@.myEmpnoPrefix), 8)), '0')) + 1) as MySerial
from #MyTempEmpRecord06041402
where left(empno, len(@.myEmpnoPrefix)) = @.myEmpnoPrefix)
as empno,
@.myEmpname as empname
go
exec #MyInsertEmpname06041402 'MH', 'Smith'
exec #MyInsertEmpname06041402 'MH', 'Clinton'
exec #MyInsertEmpname06041402 'MP', 'Smith'
exec #MyInsertEmpname06041402 'MP', 'Brown'
exec #MyInsertEmpname06041402 'MH', 'Brown'
go
select * from #MyTempEmpRecord06041402
go
drop procedure #MyInsertEmpname06041402
go
drop table #MyTempEmpRecord06041402
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145007066.106515.192790@.t31g2000cwb.googlegroups.com ?...
> there are no identity columns defined in my table in that case how to
> do
> this is my table
> create table emprecord(eno varchar(10), ename varchar(20))
> i always pass only ename
>