Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Incrementing a column without seting Identity property Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 12:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 1:43 AM
Points: 71, 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
Post #1357154
Posted Tuesday, September 11, 2012 1:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 11:15 PM
Points: 2,677, Visits: 4,742
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/
Post #1357176
Posted Tuesday, September 11, 2012 2:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
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!
Post #1357199
Posted Tuesday, September 11, 2012 2:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 1:43 AM
Points: 71, 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.
Post #1357208
Posted Tuesday, September 11, 2012 3:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 1:43 AM
Points: 71, 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 .........
Post #1357211
Posted Tuesday, September 11, 2012 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1357219
Posted Tuesday, September 11, 2012 3:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 11:15 PM
Points: 2,677, Visits: 4,742
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/
Post #1357228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse