iT邦幫忙

0

將考勤文字檔匯入SQL2005問題？

sql
badboy01 2014-09-16 10:23:34 ‧ 3772 瀏覽

  • 分享至 

  • twitterImage

請教，我將考勤文字檔資料匯入SQL 2005資料庫，但是發現因為我5分鐘會抓一次考勤文字資料，導致資料庫出現過多重複的考勤資料，我該如何再匯入考勤資料前可以自動刪除重複資料後進到資料庫中？謝謝！

魯大 iT邦高手 1 級 ‧ 2014-09-16 13:23:40 檢舉
你可以使用Trigger做資料insert的篩選
已存在的就不insert，不存在的才insert
badboy01 iT邦新手 2 級 ‧ 2014-09-23 17:30:16 檢舉
looney提到：
Trigger做資料insert的篩選

這個方法對還是初學SQL小弟來說太複雜了@@"
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

2
bluesky1213
iT邦新手 3 級 ‧ 2014-09-16 10:52:14
最佳解答

先寫入暫存資料表，然後再利用distinct 或group方式過濾重複資料，再匯入資料表中。

看更多先前的回應...收起先前的回應...
badboy01 iT邦新手 2 級 ‧ 2014-09-16 11:26:18 檢舉

請問如果我要用自動執行方式來做該怎麼下指令呢？要使用Transact-SQL來做嗎?

bluesky1213 iT邦新手 3 級 ‧ 2014-09-16 13:00:51 檢舉

你是用SSIS去做的嗎?

badboy01 iT邦新手 2 級 ‧ 2014-09-16 13:20:46 檢舉

不是，我是用網站http://www.dotblogs.com.tw/dc690216/archive/2011/01/26/21068.aspx方法來匯入考勤資料

bluesky1213 iT邦新手 3 級 ‧ 2014-09-16 13:54:10 檢舉

例如:

<pre class="c" name="code">IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL
    DROP TABLE [#Temp]

CREATE TABLE #Temp
(
      ID INT ,
      Name NVARCHAR(30),
      Phone  NVARCHAR(30)
)

INSERT INTO #Temp(ID,Name, Phone)
VALUES(2,'Mary', '0800111111')
INSERT INTO #Temp(ID,Name, Phone)
VALUES(1,'John', '0800000000')
INSERT INTO #Temp(ID,Name, Phone)
VALUES(1,'John', '0800000000')

SELECT DISTINCT ID,Name,Phone FROM #Temp

---建立目標資料表(測試用)
USE test
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'TargetTable' )
	CREATE TABLE TargetTable
	(
		  ID INT ,
		  Name NVARCHAR(30),
		  Phone  NVARCHAR(30)
	)
GO

INSERT INTO TargetTable
SELECT DISTINCT ID,Name,Phone 
FROM #Temp S
WHERE NOT EXISTS (
	SELECT ID,Name,Phone  
	FROM TargetTable T
    WHERE S.ID = T.ID AND S.Name = T.Name AND S.Phone = T.Phone
)

SELECT * FROM TargetTable
badboy01 iT邦新手 2 級 ‧ 2014-09-16 14:26:57 檢舉 
<pre class="c" name="code">USE TEST
declare @path nvarchar(max) 
declare @sql nvarchar(max) 
declare @sqlcontrol nvarchar(max) 
set @path='''C:\考勤\'+CONVERT(varchar(8) , getdate(), 112 )+'.txt''' 
set @sqlcontrol='WITH (DATAFILETYPE = XXXXXXX FIELDTERMINATOR = XXXXXXX,'+' ROWTERMINATOR = XXXXXXXX)' 
set @sql= 'BULK INSERT dbo.myabsent FROM'+@path+@sqlcontrol 
exec sp_executesql @sql

我是用以上方式將考勤文字檔讀取後直接進資料庫

bluesky1213 iT邦新手 3 級 ‧ 2014-09-16 14:42:55 檢舉

那就改 set @sql= 'BULK INSERT dbo.myabsent FROM'+@path+@sqlcontrol
將資料表dbo.myabsent改為暫存資料表，當然在執行BULK INSERT之前要先建立暫存資料表，結構要和你目標表格一樣，之後再如法炮製即可。

badboy01 iT邦新手 2 級 ‧ 2014-09-16 17:22:54 檢舉

