查询的时候表中的每个字段都要检查 ,也就是说所有字段的内容都要不一样,但我不想在表中设置唯一键如果是唯一鍵值可這樣做procedure TRES_DCC_ECRN_F.cxButton1Click(Sender: TObject);
var
ExcelApp,WorkBook:Olevariant;
ExcelSheetCount,i,k:Integer;
begin
inherited;
if RzButtonEdit1.Text <>'' then
begin
if (ExtractFileExt(RzButtonEdit1.Text)<>'.xls') then
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
RzButtonEdit1.Clear;
end
else
begin
try
Application.ProcessMessages;
ExcelApp:=CreateOleObject('Excel.Application');
WorkBook:=ExcelApp.WorkBooks.Open(RzButtonEdit1.Text);
ExcelApp.Visible:=False;
ExcelSheetCount:=WorkBook.WorkSheets.Count;
for i:=1 to ExcelSheetCount do
begin
Screen.Cursor:=crSQLWait;
WorkBook.WorkSheets[i].Activate;
a:=ExcelApp.Cells[4,2].Value;
b:=ExcelApp.Cells[8,2].Value;
c:=ExcelApp.Cells[14,2].Value;
d:=ExcelApp.Cells[21,2].Value;
e:=ExcelApp.Cells[22,2].Value;
f:=ExcelApp.Cells[23,2].Value;
if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then
begin
MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0);
InsertErrorLOG;//插入錯誤日誌
Screen.Cursor:=crDefault;
Exit;
end;
with adoq_ecrn do
begin
Close;
SQL.Text:='select * from RES_ECR_TYPE where ECRNO='''+ a+'''';
Open;
if RecordCount=1 then
begin
MessageDlg('請檢查:導入檔案時編號"'+ a+'"的記錄重復!',mtWarning,[mbOK],0);
InsertRepeatLOG;//插入日誌過程
Screen.Cursor:=crDefault;
Exit;
end;
end; with adoq_ecrn do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_TYPE(ECRNO,DESOFCHANGE,RELEASEDATE,RESPEOPLE,ISSUEDEPT,MODEL) values(:a,:b,:c,:d,:e,:f)');
Parameters.ParamByName('a').Value:=a;
Parameters.ParamByName('b').Value:=b;
Parameters.ParamByName('c').Value:=Trim(Copy(c,1,19));
Parameters.ParamByName('d').Value:=d;
Parameters.ParamByName('e').Value:=e;
Parameters.ParamByName('f').Value:=f;
ExecSQL;
end;
InsertECRLOG;//插入導入日誌過程 ProgressBar1.Min:=0;
ProgressBar1.Max:=ExcelSheetCount;
for k:=31 to WorkBook.WorkSheets[i].usedrange.rows.count do
begin
q:=ExcelApp.Cells[k,2].Value;
w:=ExcelApp.Cells[k,3].Value;
v:=ExcelApp.Cells[k,4].Value;
r:=ExcelApp.Cells[k,5].Value;
t:=ExcelApp.Cells[k,6].Value;
y:=ExcelApp.Cells[k,7].Value;
u:=ExcelApp.Cells[k,8].Value;
o:=ExcelApp.Cells[k,9].Value;
if (q<>'')and(w<>'') then
with adoq_item do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_ITEM(ECRNO,ITEMNO,ITEMDESPTION,FZRELATION,CHANGETYPE,BEFORECHANGE,AFTERCHANGE,PROPOSE,REMARK) values(:p,:q,:w,:v,:r,:t,:y,:u,:o)');
Parameters.ParamByName('p').Value:=a;
Parameters.ParamByName('q').Value:=q;
Parameters.ParamByName('w').Value:=w;
Parameters.ParamByName('v').Value:=v;
Parameters.ParamByName('r').Value:=r;
Parameters.ParamByName('t').Value:=t;
Parameters.ParamByName('y').Value:=y;
Parameters.ParamByName('u').Value:=u;
Parameters.ParamByName('o').Value:=o;
ExecSQL;
ProgressBar1.Position:=ProgressBar1.Position+1;
end;
end;
Screen.Cursor:=crDefault;
Application.ProcessMessages;
ProgressBar1.Position:=0;
RefreshECRN;//刷新從表
RefreshGroupItem;//刷新總表
end;
finally
ExcelApp.ActiveWorkBook.Saved:=True;
WorkBook.Close;
ExcelApp.Quit;
end;
end;
end
else
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
Exit;
end;
end;
var
ExcelApp,WorkBook:Olevariant;
ExcelSheetCount,i,k:Integer;
begin
inherited;
if RzButtonEdit1.Text <>'' then
begin
if (ExtractFileExt(RzButtonEdit1.Text)<>'.xls') then
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
RzButtonEdit1.Clear;
end
else
begin
try
Application.ProcessMessages;
ExcelApp:=CreateOleObject('Excel.Application');
WorkBook:=ExcelApp.WorkBooks.Open(RzButtonEdit1.Text);
ExcelApp.Visible:=False;
ExcelSheetCount:=WorkBook.WorkSheets.Count;
for i:=1 to ExcelSheetCount do
begin
Screen.Cursor:=crSQLWait;
WorkBook.WorkSheets[i].Activate;
a:=ExcelApp.Cells[4,2].Value;
b:=ExcelApp.Cells[8,2].Value;
c:=ExcelApp.Cells[14,2].Value;
d:=ExcelApp.Cells[21,2].Value;
e:=ExcelApp.Cells[22,2].Value;
f:=ExcelApp.Cells[23,2].Value;
if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then
begin
MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0);
InsertErrorLOG;//插入錯誤日誌
Screen.Cursor:=crDefault;
Exit;
end;
with adoq_ecrn do
begin
Close;
SQL.Text:='select * from RES_ECR_TYPE where ECRNO='''+ a+'''';
Open;
if RecordCount=1 then
begin
MessageDlg('請檢查:導入檔案時編號"'+ a+'"的記錄重復!',mtWarning,[mbOK],0);
InsertRepeatLOG;//插入日誌過程
Screen.Cursor:=crDefault;
Exit;
end;
end; with adoq_ecrn do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_TYPE(ECRNO,DESOFCHANGE,RELEASEDATE,RESPEOPLE,ISSUEDEPT,MODEL) values(:a,:b,:c,:d,:e,:f)');
Parameters.ParamByName('a').Value:=a;
Parameters.ParamByName('b').Value:=b;
Parameters.ParamByName('c').Value:=Trim(Copy(c,1,19));
Parameters.ParamByName('d').Value:=d;
Parameters.ParamByName('e').Value:=e;
Parameters.ParamByName('f').Value:=f;
ExecSQL;
end;
InsertECRLOG;//插入導入日誌過程 ProgressBar1.Min:=0;
ProgressBar1.Max:=ExcelSheetCount;
for k:=31 to WorkBook.WorkSheets[i].usedrange.rows.count do
begin
q:=ExcelApp.Cells[k,2].Value;
w:=ExcelApp.Cells[k,3].Value;
v:=ExcelApp.Cells[k,4].Value;
r:=ExcelApp.Cells[k,5].Value;
t:=ExcelApp.Cells[k,6].Value;
y:=ExcelApp.Cells[k,7].Value;
u:=ExcelApp.Cells[k,8].Value;
o:=ExcelApp.Cells[k,9].Value;
if (q<>'')and(w<>'') then
with adoq_item do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_ITEM(ECRNO,ITEMNO,ITEMDESPTION,FZRELATION,CHANGETYPE,BEFORECHANGE,AFTERCHANGE,PROPOSE,REMARK) values(:p,:q,:w,:v,:r,:t,:y,:u,:o)');
Parameters.ParamByName('p').Value:=a;
Parameters.ParamByName('q').Value:=q;
Parameters.ParamByName('w').Value:=w;
Parameters.ParamByName('v').Value:=v;
Parameters.ParamByName('r').Value:=r;
Parameters.ParamByName('t').Value:=t;
Parameters.ParamByName('y').Value:=y;
Parameters.ParamByName('u').Value:=u;
Parameters.ParamByName('o').Value:=o;
ExecSQL;
ProgressBar1.Position:=ProgressBar1.Position+1;
end;
end;
Screen.Cursor:=crDefault;
Application.ProcessMessages;
ProgressBar1.Position:=0;
RefreshECRN;//刷新從表
RefreshGroupItem;//刷新總表
end;
finally
ExcelApp.ActiveWorkBook.Saved:=True;
WorkBook.Close;
ExcelApp.Quit;
end;
end;
end
else
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
Exit;
end;
end;
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_T_ECO_PASS] Script Date: 03/14/2009 17:50:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_T_ECO_PASS]
AS
--INSERT INTO RES_ECO_PASS( SendData , ECONO , subject , EcrNo , Model , ECOCC )
--SELECT SendData , ECONO , subject , EcrNo , Model , ECOCC
--FROM dbo.T_RES_ECO_PASS
DECLARE @SendData NVARCHAR(2000) DECLARE @ECONO NVARCHAR(2000) DECLARE @SUBJECT NVARCHAR(4000) DECLARE @ECRNO NVARCHAR(2000)
DECLARE @MODEL NVARCHAR(4000) DECLARE @ECOCC NVARCHAR(2000) DECLARE @TT NVARCHAR(2000) DECLARE @TTT NVARCHAR(2000)
DECLARE @T NVARCHAR(2000) DECLARE SOLIST CURSOR FOR SELECT SendData , ECONO , subject , EcrNo , Model , ECOCC FROM T_RES_ECO_PASS
OPEN SOLIST FETCH NEXT FROM SOLIST
INTO @SendData,@ECONO,@SUBJECT,@ECRNO,@MODEL,@ECOCC
WHILE @@FETCH_STATUS=0
BEGIN
--PRINT 'AA'
SELECT @TT=@MODEL
WHILE(CHARINDEX(',',@MODEL)>0)
BEGIN
SELECT @T=LEFT(@TT,CHARINDEX(',',@TT)-1)
INSERT INTO RES_ECO_PASS( SendData , ECONO , subject , EcrNo , Model , ECOCC )
VALUES(@SendData,@ECONO,@SUBJECT,@ECRNO,@T,@ECOCC)
SELECT @TT=RIGHT(@TT,LEN(@TT)-CHARINDEX(',',@TT)-1)
SELECT @MODEL=@TT
END
INSERT INTO RES_ECO_PASS( SendData , ECONO , subject , EcrNo , Model , ECOCC )
VALUES(@SendData,@ECONO,@SUBJECT,@ECRNO,@MODEL,@ECOCC)
FETCH NEXT FROM SOLIST INTO @SendData,@ECONO,@SUBJECT,@ECRNO,@MODEL,@ECOCC
END
CLOSE SOLIST
DEALLOCATE SOLIST
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_ECR_ECO_UPDATE] Script Date: 03/14/2009 17:49:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SP_ECR_ECO_UPDATE]
AS
BEGIN
SET NOCOUNT ON;
--update<ECONO>
update a set a.ECONO=b.ECONO
from RES_ECR_TYPE a,
(
select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE
from RES_ECO_PASS
where EcrNo in (select ECRNO from RES_ECR_TYPE)
)b
where a.ECRNO=b.EcrNo
--update<ECOCC>
update a set a.ECROCC=b.ECOCC
from RES_ECR_TYPE a,
(
select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE
from RES_ECO_PASS
where EcrNo in (select ECRNO from RES_ECR_TYPE)
)b
where a.ECRNO=b.EcrNo
--update<ECORELEASEDATE>
update a set a.ECORELEASEDATE=b.ECORELEASEDATE
from RES_ECR_TYPE a,
(
select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE
from RES_ECO_PASS
where EcrNo in (select ECRNO from RES_ECR_TYPE)
)b
where a.ECRNO=b.EcrNoEND
'Excel 5.0;DATABASE=c:\book1.xls' )...[Sheet1$]
insert 目标表 select * from 中间表 where id not in (select id from 目标表) --就是判断不重复的条件drop table 中间表
方法有2.1为楼上石头说的,先导入临时表,然后对应更新和插入.2为使用SSIS.资料如下:
SSIS处理导入数据时, 存在的更新, 不存在的插入
http://blog.csdn.net/zjcxc/archive/2006/09/10/1202876.aspx3.如果还想要其他方法,得使用sql 2008了,有个MERGE 语句.资料如下:
MERGE 语句这个新增的 Transaction SQL 语句在一个基于源数据连接结果集的目标表上执行 INSERT、UPDATE 和 DELETE 操作。该语法允许您将一个数据源连接到目标表或视图上。然后在连接后的结果集上执行多种操作。MERGE 的语法为:[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ [ AS ] table_alias ]
[ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
THEN <merge_ matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
<merge_hint>::=
{ [ <table_hint_limited> [ ,...n ] ]
[ [ , ] INDEX ( index_val [ ,...n ] ) ] }<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( table_hint [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
}<merge_matched>::=
{ UPDATE SET <set_clause> | DELETE }<set_clause>::=
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name. { { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] ) } }
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
} [ ,...n ] <merge_not_matched>::=
INSERT [ ( <column_list> ) ]
{ VALUES ( <values_list> )
| DEFAULT VALUES }<output_clause>::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
[ (column_list) ] ]
[ OUTPUT <dml_select_list> ]
}
<dml_select_list>::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
[ ,...n ]<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $ACTION示例:在一条 SQL 语句中使用 WHERE 在一张表上执行 UPDATE 和 DELETE 操作USE AdventureWorks;
GO
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = GETDATE()
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
THEN DELETE;这个示例是一个非常典型的销售定货库存问题。这个示例很简单,表达的意思就是:如果某一个产品产生了销售定单数据,则
将其对应的产品库存除去该销售定单所产生的数量,如果当前库存数量与该销售定单数量相同,则从库存表中删除该产品的库存纪录。我们看到,利用 MERGE 语句可以将复杂的 SQL 语句简化。它比起 IF、CASE 等更加灵活和强大。
或许楼主可以尝试一下checksum函数 也就是生成记录的哈希值
用checksum(*)对整条记录进行操作,checksum(column)一看知道对某一个了。
如果返回的哈希值一样,那就说明两条记录或者column相等