先寫入暫存資料表,然後再利用distinct 或group方式過濾重複資料,再匯入資料表中。
請問如果我要用自動執行方式來做該怎麼下指令呢?要使用Transact-SQL來做嗎?
你是用SSIS去做的嗎?
不是,我是用網站http://www.dotblogs.com.tw/dc690216/archive/2011/01/26/21068.aspx方法來匯入考勤資料
例如:
<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
<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
我是用以上方式將考勤文字檔讀取後直接進資料庫
那就改 set @sql= 'BULK INSERT dbo.myabsent FROM'+@path+@sqlcontrol
將資料表dbo.myabsent改為暫存資料表,當然在執行BULK INSERT之前要先建立暫存資料表,結構要和你目標表格一樣,之後再如法炮製即可。
您好!
我建立了#Tmp暫存資料表結構與dbo.myabsent相同,然後將dbo.myabsent改為dbo.#Tmp,執行出現"無效的物件名稱#Tmp"
@@"
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)
)
<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存在@@"
也沒有文字檔匯入的訊息@@"
tempdb 是在系統資料庫中,另外你沒寫到寫入你目標資料表的語法啊?
請參考我第一個範例改成你需要的。
下段程式碼的用意
@sql 變數就是你要執行的語法 你得要加上去才行
執行語法
exec sp_executesql @sql
<pre class="c" name="code">set @sql= 'BULK INSERT #Tmp FROM'+@path+@sqlcontrol
exec sp_executesql @sql
<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
)
)
多了一個)
set @sql= 'BULK INSERT #Tmp FROM'+@path+@sqlcontrol
exec sp_executesql @sql
小弟駑鈍@@",奇怪的是我執行sqlcmd在tempdb沒有發現#Tmp資料表,但是如果在一般SQL查詢中執行又會出現#Tmp資料表?所以不知道這是甚麼問題?
因為#Tmp是暫存資料表所以不是在資料表中,是歸屬在暫存資料表
<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
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
test.txt內容:
1,John,111111
1,John,111111
2,Mary,1123123
1,John,111111
4,Joey,1133213
1,John,111111
4,Joey,1133213
試過這樣的方式是可以跑的,如果還有錯,要看你的錯誤訊息是什麼。
<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' 之處的語法不正確。
<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
(下半部語法)
我這邊看是另一個錯誤訊息,
不過就針對你的錯誤,應該是這段
BULK INSERT #Tmp FROM'+@path+@sqlcontrol
=>
BULK INSERT #Tmp FROM '+@path+@sqlcontrol
FROM沒有空格
你自己執行T-SQL
print @sql 就可以知道整段語法對不對哪裡錯了
還有你後面的條件都沒有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
我耍白癡了沒AND@@"~感謝大大耐心指導~可以正確執行了~但是目標表格資料會有重複相同的考勤資料~不知道是哪裡問題?再次感恩
是不是目標資料也要再比對一次?
有先檢查過資料時間嗎?
或許是之前測試已寫入的重複資料?
若你想要測試你直接執行T-SQL就可以確認是否寫入目標資料表的資料會重複了
我有將資料表都清空,現在執行後的一個問題是tmp資料表每次都會保留最新的考勤資料,然後tmp資料表會每次insert into 到目標資料表中,目標資料表就會一直累積temp來的資料(包含重複資料)。
我覺得應該是時間欄位的問題
如果你原本是
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 中所查詢的欄位以及條件中的欄位就應該剔除掉。
否則查詢出來仍會存在你所謂的重複資料
增加鍵值
來達到不重複
不用去刪除