SQL Server: Importing and archiving weekly data

Any ideas/suggestions appreciated....

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.

Here's an example:

customer.txt comes in from vendor....

First we move the contents of customers_previous to customers_arch
Next we move the contents of customers to customers_previous
Finally we import the new customers.txt file into the table customers

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

Thanks

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'  
      );

UPDATE: Ok, Brian, to answer your other question, How to run it for multiple files saved in your 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