Amend script - continue on duplicate?

Posted on 2011-05-07
Last Modified: 2012-05-11

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

Question by:kenuk110
    LVL 9

    Expert Comment

    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?

    Author Comment

    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.


    Author Comment

    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?


    LVL 15

    Expert Comment

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


    Author Comment


    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
    LVL 15

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now