問(wèn)題描述

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

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

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.

這是一個(gè)例子:

customer.txt 來(lái)自供應(yīng)商....

1. 首先我們將customers_previous的內(nèi)容移動(dòng)到customers_arch

接下來(lái)我們將customers的內(nèi)容移動(dòng)到customers_previous

Next we move the contents of customers to customers_previous

最后我們將新的 customers.txt 文件導(dǎo)入表 customers

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

有沒(méi)有人寫過(guò)一個(gè) SQL 例程來(lái)做到這一點(diǎn)，或者知道在哪里可以找到一個(gè)，修改起來(lái)不會(huì)太痛苦?

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:

將您以前的數(shù)據(jù)復(fù)制到存檔

To copy your previous data to Archive

Insert into customers_arch select * from customers_previous

要將您的客戶數(shù)據(jù)復(fù)制到上一個(gè):

To Copy your Customer Data to Previous:

truncate table customers_previous;
insert into customers_previous select * from customers

然后要加載您的文本文件，請(qǐng)?jiān)谇宄笫褂门坎迦爰虞d您的客戶表.

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，回答你的另一個(gè)問(wèn)題，如何為保存在 WeeklyTable 中的多個(gè)文件運(yùn)行它.

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

假設(shè)你的 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')

您可以創(chuàng)建一個(gè)動(dòng)態(tài)查詢來(lái)為每個(gè)文件運(yùn)行您的腳本.

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

這篇關(guān)于SQL Server:導(dǎo)入和歸檔每周數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！