MS SQL Server MSEQ - How to insert auto increment?

Author

How to insert auto increment?

2006-04-04, 8:28 pm

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

David Portas

2006-04-04, 8:28 pm

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

2006-04-04, 8:28 pm

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

David Portas

2006-04-04, 8:28 pm

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

Hugo Kornelis

2006-04-04, 8:28 pm

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

Sponsored Links