Inserting repeating incremental values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting repeating incremental values

Author

Topic

ssipiora
Starting Member

2 Posts

Posted - 2010-08-16 : 15:46:31

I"m very new to SQL but need to insert LOT numbers to inventory.

I have my table (cards) and my column (lot).

The cards come in lots of 25 units. Each lot increments by 1. So rows 1-25 would have lot 0001, 26-50 would have a lot of 0002, etc. I need to do this for 1,000,000 rows.

I've looked at a lot of resources and books and can't find any examples of how to do this.

Any ideas?

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-16 : 16:07:25

can you post the ddl of your tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-16 : 16:19:03

ya know

I don't even have to insert any rows to tell you what card number belongs to what lot..just do this


DECLARE @c int
 SELECT @c = 25
 SELECT ROUND((@c*1.00/25.00)+.49,0)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ssipiora
Starting Member

2 Posts

Posted - 2010-08-16 : 23:50:23

I talked to my Web developer and he claims he really needs the value in the column.

Here is the DDL:
USE [development]
GO
/****** Object: Table [dbo].[cards] Script Date: 08/16/2010 21:45:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cards](
[card_number] [nchar](16) NULL,
[exp_date] [nchar](16) NULL,
[lot] [nchar](80) NULL
) ON [PRIMARY]
GO

quote:
Originally posted by X002548

ya know

I don't even have to insert any rows to tell you what card number belongs to what lot..just do this

DECLARE @c int
SELECT @c = 25
SELECT ROUND((@c*1.00/25.00)+.49,0)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-08-17 : 03:09:55

declare @tbl as table(id int,rid int)
insert into @tbl
select top 1000000
row_number()over(order by sys.sysobjects.id desc)%25,
row_number()over(order by (select 1))
from sys.sysobjects,sys.syscolumns

select rid,dense_rank()over(order by rid-id) from @tbl

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources