Insert with Incremental Increase

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert with Incremental Increase

Author

Topic

cardgunner

326 Posts

Posted - 2014-08-01 : 13:54:18

I need to insert into a table a set of records where the first column will be increased by one for every new record. So the first field, ID, will start at T10018 and each record after that the ID will increase by one. The second record will be T10019, the third is T10020 and so on till it reaches T10218. There is a start and stop.

CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 13:56:16

Can you instead just concatenate the T10... onto an identity column? What happens when it hits 218?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2014-08-01 : 14:00:46

It stops.

CardGunner

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-01 : 14:28:32

Use ROW_NUMBER() to assign a sequential value to each new row, then add that to the base number of 10017 to get the final value.

Something like this:



INSERT INTO dbo.tablename
SELECT 'T' + CAST(10017 + row_num AS varchar(10)), ...
FROM (
    SELECT ...,
        ROW_NUMBER() OVER(ORDER BY GETDATE()) AS row_num
    FROM ...
) AS test_data_with_row_num

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 14:30:38

Can you use something like the following

DECLARE @start int = 10018,@stop int =10218

;WITH MYCounts
AS
(
SELECT @start + 1 I
UNION ALL
SELECT I + 1
FROM MYCounts
WHERE I < @stop

)

SELECT 'T' + CAST(I as Varchar(10)) FROM MYCounts
OPTION (MAXRECURSION 0)

cardgunner

326 Posts

Posted - 2014-08-01 : 14:36:12

So i tested this


create table #test_order
(ID int IDENTITY(18,1) PRIMARY KEY
,t_order nvarchar(9)
,t_odat datetime
t_qty int)

Insert into #test_order
values ('', 'T10'+ ID, '2014-08-01-15:12:17.000' , '8')

i get this error
An explicit value for the identity column in table '#test_order' can only be specified when a column list is used and IDENTITY_INSERT is ON.

CardGunner

cardgunner

326 Posts

Posted - 2014-08-01 : 14:49:48

Michael, that looks like it could work but now I need to call that into the insert statement.

How would I use that in the insert statement? Given the table below.


create table #test_order
(t_order nvarchar(9)
,t_odat datetime
t_qty int)

CardGunner

cardgunner

326 Posts

Posted - 2014-08-01 : 15:14:52

i got this to work

DECLARE @start int = 10018,@stop int =10218


;WITH MYCounts
AS
(
SELECT @start + 1 I 
UNION ALL 
SELECT I + 1 
FROM MYCounts
WHERE I < @stop

)
Insert into #mycounts
SELECT 'T' + CAST(I as Varchar(10)) as cnt FROM MYCounts
OPTION (MAXRECURSION 0)

CardGunner

cardgunner

326 Posts

Posted - 2014-08-01 : 15:22:57

But now when I try to add the other columns I get the error incorrect syntax near keyword 'OPTION'

create table #mycounts
(t_order nvarchar(9)
,t_odat datetime
,t_qty int)


DECLARE @start int = 10018,@stop int =10218


;WITH MYCounts
AS
(
SELECT @start + 1 I 
UNION ALL 
SELECT I + 1 
FROM MYCounts
WHERE I < @stop

)
Insert into #mycounts
values(
(SELECT 'T' + CAST(I as Varchar(10)) as cnt FROM MYCounts
OPTION (MAXRECURSION 0))
, getdate
, 0)


drop table #mycounts

CardGunner

cardgunner

326 Posts

Posted - 2014-08-01 : 15:31:34

Okay I changed someof the sql and got a different result


create table #mycounts
(t_order nvarchar(9)
,t_odat datetime
,t_qty int)

go

DECLARE @start int = 10018,@stop int =10218


;WITH MYCounts
AS
(
SELECT @start + 1 I 
UNION ALL 
SELECT I + 1 
FROM MYCounts
WHERE I < @stop

)
Insert into #mycounts

SELECT 'T' + CAST(I as Varchar(10)) as cnt FROM MYCounts
OPTION (MAXRECURSION 0)
, getdate
,'0'


drop table #mycounts

results
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ','.

CardGunner

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-02 : 06:58:10

Why is this at the end of your query??
, getdate
,'0'

Are you trying to add those to the Select?

If so try this

create table #mycounts
(t_order nvarchar(9)
,t_odat datetime
,t_qty int)

go

DECLARE @start int = 10018,@stop int =10218

;WITH MYCounts
AS
(
SELECT @start + 1 I
UNION ALL
SELECT I + 1
FROM MYCounts
WHERE I < @stop

)
Insert into #mycounts

SELECT 'T' + CAST(I as Varchar(10)) as cnt, getdate()
,'0'
FROM MYCounts
OPTION (MAXRECURSION 0)

SELECT * FROM #mycounts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources