SQL Server：导入和存档每周数据 [英] SQL Server: Importing and archiving weekly data

问题描述

任何想法/建议表示赞赏....

Any ideas/suggestions appreciated....

我被要求提出从外部供应商（文本文件）导入新数据的简单方法。我们得到几个文本文件，每个文件都需要导入到自己的表中。有些表必须将当前/现有数据移动到名为 TABLENAME_Previous 的表中（以处理各种现有报告），然后清空当前表并导入新数据进去。此外，现在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 来自供应商....

customer.txt comes in from vendor....

1. 首先我们移动 customers_previous 到 customers_arch

接下来我们移动客户的内容到 customers_previous

最后我们导入新的 customers.txt 表格中的文件客户

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

然后加载文本文件使用Bulk Insert to清除后加载客户表。

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：导入和存档每周数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！