Sql Server bulk upload flat file

In every company I have been with, there has always been a legacy app (or sometimes new) that needs to upload data from a flat file to a database. Here is a quick script that will:

1. Create a temp table
2. upload data to the temp table
3. swap the new “temp” table with the existing.
4. Keeping the old table as a backup with a clever name

First lets get the shell of your stored proc…

CREATE PROCEDURE [dbo].[bulk_upload]
       -- Add the parameters for the stored procedure here
AS
BEGIN
       -- ADD CODE HERE!
       
END

In the place marked “add code here” lets create a temp table that matches your production table. If you do not have a production table already made, please do so now.

       --create temp table

       CREATE TABLE Your_table_temp (
       [ID] [int] NOT NULL,
       [COL1] [decimal](18, 2) NULL,
       [Col2] [datetime] NULL,
       );

       --add primary key to temp
       alter table Your_table_temp add primary key(ID)

Next, the main part, is the actual upload section…add this code below… Here, you can change the column and row delimeter.

       --bulk load the data
       bulk insert Your_table_temp from 'C:\folder\flatfile.txt'
       WITH
       (FIRSTROW = 2, 
              FIELDTERMINATOR = ',',
              ROWTERMINATOR = '\n',
              TABLOCK
       )

Next lets set up some variables. These variables will help us rename the old table to a backup.

       declare @cnt int,
                     @date_ as varchar(50), 
                     @tbl_rename varchar(50),
                     @rand_num int

       select @cnt = count(*) from Your_table_temp

       select @date_ = concat(convert(varchar, year(getdate())),'-',convert(varchar, month(getdate())),'-',convert(varchar, day(getdate())))
       --@date will be something like "2021-05-14"

       SELECT @rand_num = round(RAND()*(100-5)+5,0)
       --also adding a random number, in my case this script ran multiple times a day. 
       --a better approach would be adding seconds?

       set @tbl_rename = concat('Your_table_',@date_,'_',@rand_num)

After this, do the swap ONLY IF you have data uploaded to the temp table

       if(@cnt > 0)
       BEGIN
              EXEC sp_rename 'Your_table', @tbl_rename
              EXEC sp_rename 'Your_table_temp', 'Your_table'
       END
       ELSE
       BEGIN
              drop table Your_table_temp
       END

After this runs, you should have a new table called “Your_Table” and a backup named something like “Your_Table_2021-05-14_41”

From here you can schedule this task to run whenever you want.

Here is all the code put together….

CREATE PROCEDURE [dbo].[bulk_upload]
       -- Add the parameters for the stored procedure here
AS
BEGIN
       SET NOCOUNT ON;

       --create temp table

       CREATE TABLE Your_table_temp (
       [ID] [int] NOT NULL,
       [COL1] [decimal](18, 2) NULL,
       [Col2] [datetime] NULL,
       );

       --add primary key to temp
       alter table Your_table_temp add primary key(ID)
       

       --bulk load the data
       bulk insert Your_table_temp from 'C:\folder\flatfile.txt'
       WITH
       (FIRSTROW = 2, 
              FIELDTERMINATOR = ',',
              ROWTERMINATOR = '\n',
              TABLOCK
       )

       --if the temp has data, then rename the tables, else drop the new temp table
       declare @cnt int,
                     @date_ as varchar(50), 
                     @tbl_rename varchar(50),
                     @rand_num int

       select @cnt = count(*) from Your_table_temp
       select @date_ = concat(convert(varchar, year(getdate())),'-',convert(varchar, month(getdate())),'-',convert(varchar, day(getdate())))
       SELECT @rand_num = round(RAND()*(100-5)+5,0)
       set @tbl_rename = concat('Your_table_',@date_,'_',@rand_num)

       if(@cnt > 0)
       BEGIN
              EXEC sp_rename 'Your_table', @tbl_rename
              EXEC sp_rename 'Your_table_temp', 'Your_table'
       END
       ELSE
       BEGIN
              drop table Your_table_temp
       END
END

Comment if you have any questions or improvements 🙂