您好！
我建立了#Tmp暫存資料表結構與dbo.myabsent相同，然後將dbo.myabsent改為dbo.#Tmp，執行出現"無效的物件名稱#Tmp"
@@"

bluesky1213 iT邦新手 3 級 ‧ 2014-09-16 17:53:26 檢舉

BULK INSERT dbo.myabsent FROM

"dbo."去掉
還有條件建立暫存表時如果存在要先移除。

<pre class="c" name="code">IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL
    DROP TABLE [#Temp]

CREATE TABLE #Temp
(
      ID INT ,
      Name NVARCHAR(30),
      Phone  NVARCHAR(30)
)
badboy01 iT邦新手 2 級 ‧ 2014-09-17 09:47:19 檢舉 
<pre class="c" name="code">
USE TEST

declare @path nvarchar(max) 
declare @sql nvarchar(max) 
declare @sqlcontrol nvarchar(max) 
set @path='''C:\考勤\'+CONVERT(varchar(8) , getdate(), 112 )+'.txt''' 
set @sqlcontrol='WITH (DATAFILETYPE = XXXXXXXXX''''+','+' FIELDTERMINATOR = 'XXXXXXXXX'+','+' ROWTERMINATOR = 'XXXXXXXX')' 


IF OBJECT_ID('tempdb.dbo.#Tmp') IS NOT NULL
    DROP TABLE [#Tmp]

CREATE TABLE #Tmp
(
	date_attendance datetime ,
	test NVARCHAR(50),
	CardID NVARCHAR(50),
	Commuting_time NVARCHAR(50),
	employeeID NVARCHAR(50),
	NameID NVARCHAR(50),
	Dept NVARCHAR(50)
)

set @sql= 'BULK INSERT #Tmp FROM'+@path+@sqlcontrol 
exec sp_executesql @sql

我按照大大語法放進去執行，訊息有出現"7個受影響的資料列",但是我到資料庫中卻發現沒有#Tmp存在@@"

badboy01 iT邦新手 2 級 ‧ 2014-09-17 10:05:49 檢舉

也沒有文字檔匯入的訊息@@"

bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 11:36:19 檢舉

tempdb 是在系統資料庫中，另外你沒寫到寫入你目標資料表的語法啊?
請參考我第一個範例改成你需要的。
下段程式碼的用意
@sql 變數就是你要執行的語法 你得要加上去才行
執行語法
exec sp_executesql @sql

<pre class="c" name="code">set @sql= 'BULK INSERT #Tmp FROM'+@path+@sqlcontrol   
exec sp_executesql @sql

http://msdn.microsoft.com/zh-tw/library/ms188001.aspx

bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 11:41:17 檢舉 
<pre class="c" name="code">INSERT INTO 目標表格名稱  
SELECT DISTINCT date_attendance,
    test,
    CardID,
    Commuting_time,
    employeeID,
    NameID,
    Dept  
FROM #Tmp S  
WHERE NOT EXISTS (  
    SELECT ID,Name,Phone    
    FROM 目標表格名稱 T  
    WHERE S.date_attendance = T.date_attendance 
    AND S.test = T.test 
    AND S.CardID = T.CardID  
    AND S.Commuting_time = T.Commuting_time  
    AND S.employeeID = T.employeeID 
    AND S.NameID = T.NameID 
    AND S.Dept = T.Dept 
)  
)
bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 11:41:42 檢舉

多了一個)

badboy01 iT邦新手 2 級 ‧ 2014-09-17 14:50:00 檢舉

set @sql= 'BULK INSERT #Tmp FROM'+@path+@sqlcontrol
exec sp_executesql @sql

小弟駑鈍@@",奇怪的是我執行sqlcmd在tempdb沒有發現#Tmp資料表，但是如果在一般SQL查詢中執行又會出現#Tmp資料表？所以不知道這是甚麼問題？

bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 15:19:34 檢舉

因為#Tmp是暫存資料表所以不是在資料表中，是歸屬在暫存資料表

bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 15:25:29 檢舉 
<pre class="c" name="code">set @sql= N'IF OBJECT_ID(''tempdb.dbo.#Tmp'') IS NOT NULL
    DROP TABLE [#Tmp]

	CREATE TABLE #Tmp
	(
		date_attendance datetime ,
		test NVARCHAR(50),
		CardID NVARCHAR(50),
		Commuting_time NVARCHAR(50),
		employeeID NVARCHAR(50),
		NameID NVARCHAR(50),
		Dept NVARCHAR(50)
	) 
	BULK INSERT #Tmp FROM'+@path+@sqlcontrol 
exec sp_executesql @sql
bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 15:47:05 檢舉

EX:

<pre class="c" name="code">USE TEST

declare @path nvarchar(max) 
declare @sql nvarchar(max) 
declare @sqlcontrol nvarchar(max) 
set @path='''D:\test.txt''' 
set @sqlcontrol='WITH (FIELDTERMINATOR = '','')' 

set @sql= N'IF OBJECT_ID(''tempdb.dbo.#Tmp'') IS NOT NULL
    DROP TABLE [#Tmp]

	CREATE TABLE #Tmp
	(
		ID int,
		Name NVARCHAR(50),
		Phone NVARCHAR(50)
	) 
	BULK INSERT #Tmp FROM'+@path+@sqlcontrol
	+' INSERT INTO test  
SELECT DISTINCT ID,Name,Phone   
FROM #Tmp S  
WHERE NOT EXISTS (  
    SELECT ID,Name,Phone    
    FROM test T  
    WHERE S.ID = T.ID 
    AND S.Name = T.Name 
    AND S.Phone = T.Phone 
)  '
exec sp_executesql @sql
bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 15:47:39 檢舉

test.txt內容:
1,John,111111
1,John,111111
2,Mary,1123123
1,John,111111
4,Joey,1133213
1,John,111111
4,Joey,1133213

bluesky1213 iT邦新手 3 級 ‧ 2014-09-17 15:48:54 檢舉

試過這樣的方式是可以跑的，如果還有錯，要看你的錯誤訊息是什麼。

badboy01 iT邦新手 2 級 ‧ 2014-09-18 08:55:22 檢舉 
<pre class="c" name="code">
USE TEST

declare @path nvarchar(max) 
declare @sql nvarchar(max) 
declare @sqlcontrol nvarchar(max)
 
set @path='''C:\考勤\'+CONVERT(varchar(8) , getdate(), 112 )+'.txt''' 
set @sqlcontrol='WITH (DATAFILETYPE = '''+'XXXXXXX'+''''+','+' FIELDTERMINATOR = '''+','+'XXXXXX'+','+' ROWTERMINATOR = '+'XXXXXXXX'+')' 
set @sql='IF OBJECT_ID(''tempdb.dbo.#Tmp'') IS NOT NULL
    DROP TABLE #Tmp

	CREATE TABLE #Tmp
	(
		date_attendance datetime,
		test NVARCHAR(50),
		CardID NVARCHAR(50),
		Commuting_time NVARCHAR(50),
		employeeID NVARCHAR(50),
		NameID NVARCHAR(50),
		Dept NVARCHAR(50)
	)

大大您好！
出現一個錯誤訊息(先貼上半部語法)
訊息 102, 層級 15, 狀態 1, 伺服器 XXX-DC, 行 21
接近 '#Tmp' 之處的語法不正確。

badboy01 iT邦新手 2 級 ‧ 2014-09-18 08:56:51 檢舉 
<pre class="c" name="code">
        BULK INSERT #Tmp FROM'+@path+@sqlcontrol
	+'INSERT INTO TABLE_1
SELECT DISTINCT date_attendance,Test,CardID,Commuting_time,employeeID,NameID,Dept
FROM #Tmp
WHERE NOT EXISTS (
	SELECT date_attendance,Test,CardID,Commuting_time,employeeID,NameID,Dept
	FROM TABLE_1
	WHERE 	#Tmp.date_attendance = TABLE_1.date_attendance
	        #Tmp.test = TABLE_1.test
		#Tmp.CardID = TABLE_1.CardID
		#Tmp.Commuting_time = TABLE_1.Commuting_time
		#Tmp.employeeID = TABLE_1.employeeID
		#Tmp.NameID = TABLE_1.NameID
		#Tmp.Dept = TABLE_1.Dept
)'
exec sp_executesql @sql

(下半部語法)

bluesky1213 iT邦新手 3 級 ‧ 2014-09-18 11:09:41 檢舉

我這邊看是另一個錯誤訊息，
不過就針對你的錯誤，應該是這段
BULK INSERT #Tmp FROM'+@path+@sqlcontrol
=>
BULK INSERT #Tmp FROM '+@path+@sqlcontrol

FROM沒有空格
你自己執行T-SQL
print @sql 就可以知道整段語法對不對哪裡錯了

bluesky1213 iT邦新手 3 級 ‧ 2014-09-18 11:14:56 檢舉

還有你後面的條件都沒有AND阿

<pre class="c" name="code"> WHERE   #Tmp.date_attendance = TABLE_1.date_attendance  
       AND #Tmp.test = TABLE_1.test  
       AND  #Tmp.CardID = TABLE_1.CardID  
       AND  #Tmp.Commuting_time = TABLE_1.Commuting_time  
       AND   #Tmp.employeeID = TABLE_1.employeeID  
       AND  #Tmp.NameID = TABLE_1.NameID  
       AND  #Tmp.Dept = TABLE_1.Dept
badboy01 iT邦新手 2 級 ‧ 2014-09-18 12:01:59 檢舉

我耍白癡了沒AND@@"~感謝大大耐心指導~可以正確執行了~但是目標表格資料會有重複相同的考勤資料~不知道是哪裡問題？再次感恩

badboy01 iT邦新手 2 級 ‧ 2014-09-18 12:08:05 檢舉

是不是目標資料也要再比對一次？

bluesky1213 iT邦新手 3 級 ‧ 2014-09-18 13:39:40 檢舉

有先檢查過資料時間嗎?
或許是之前測試已寫入的重複資料?
若你想要測試你直接執行T-SQL就可以確認是否寫入目標資料表的資料會重複了

badboy01 iT邦新手 2 級 ‧ 2014-09-18 14:28:26 檢舉

我有將資料表都清空，現在執行後的一個問題是tmp資料表每次都會保留最新的考勤資料,然後tmp資料表會每次insert into 到目標資料表中,目標資料表就會一直累積temp來的資料(包含重複資料)。

bluesky1213 iT邦新手 3 級 ‧ 2014-09-18 15:13:34 檢舉

我覺得應該是時間欄位的問題
如果你原本是
ID Name DateTime
A John '2014/09/18 14:00:05'
A John '2014/09/18 14:00:15'
A John '2014/09/18 14:04:15'
那麼這3筆資料還是會寫入
因為我給你的條件式所有欄位都加入
也就是每一筆資料去比對所有欄位都相同的話才不寫入，
但我不清楚你所謂重複資料室包含時間也一樣嗎?
如果你是依據人的話你的條件式就應該替除時間欄位，
所以你在NOT EXISTS 中所查詢的欄位以及條件中的欄位就應該剔除掉。
否則查詢出來仍會存在你所謂的重複資料

badboy01 iT邦新手 2 級 ‧ 2014-09-18 15:41:07 檢舉

恩~因為我考勤資料為5分鐘自動抓取，但可能這段時間沒有新的刷卡資料，所以就會欄位完全相同的資料(時間欄位完全相同)都進到資料庫中，可是在NOT EXISTS欄位我都拉進去了。

bluesky1213 iT邦新手 3 級 ‧ 2014-09-19 14:02:54 檢舉

你的資料庫是Test?
我是因為測試所以用USE TEST
你有確認過資料表是否是同一個資料庫嗎?

badboy01 iT邦新手 2 級 ‧ 2014-09-23 13:41:15 檢舉

我是用正確名稱，是因為post上去改test,該如何寄檔案給大大呢?

登入發表回應
2
jouabcd
iT邦新手 1 級 ‧ 2014-09-16 10:54:35

增加鍵值

來達到不重複

不用去刪除

badboy01 iT邦新手 2 級 ‧ 2014-09-16 12:04:49 檢舉

因為我五分鐘會抓一次考勤資料，如果不刪除將會造成大量重複資料存在。

mis2000lab iT邦好手 1 級 ‧ 2014-09-18 15:23:11 檢舉

只把異動的那些資料作處理，不
要批次（全部）重做一次

badboy01 iT邦新手 2 級 ‧ 2014-09-18 15:29:44 檢舉

您好！
該如何下語法異動的資料去做處理呢？
謝謝！

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