• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Amend script - continue on duplicate?


I have a script that inserts data in to a table based on the query run prior to the insert. The script is brilliant and works perfectly but I would like to amend it so it will continue if there is already a record in the system the same as the one being entered.

The script is attached, I got it from here in the first place but it's beyond me to amend it with my knowledge.

Any help would be much appreciated.

Best Regards,

--adjust to make the COL refs predictably 2 char
set @start = upper(case when @start like '[A-Z][0-99]%' then '@' else '' end +@start)
set @end = upper(case when @end like '[A-Z][0-99]%' then '@' else '' end +@end)

-- this inserts into table named "Grid"
insert blockdirectory (countryCode ,cityCode ,storeCode ,storeType , gridCodeX , gridCodeY)

select @countryCode, @cityCode, @storeCode, @storeType, REPLACE(COL,'@','') + right(ROW,2), REPLACE(COL,'@','') + right(ROW,2)
	-- this inner query generates all the references from @A1 through ZZ99
	-- the @ is a placeholder for single-letter columns
		char(a.number)+coalesce(char(b.number),'') as COL
		,d.number as ROW
	from master..spt_values a
	left join master..spt_values b on b.type='P' and b.number between 65 and 90
	inner join master..spt_values d on d.type='P' and d.number between 1 and 99
	where a.type='P' and a.number between 64 and 90
) F

-- filter for the range of COL/ROW required
where COL between left(@start,2) and left(@end,2)
and ROW between substring(@start,3,3) and substring(@end,3,3)

update blockdirectory
set dmsActive = 'False'
Where dmsActive IS NULL

Open in new window

  • 3
  • 2
1 Solution
Do mean blockdirectory is not inserted data when it is having the same row? You should get an error right? Do you have a primary key in the table?
kenuk110Author Commented:
Hi, yes that's correct, the script stops  if it finds a duplicate key. I'm in the office in an hour, I'll update the primary key, I believe it's called pk but I'll double check.

Thanks for your response.

kenuk110Author Commented:
Hi again,

I've just checked and the primary key is called 'pk' without the quotes. This field is set to have Identity Specification = Yes with Auto Increment and Seed set to 1. I have an index (unique) on countryCode, cityCode, storeCode, storeType and gridCodeX also.

Hope this helps?


Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Aaron ShiloCommented:
under the primary key and unique index properties check the Ignore Duplicates option.

kenuk110Author Commented:

Thanks for the response. I found what you mentioned but I can't change the value from No to Yes, it's greyed out. Any ideas?

Thanks again
Aaron ShiloCommented:
well if your sql server version is not enterprise then your out of luck.

whats left for you is to :

1. save query results to a temp table.
2. check for duplicates using a simple join and delete them from the temp table
3. add the rest of the data to the table.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now