如何在SQL Server 2008中创建自定义唯一约束 [英] How can I Create Custom Unique Constraint in SQL Server 2008

问题描述

嗨朋友们，



我有两张桌子：

1. ProductDetail ：

Hi Friends,

I have two tables:
1. ProductDetail:

PrKID INT,
IMEI1 VARCHAR(20),
IMEI2 VARCHAR(20),
IMEI3 VARCHAR(20),
IMEI4 VARCHAR(20)

和

2. IMEIUpload ：

and
2. IMEIUpload:

IMPkID,
IMEI1 VARCHAR(20),
IMEI2 VARCHAR(20),
IMEI3 VARCHAR(20),
IMEI4 VARCHAR(20)

我用Excel上传 IMEI s（列名为 [IMEI1，IMEI2，IMEI3，IMEI4] ）



我检查 IMEI s已经存在，所以上传的性能非常慢

我怎么能增加这个???





请帮帮我??



Shreeniwas Kushwah

I Uploads IMEIs with Excel( Column Names as [IMEI1, IMEI2, IMEI3, IMEI4])

I check IMEIs in both tables to already existing so performance of upload is very slow
how can i increase this???


Please Help Me??

Shreeniwas Kushwah

推荐答案

请，首先阅读我的评论。



查看示例查询：

Please, read my comment first.

Have a look at example query:

--declare variable; temporary table 
DECLARE @tmpIms TABLE (IMEI1 VARCHAR(20),
    IMEI2 VARCHAR(20),
    IMEI3 VARCHAR(20),
    IMEI4 VARCHAR(20))

--insert values from Excel into temporary table
INSERT INTO @tmpIms (IMEI1, IMEI2, IMEI3, IMEI4)
SELECT IMEI1, IMEI2, IMEI3, IMEI4  
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\ExcelFileName.xls', 'SELECT * FROM [Sheet1

'） AS DT

- 仅查看新的IMEI
SELECT *
FROM @ tmpIms
WHERE IMEI1 NOT IN （
SELECT IMEI1
FROM ProductDetails AS PD INNER JOIN @ tmpIms AS TI ON PD.IMEI1 = TI.IMEI1 AND PD.IMEI2 = TI.IMEI2 AND PD.IMEI3 = TI.IMEI3 AND PD.IMEI4 = TI .IMEI4
）

') AS DT --view only new IMEI's SELECT * FROM @tmpIms WHERE IMEI1 NOT IN ( SELECT IMEI1 FROM ProductDetails AS PD INNER JOIN @tmpIms AS TI ON PD.IMEI1 = TI.IMEI1 AND PD.IMEI2 = TI.IMEI2 AND PD.IMEI3 = TI.IMEI3 AND PD.IMEI4 = TI.IMEI4 )

并根据需要更改代码;）

and change the code to your needs ;)

这篇关于如何在SQL Server 2008中创建自定义唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！