問題描述

感謝任何想法/建議......

我被要求想出一種簡單的方法來導入我們從外部供應商那里收到的新數據(文本文件).我們得到幾個文本文件，每個文件都需要導入到自己的表中.某些表必須將當前/現有數據移動到名為 TABLENAME_Previous 的表中(以處理各種現有報告)，然后清空當前表并將新數據導入其中.此外，現在上一個"表中的任何數據都必須附加到存檔表中.

I've been asked to come up with a simple way to import new data we receive from an outside vendor (text files). We get several text files and each needs to be imported into its own table. Some tables have to have the current/existing data moved into a table called TABLENAME_Previous (to work with various existing reports), then have the current table emptied out and the new data imported into it. Also, any data now in the "previous" table has to be appended to an archive table.

這是一個例子:

customer.txt 來自供應商....

1. 首先我們將customers_previous的內容移動到customers_arch

接下來我們將customers的內容移動到customers_previous

Next we move the contents of customers to customers_previous

最后我們將新的 customers.txt 文件導入表 customers

Finally we import the new customers.txt file into the table customers

有沒有人寫過一個 SQL 例程來做到這一點，或者知道在哪里可以找到一個，修改起來不會太痛苦?

Has anyone ever written a SQL routine to do this, or knows where to find one, that wouldn't be too painful to modify?

謝謝

推薦答案

你可以嘗試這樣的事情:

you may try something like this:

將您以前的數據復制到存檔

To copy your previous data to Archive

Insert into customers_arch select * from customers_previous

要將您的客戶數據復制到上一個:

To Copy your Customer Data to Previous:

truncate table customers_previous;
insert into customers_previous select * from customers

然后要加載您的文本文件，請在清除后使用批量插入加載您的客戶表.

Then to Load you text file use Bulk Insert to load your customer table after clearing it.

truncate table customers;
bulk    insert customers
from    'd:\yourfolder\customers.txt'
WITH   
      (  
         FIELDTERMINATOR =',',  
         ROWTERMINATOR ='\n'  
      );

更新:好的，Brian，回答你的另一個問題，如何為保存在 WeeklyTable 中的多個文件運行它.

UPDATE: Ok, Brian, to answer your other question, How to run it for multiple files saved in your WeeklyTable.

假設你的 WeeklyTable 是這樣的:

Suppose your WeeklyTable is like this:

Declare @WeeklyTable TABLE(ID int Identity(1,1), [FileName] varchar(50))
insert into @WeeklyTable Values
('Customers'),('Orders'), ('Order_Details')

您可以創建一個動態查詢來為每個文件運行您的腳本.

You can create a dynamic query to run your script for each file.

Declare @Template varchar(max)
Set @Template = '
    -- Start of [[FILENAME]] --------------------
    Insert into [FILENAME]_arch select * from [FILENAME]_previous
    GO

    truncate table [FILENAME]_previous;
    insert into [FILENAME]_previous select * from [FILENAME]
    GO

    truncate table [FILENAME];
    bulk    insert [FILENAME]
    from    ''d:\yourfolder\[FILENAME].txt''
    WITH   
          (  
             FIELDTERMINATOR ='','',  
             ROWTERMINATOR =''\n''  
          );


'
Declare @s varchar(max)
Declare @FileName varchar(50)
Declare @ID int =0

Select TOP 1 @ID=ID, @FileName=[FileName] From @WeeklyTable Where ID>@ID order by ID
While @@ROWCOUNT>0 Begin
    Set @s = REPLACE(@Template, '[FILENAME]', @FileName)
    Print @s
--  EXEC(@s)  -- Uncomment to EXEC the script.
    Select TOP 1 @ID=ID, @FileName=[FileName] From @WeeklyTable Where ID>@ID order by ID
End

這篇關于SQL Server:導入和歸檔每周數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！