SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Incrementing a column without seting Identity property


Incrementing a column without seting Identity property

Author
Message
greeshma.patla
greeshma.patla
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 59
 

CREATE TABLE [dbo].[LKP_PARAM_TYPE](
[PARAM_TYPE_ID] [int] NOT NULL,
[PARAM_DESC] [varchar](50) NOT NULL,
CONSTRAINT [XPKLKP_PARAM_TYPE] PRIMARY KEY CLUSTERED
(
[PARAM_TYPE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[SRC_DOM_RETAIL_AIRB_PARAM](
[RISK_PARAMETERS] [varchar](20) NOT NULL,
[A_WEIGHT_IN_WITHOUT_FACTOR] [numeric](18, 2) NOT NULL
) ON [PRIMARY]

INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('pd_ec','0.00')
INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('pd_rc','0.00')
INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('eadf_ec','0.00')
INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('eadf_rc','0.00')
INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('lgd_ec','1.00')
INSERT SRC_DOM_RETAIL_AIRB_PARAM(RISK_PARAMETERS,A_WEIGHT_IN_WITHOUT_FACTOR) VALUES('lgd_rc','0.00')


DECLARE @MAX_ID INT

SELECT @MAX_ID = MAX(PARAM_TYPE_ID) FROM LKP_PARAM_TYPE

IF @MAX_ID IS NULL
SET @MAX_ID=0


MERGE INTO LKP_PARAM_TYPE AS C
USING (
SELECT
DISTINCT RISK_PARAMETERS
FROM SRC_DOM_RETAIL_AIRB_PARAM )X

) AS CT
ON C.PARAM_DESC = CT.RISK_PARAMETERS
WHEN NOT MATCHED
THEN
INSERT(PARAM_TYPE_ID,PARAM_DESC)
VALUES(@MAX_ID+1,CT.RISK_PARAMETERS);






Hi .. I have a Primary key column[PARAM_TYPE_ID] which is not identity..
i wanna set it to increment automatically using merge...Please help me in this regard..Its throwing up an error
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3679 Visits: 5175
greeshma.patla (9/11/2012)
 MERGE INTO        LKP_PARAM_TYPE AS C 
USING (
SELECT
DISTINCT RISK_PARAMETERS
FROM SRC_DOM_RETAIL_AIRB_PARAM )X -- I think this bracket is the problem, remove it

) AS CT
ON C.PARAM_DESC = CT.RISK_PARAMETERS
WHEN NOT MATCHED
THEN
INSERT(PARAM_TYPE_ID,PARAM_DESC)
VALUES(@MAX_ID+1,CT.RISK_PARAMETERS);



This looks like a problem with closing brackets, I have put a comment on the code above.
If this does not solve your problem, please post the error that you are receiving

I would also like to say that IDENTITY is the best way to achieve your requirement
Can you tell us why you are avoiding the IDENTITY property?


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7199 Visits: 6431
Looks like a really, really, really dangerous approach to me.

Use IDENTITY.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
greeshma.patla
greeshma.patla
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 59
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'XPKLKP_PARAM_TYPE'. Cannot insert duplicate key in object 'dbo.LKP_PARAM_TYPE'.
The statement has been terminated.
greeshma.patla
greeshma.patla
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 59
DBA team defined the structure and we are not supposed to change it as it is in production..



SELECT @MAX_ID = MAX(PARAM_TYPE_ID) FROM LKP_PARAM_TYPE

IF @MAX_ID IS NULL
SET @MAX_ID=0
MERGE INTO LKP_PARAM_TYPE AS C
USING (
SELECT RISK_PARAMETERS
,ROW_NUMBER()OVER(ORDER BY RISK_PARAMETERS)+@MAX_ID AS ID
FROM
(
SELECT
DISTINCT RISK_PARAMETERS
FROM SRC_DOM_RETAIL_AIRB_PARAM
)X
) AS CT
ON C.PARAM_DESC = CT.RISK_PARAMETERS
WHEN NOT MATCHED
THEN
INSERT(PARAM_TYPE_ID,PARAM_DESC)
VALUES(ID,CT.RISK_PARAMETERS);



I have gt this idea suggest me if i can do it in a better way .........
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4960 Visits: 5478

I have gt this idea suggest me if i can do it in a better way .........


The much better way would be to contact DBA (if you cannot change it yourself) and advise that you need identity there. Dwain is absolutely right.
Instead of implementing the square-weels bicycle you better use in-build feature of SQL Server designed exactly for the purpose.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3679 Visits: 5175
greeshma.patla (9/11/2012)
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'XPKLKP_PARAM_TYPE'. Cannot insert duplicate key in object 'dbo.LKP_PARAM_TYPE'.
The statement has been terminated.


With this approach, I am sure you will get this error again and again
Just imagine what would happen if somebody failed to update the PARAM_TYPE_ID column in LKP_PARAM_TYPE table with the latest value( you will get the error again )

greeshma.patla (9/11/2012)
I have gt this idea suggest me if i can do it in a better way .........


Yes. As others have also said "Use IDENTITY".


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search