Decrementing the rows fro every insertion

Question

Post reply

Decrementing the rows fro every insertion

Charmer

SSChampion

Points: 14903
More actions
September 26, 2011 at 6:47 am

#244403

i have a stored procedure which passes the values dynamically....
this is my destination table...
JurisID CodeID CodeIDDescr SrcCodeDescr PnxCodeValue IsConverted
1501 6002 SOU SOU SO 0
1501 6006 NTR NTR NT 0
1501 6002 EA EA EA 0
my need is when my stored procedure inserts some rows, the PnxCodevalue must starts with some default value, say 999...so for the 1st row it starts with 999 and 998 for the 2nd row and 997 for the 3rd row...and so on...when i run the sp again, it must starts with the value 996 automatically and no duplicate values are allowed...how can i do that...?
how to set up a identity column with default values?
Thanks,
Charmer

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

DECLARE @demo TABLE (ID SMALLINT IDENTITY(999, -1) PRIMARY KEY CLUSTERED, sometext VARCHAR(50))

INSERT INTO @demo (sometext) VALUES ('one')

INSERT INTO @demo (sometext) VALUES ('two')

INSERT INTO @demo (sometext) VALUES ('three')

SELECT * FROM @demo ORDER BY ID DESC

sturner SSC-Insane Points: 22411 More actions · Answer 2

Maintain an ID value table for obtaining the the next identifier. Obtain the next value from the table and insert into table wrapped in a transaction.

a simplified example:

declare @nextID int

begin transaction

update nextIDtable set @nextID = nextID = nextID - 1

insert destinationTable (urisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted)

values (1501, 6002, EA, EA, @nextID,0

commit transaction

My example uses an integer but your example table led me to believe you might want to use some data type other than an integer. If you are inserting multiple rows you might want to use OUTPUT to your advantage.

If you talking about an integer quantity, an identity column would be the best approach.

The probability of survival is inversely proportional to the angle of arrival.

Charmer SSChampion Points: 14903 More actions · Answer 3

DECLARE @JurisID int, @CodedID varchar(4), @CodeIDDescr varchar(60), @SrcCodeDescr varchar(60), @IsConverted int, @DecNumber bigint

here i declare @DecNumber as a variable, how do i declare it as identity column or something?

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 4

CREATE PROCEDURE Sp_Coded (@TableName varchar(15), @ColumnName varchar(10), @CodeID int, @Number bigint, @MaxLength int) AS

BEGIN

DECLARE @MySql varchar(5000)

SET @MySql = 'select 1501 as JurisID,' + Cast(@CodeID as Varchar(4)) + ' as CodedID, ' + @TableName+ '.' + @ColumnName + ' as CodeIDDescr,' + @TableName+ '.' + @ColumnName +' as SrcCodeDescr, 1 as IsConverted' +

' from dbo.' + @TableName +

' left outer join SrcCodeMap on ' + @TableName +'.'+ @ColumnName + '= SrcCodeMap.SrcCodeDescr' +

' where LEN(' +@TableName+'.'+@ColumnName + ')>' + convert(varchar,@MaxLength,20)+ ' and JurisID is null'

IF OBJECT_ID('tempDB', 'U') IS NOT NULL

DROP TABLE tempDB;

CREATE TABLE tempDB (JurisID int,CodedID varchar(4), CodeIDDescr varchar(60), SrcCodeDescr varchar(60), IsConverted int)

INSERT INTO tempDB (JurisID,CodedID,CodeIDDescr,SrcCodeDescr,IsConverted)

EXEC(@MySql)

DECLARE @JurisID int, @CodedID varchar(4), @CodeIDDescr varchar(60), @SrcCodeDescr varchar(60), @IsConverted int, @DecNumber bigint

SET @DecNumber = @Number

DECLARE MySql CURSOR FOR SELECT * FROM tempDB

OPEN Mysql

FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted

WHILE @@FETCH_STATUS = 0

BEGIN

insert into SrcCodeMap (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted) Values (@JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @DecNumber, @IsConverted)

SET @DecNumber = @DecNumber - 1

FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted

END

CLOSE Mysql

DEALLOCATE Mysql

END

here i store the @Number parameter value into the @DecNumber Variable and i use SET @DecNumber = @DecNumber - 1

to decrement the value...instead of getting the parameter value, how can i declare @DecNumber as identity column to get decremented the value for each insertion.....

Give me your idea

Thanks,
Charmer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

prakash 67108 (9/26/2011)
how do i declare it as identity column or something?

Did you actually try to understand the exemple I posted?

Charmer SSChampion Points: 14903 More actions · Answer 6

Charmer

SSChampion

Points: 14903

September 26, 2011 at 7:20 am

#1386468

sorry pal, i didn't understand that

Thanks,
Charmer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us (9/26/2011)
DECLARE @demo TABLE (ID SMALLINT IDENTITY(999, -1) PRIMARY KEY CLUSTERED, sometext VARCHAR(50)) INSERT INTO @demo (sometext) VALUES ('one') INSERT INTO @demo (sometext) VALUES ('two') INSERT INTO @demo (sometext) VALUES ('three') SELECT * FROM @demo ORDER BY ID DESC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

USE tempdb

GO

IF OBJECT_ID('tempdb..Test') IS NOT NULL DROP TABLE Test

CREATE TABLE Test (JurisID CHAR(4), CodeID CHAR(4), CodeIDDescr CHAR(4), SrcCodeDescr CHAR(4), PnxCodeId INT IDENTITY (1,1), PnxCodeValue AS (1000-PnxCodeId), IsConverted BIT)

INSERT INTO Test (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, IsConverted)

SELECT '1501', '6002', 'SOU', 'SOU', 0 UNION ALL

SELECT '1501', '6006', 'NTR', 'NTR', 0 UNION ALL

SELECT '1501', '6002', 'EA', 'EA', 0

SELECT JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted

FROM Test

DROP TABLE Test

CREATE TABLE Test (JurisID CHAR(4), CodeID CHAR(4), CodeIDDescr CHAR(4), SrcCodeDescr CHAR(4), PnxCodeValue INT IDENTITY (999,-1), IsConverted BIT)

INSERT INTO Test (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, IsConverted)

SELECT '1501', '6002', 'SOU', 'SOU', 0 UNION ALL

SELECT '1501', '6006', 'NTR', 'NTR', 0 UNION ALL

SELECT '1501', '6002', 'EA', 'EA', 0

SELECT JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted

FROM Test

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Charmer SSChampion Points: 14903 More actions · Answer 9

Charmer

SSChampion

Points: 14903

September 26, 2011 at 7:29 am

#1386480

Ya thanks Buddy... i got fixed it...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 10

but when i rerun the stored procedure with different parameter values...once again it starts with 999,998....it should be like that...whats my mistake?

Thanks,
Charmer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

prakash 67108 (9/26/2011)
but when i rerun the stored procedure with different parameter values...once again it starts with 999,998....it should be like that...whats my mistake?

We did demo code, you need to do the identity trick in your real table.