SQL Server:导入和归档每周数据

2021-11-17 00:00:00 import sql-server archive

感谢任何想法/建议......

我被要求想出一种简单的方法来导入我们从外部供应商那里收到的新数据(文本文件).我们得到几个文本文件，每个文件都需要导入到自己的表中.某些表必须将当前/现有数据移动到名为 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

相关文章