September 11, 2012 at 12:25 am
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
September 11, 2012 at 1:38 am
greeshma.patla (9/11/2012)
MERGE INTO LKP_PARAM_TYPE AS CUSING (
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?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 11, 2012 at 2:46 am
Looks like a really, really, really dangerous approach to me.
Use IDENTITY.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 11, 2012 at 2:51 am
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.
September 11, 2012 at 3:00 am
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 .........
September 11, 2012 at 3:11 am
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.
September 11, 2012 at 3:25 am
greeshma.patla (9/11/2012)
Msg 2627, Level 14, State 1, Line 10Violation 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".
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply