I am trying to find the record that already exists, and I get the Msg 128, Level 15, State 1 error.

Question

I am trying to find the record that already exists, and I get the Msg 128, Level 15, State 1 error.

sslaven50

SSC Enthusiast

Points: 117
More actions
November 30, 2015 at 7:10 am

#322933

if EXISTS (select tm.mbrs_id from temp_Mbrship tm
join mbrship m
on tm.mbrs_id = m.mbrs_id)
Print 'Record already exists in mbrship table' + mbrs_id
-- error -- Msg 128, Level 15, State 1, Line 5
-- The name "mbrs_id" is not permitted in this context. Valid expressions are constants, constant
-- expressions, and (in some contexts) variables. Column names are not permitted.
-- Is there a way to get this value into the print statement?
ELSE
insert into Mbrship(
mbrs_id,
mbrs_sts_cd,
mbrs_expir_dt,
mbrs_crd_expir_dt,
mbrs_canc_dt,
mbrs_dnr_ren_cd,
mbrs_dues_cost_at,
mbrs_ent_at,
mbrs_pmt_apply_at,
mbrs_pmt_pend_at,
mbrs_ren_meth_cd,
mbrs_bil_cat_cd,
brn_ky,
mbrs_kit_issd_in,
mbrs_cred_apply_at,
mbrs_cred_pend_at,
mbrs_curr_effect_dt)
select
mbrs_id,
mbrs_sts_cd,
mbrs_expir_dt,
mbrs_expir_dt,
mbrs_canc_dt,
mbrs_dnr_ren_cd,
mbrs_dues_cost_at,
mbrs_ent_at,
mbrs_pmt_apply_at,
mbrs_pmt_pend_at,
mbrs_ren_meth_cd = CASE(mbrs_ren_meth_cd)
WHEN 'Y' THEN 'A'
ELSE 'B'
END,
'',
0,
'Y',
0,
0,
CASE(mbrs_sts_cd)
WHEN 'P' THEN mbrs_expir_dt
ELSE DATEADD(YEAR, -1, mbrs_expir_dt)
END
from temp_Mbrship
WHERE mbrs_id in ('00040687','00001010','00002120','00002640','00022121','00032690','00037870',
'00041367','00046090','00050060','00041760','00043970','00065860',
'00066331','00085100')

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not a quick fix.

Your code has a race condition. Two sessions could execute this at the same time, both run the SELECT and find no rows, then both run the insert and either insert duplicates or one gets a duplicate key error. Plus, an EXISTS checks whether there's a row or not, you're inserting multiple, so the exists will only work if none of the rows you're about to insert are present.

You should include the EXISTS check (or NOT EXISTS more correctly) as part of the insert statement, not as a separate statement beforehand, or you need to run the two in a transaction with an UPDLOCK hint on the first select.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

your error is your print statement, you cannot print a column's value.

PRINT 'Record already exists in mbrship table' + mbrs_id

you have to print something static or a variable like @mbrs_id that you'd need to declare and populate.

it also looks like you might exclude a other inserts because at least one row has a match in temp.

i would join temp tot eh table and only insert items that did not exist, like this:

INSERT INTO Mbrship(...)

SELECT (...)

FROM temp_Mbrship tm

[highlight="#ffff11"] LEFT JOIN mbrship m

ON tm.mbrs_id = m.mbrs_id[/highlight]

WHERE tm.mbrs_id IN ( '00040687', '00001010', '00002120', '00002640',

'00022121', '00032690', '00037870', '00041367',

'00046090', '00050060', '00041760', '00043970',

'00065860', '00066331', '00085100' )

[highlight="#ffff11"]AND m.mbrs_id IS NULL [/highlight]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!