Import & Export Data Part 2

The BULK INSERT statement imports formatted data directly into a table or view of your choosing. The main advantage of this statement is that is minimally logged if the correct recovery model is choosen. Peforming a transaction log backup after each bulk insert reclaims the log space that was used. This statment has many parameters that can alter how the statement executes. Today, I am going to demonstrate the parameters that I think are most useful.

We will be working again with the Boy Scouts of America (BSA) hypothetical database. The first step when creating a nightly load process is to create a full database backup and change the recovery model to bulk .

The code snipet below performs the backup using a 7 day file name rotation. This is just a preview of things to come. I will be fully exploring database maintenance and backups in the future.

<span style="color: #008000;"> -- DECLARE VARIABLES
DECLARE @VAR_DAY VARCHAR(3);
DECLARE @VAR_PATH VARCHAR(125);
DECLARE @VAR_NAME VARCHAR(125);
DECLARE @VAR_FILE VARCHAR(250);
DECLARE @VAR_DESC VARCHAR(250);

-- TARGET DIRECTORY & NAME
SET @VAR_PATH = 'C:\MSSQL\BACKUP';  
SET @VAR_NAME = 'BSA';  
  
-- WEEKDAY BACKUP PLAN
SELECT @VAR_DAY = 
    CASE DATEPART(dw, GETDATE())
        WHEN 1 THEN 'SUN'
        WHEN 2 THEN 'MON'
        WHEN 3 THEN 'TUE'
        WHEN 4 THEN 'WED'
        WHEN 5 THEN 'THU'
        WHEN 6 THEN 'FRI'
        WHEN 7 THEN 'SAT'
    END;

-- MAKE UP FILE NAME
SELECT @VAR_FILE = @VAR_PATH + '\' +  UPPER(@VAR_NAME) + '\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_FULL.BAK';
    
-- SHOW THE FINAL NAME
PRINT 'BACKUP THE FOLLOWING DATABASE TO FILE:'
PRINT '    ' + @VAR_FILE;

-- MAKE UP THE DESCRIPTION
SELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Full Database Backup';

-- OVERWRITE EXISTING FILE
BACKUP DATABASE @VAR_NAME
  TO DISK = @VAR_FILE
  WITH 
    FORMAT, 
    INIT,  
    NAME = @VAR_DESC, 
    SKIP, 
    NOREWIND, 
    NOUNLOAD,  
    STATS = 10;
GO
</span>

-- DECLARE VARIABLES

DECLARE @VAR_DAY VARCHAR(3);

DECLARE @VAR_PATH VARCHAR(125);

DECLARE @VAR_NAME VARCHAR(125);

DECLARE @VAR_FILE VARCHAR(250);

DECLARE @VAR_DESC VARCHAR(250);

-- TARGET DIRECTORY & NAME

SET @VAR_PATH = 'C:\MSSQL\BACKUP';

SET @VAR_NAME = 'BSA';

-- WEEKDAY BACKUP PLAN

SELECT @VAR_DAY =

CASE DATEPART(dw, GETDATE())

WHEN 1 THEN 'SUN'

WHEN 2 THEN 'MON'

WHEN 3 THEN 'TUE'

WHEN 4 THEN 'WED'

WHEN 5 THEN 'THU'

WHEN 6 THEN 'FRI'

WHEN 7 THEN 'SAT'

END;

-- MAKE UP FILE NAME

SELECT @VAR_FILE = @VAR_PATH + '\' + UPPER(@VAR_NAME) + '\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_FULL.BAK';

-- SHOW THE FINAL NAME

PRINT 'BACKUP THE FOLLOWING DATABASE TO FILE:'

PRINT ' ' + @VAR_FILE;

-- MAKE UP THE DESCRIPTION

SELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Full Database Backup';

-- OVERWRITE EXISTING FILE

BACKUP DATABASE @VAR_NAME

TO DISK = @VAR_FILE

WITH

FORMAT,

INIT,

NAME = @VAR_DESC,

SKIP,

NOREWIND,

NOUNLOAD,

STATS = 10;

The code snippet changes the recovery model from FULL to BULK LOGGED by using the ALTER DATABASE statement.

<span style="color: #008000;">-- CHANGE THE RECOVERY MODEL
ALTER DATABASE BSA SET
  RECOVERY BULK_LOGGED;
GO
</span>

-- CHANGE THE RECOVERY MODEL

ALTER DATABASE BSA SET

RECOVERY BULK_LOGGED;

The BSA database has a STAGING schema for loading external data. The BULK INSERT statement in its simplest form must have the source data file match the number of columns in the target table. We need to recreate the table to have just two fields.

There are many arguements that can be used to change how the statement executes. The FIRSTROW arguement allows us to skip the header row. The FIELDTERMINATOR and ROWTERMINATOR arguements are used to define a Comma Seperated Values format. The KEEPIDENTITY arguement allows us to use ID value in the data file instead of the automatic number generated by the IDENTITY column. Last but not least, we do not want to skip any errors.

<span style="color: #008000;">-- Select BSA database
USE BSA;
GO

-- Remove the table
DROP TABLE [STAGE].[TBL_RANK];
GO

-- Table w/o default fields
CREATE TABLE [STAGE].[TBL_RANK]
(
	[RANK_ID] [int] IDENTITY(1,1) NOT NULL,
	[RANK_DESC] [varchar](50) NOT NULL,
    CONSTRAINT [PK_TBL_RANK] PRIMARY KEY CLUSTERED ([RANK_ID] ASC)
) 
GO

-- Import Comma Seperated Value File
BULK INSERT BSA.STAGE.TBL_RANK
   FROM 'C:\TEST\RANK1.CSV'
   WITH
   (
       FIRSTROW = 2,
       FIELDTERMINATOR = ', ',
       ROWTERMINATOR = '\n',
       KEEPIDENTITY,
       MAXERRORS = 0
   );
GO 
</span>

-- Select BSA database

USE BSA;

-- Remove the table

DROP TABLE [STAGE].[TBL_RANK];

-- Table w/o default fields

CREATE TABLE [STAGE].[TBL_RANK]

(

[RANK_ID] [int] IDENTITY(1,1) NOT NULL,

[RANK_DESC] [varchar](50) NOT NULL,

CONSTRAINT [PK_TBL_RANK] PRIMARY KEY CLUSTERED ([RANK_ID] ASC)

)

-- Import Comma Seperated Value File

BULK INSERT BSA.STAGE.TBL_RANK

FROM 'C:\TEST\RANK1.CSV'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ', ',

ROWTERMINATOR = '\n',

KEEPIDENTITY,

MAXERRORS = 0

);

I am going to make the problem a little more difficult by adding the three fields that are defaults. How do we now import a file that has less columns that the table?

<span style="color: #008000;">-- Add orginal fields
ALTER TABLE [STAGE].[TBL_RANK] ADD
	[ROW_GUID] [uniqueidentifier] ROWGUIDCOL NULL,
	[MODIFIED_DTE] [datetime] NULL,
	[MODIFIED_NM] [varchar](20) NULL;
GO

-- Add orginal constraints
ALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_ROW_GUID]  DEFAULT (newsequentialid()) FOR [ROW_GUID]
GO

ALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_MODIFIED_DTE]  DEFAULT (getdate()) FOR [MODIFIED_DTE]
GO

ALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_MODIFIED_NM]  DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]
GO 
</span>

-- Add orginal fields

ALTER TABLE [STAGE].[TBL_RANK] ADD

[ROW_GUID] [uniqueidentifier] ROWGUIDCOL NULL,

[MODIFIED_DTE] [datetime] NULL,

[MODIFIED_NM] [varchar](20) NULL;

-- Add orginal constraints

ALTER TABLE [STAGE].[TBL_RANK] ADD CONSTRAINT [DF_TR_ROW_GUID] DEFAULT (newsequentialid()) FOR [ROW_GUID]

ALTER TABLE [STAGE].[TBL_RANK] ADD CONSTRAINT [DF_TR_MODIFIED_DTE] DEFAULT (getdate()) FOR [MODIFIED_DTE]

ALTER TABLE [STAGE].[TBL_RANK] ADD CONSTRAINT [DF_TR_MODIFIED_NM] DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]

That is where format files come in handy. We are going to use the BCP utility to create a format file from the table definition. From there, I am going to modify it by removing unwanted source rows and defining target fields. Run the following from a command prompt. All files used in the examples can be found at the end of the article.

<span style="color: #008000;">rem
rem Run from cmd shell, create non xml format file, copy to final target and edit.
rem

bcp BSA.RECENT.TBL_RANK format nul -T -n -f rank2-all.fmt
copy rank2-all.fmt rank2.fmt

</span>

rem

rem Run from cmd shell, create non xml format file, copy to final target and edit.

rem

bcp BSA.RECENT.TBL_RANK format nul -T -n -f rank2-all.fmt

copy rank2-all.fmt rank2.fmt

Some arguements of interest are used in this example. The FIRSTROW and LASTROW are used to select a subset of the source data file. The BATCHSIZE allows the database engine to commit changes to disk. This is really important when the number of records increases to free up resources. Last but not least, the FORMATFILE allows our custom file definition to be used.

<span style="color: #008000;">-- Remove data from table
TRUNCATE TABLE STAGE.TBL_RANK;
GO

-- Import Tab Delimited File
BULK INSERT BSA.STAGE.TBL_RANK
   FROM 'C:\TEST\RANK2.TAB'
   WITH 
   (
       FIRSTROW = 2,   
       LASTROW = 9,   
       BATCHSIZE = 2,
       FORMATFILE = 'C:\TEST\RANK2.FMT'
   );
GO 
</span>

-- Remove data from table

TRUNCATE TABLE STAGE.TBL_RANK;

-- Import Tab Delimited File

BULK INSERT BSA.STAGE.TBL_RANK

FROM 'C:\TEST\RANK2.TAB'

WITH

(

FIRSTROW = 2,

LASTROW = 9,

BATCHSIZE = 2,

FORMATFILE = 'C:\TEST\RANK2.FMT'

);

The last arguement that I want to introduce today allows triggers to be executed when BULK INSERT is executed. This is an easy way to move data from one table to another or from STAGE to RECENT schemas. The snipet below adds the trigger to the staging table and imports the data.

<span style="color: #008000;">-- Remove data from table
TRUNCATE TABLE STAGE.TBL_RANK;
GO

-- Remove data from table
TRUNCATE TABLE RECENT.TBL_RANK;
GO

-- Add trigger to staging table
CREATE TRIGGER TRG_STAGE_2_RECENT
ON [STAGE].[TBL_RANK] FOR INSERT
AS 
  INSERT INTO [RECENT].[TBL_RANK] (RANK_DESC)
  SELECT i.RANK_DESC FROM inserted i
GO

-- Import Data with triggers
BULK INSERT BSA.STAGE.TBL_RANK
   FROM 'C:\TEST\RANK2.TAB'
   WITH 
   (
       FIRSTROW = 2,   
       FIRE_TRIGGERS,
       FORMATFILE = 'C:\TEST\RANK2.FMT'
   );
GO 
</span>

-- Remove data from table

TRUNCATE TABLE STAGE.TBL_RANK;

-- Remove data from table

TRUNCATE TABLE RECENT.TBL_RANK;

-- Add trigger to staging table

CREATE TRIGGER TRG_STAGE_2_RECENT

ON [STAGE].[TBL_RANK] FOR INSERT

INSERT INTO [RECENT].[TBL_RANK] (RANK_DESC)

SELECT i.RANK_DESC FROM inserted i

-- Import Data with triggers

BULK INSERT BSA.STAGE.TBL_RANK

FROM 'C:\TEST\RANK2.TAB'

WITH

(

FIRSTROW = 2,

FIRE_TRIGGERS,

FORMATFILE = 'C:\TEST\RANK2.FMT'

);

Last but not least, we should backup the transaction log file to reclaim the space and change the recovery model to FULL.

<span style="color: #008000;">-- DECLARE VARIABLES
DECLARE @VAR_DAY VARCHAR(3);
DECLARE @VAR_PATH VARCHAR(125);
DECLARE @VAR_NAME VARCHAR(125);
DECLARE @VAR_FILE VARCHAR(250);
DECLARE @VAR_DESC VARCHAR(250);

-- TARGET DIRECTORY & NAME
SET @VAR_PATH = 'C:\MSSQL\BACKUP';  
SET @VAR_NAME = 'BSA';  
  
-- WEEKDAY BACKUP PLAN
SELECT @VAR_DAY = 
    CASE DATEPART(dw, GETDATE())
        WHEN 1 THEN 'SUN'
        WHEN 2 THEN 'MON'
        WHEN 3 THEN 'TUE'
        WHEN 4 THEN 'WED'
        WHEN 5 THEN 'THU'
        WHEN 6 THEN 'FRI'
        WHEN 7 THEN 'SAT'
    END;
    
-- MAKE UP FILE NAME
SELECT @VAR_FILE = @VAR_PATH + '\' + UPPER(@VAR_NAME) + '\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_LOG.TRN';

-- SHOW THE FINAL NAME
PRINT 'BACKUP THE FOLLOWING LOG TO FILE:'
PRINT '    ' + @VAR_FILE;

-- MAKE UP THE DESCRIPTION
SELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Transaction Log Backup';


-- OVERWRITE EXISTING FILE
BACKUP LOG @VAR_NAME
  TO DISK = @VAR_FILE
  WITH  
    FORMAT, 
    INIT,  
    NAME = @VAR_DESC, 
    SKIP, 
    NOREWIND, 
    NOUNLOAD,  
    STATS = 10;
GO
</span>

-- DECLARE VARIABLES

DECLARE @VAR_DAY VARCHAR(3);

DECLARE @VAR_PATH VARCHAR(125);

DECLARE @VAR_NAME VARCHAR(125);

DECLARE @VAR_FILE VARCHAR(250);

DECLARE @VAR_DESC VARCHAR(250);

-- TARGET DIRECTORY & NAME

SET @VAR_PATH = 'C:\MSSQL\BACKUP';

SET @VAR_NAME = 'BSA';

-- WEEKDAY BACKUP PLAN

SELECT @VAR_DAY =

CASE DATEPART(dw, GETDATE())

WHEN 1 THEN 'SUN'

WHEN 2 THEN 'MON'

WHEN 3 THEN 'TUE'

WHEN 4 THEN 'WED'

WHEN 5 THEN 'THU'

WHEN 6 THEN 'FRI'

WHEN 7 THEN 'SAT'

END;

-- MAKE UP FILE NAME

SELECT @VAR_FILE = @VAR_PATH + '\' + UPPER(@VAR_NAME) + '\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_LOG.TRN';

-- SHOW THE FINAL NAME

PRINT 'BACKUP THE FOLLOWING LOG TO FILE:'

PRINT ' ' + @VAR_FILE;

-- MAKE UP THE DESCRIPTION

SELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Transaction Log Backup';

-- OVERWRITE EXISTING FILE

BACKUP LOG @VAR_NAME

TO DISK = @VAR_FILE

WITH

FORMAT,

INIT,

NAME = @VAR_DESC,

SKIP,

NOREWIND,

NOUNLOAD,

STATS = 10;

The code snippet changes the recovery model from BULK LOGGED to FULL.

<span style="color: #008000;">-- CHANGE THE RECOVERY MODEL
ALTER DATABASE BSA SET
  RECOVERY FULL;
GO
</span>

-- CHANGE THE RECOVERY MODEL

ALTER DATABASE BSA SET

RECOVERY FULL;

Importing data by using the BULK INSERT statements is a better choice for moving large amounts of data. There are many options that can be specified with the statement. A format file can be used to skip columns or define additional mappings. In summary, BULK INSERT is like a one way ticket to BANGOR, ME. You can get to the destination but can not get back to the original starting point.
The BCP utility fixes this defect by allowing data to be exported or imported. I will be exploring using the utility next time.

Files Used In Examples

rank1.csv	Scout Rank Data (CSV)
rank2.tab	Scout Rank Data (TAB)
rank2.fmt	Modified format file
rank2-all.fmt	BCP format file based on table

6 Thoughts to “Import & Export Data Part 2”

Detra

December 6, 2011 at 6:41 pm

Saved, I enjoy your site! :)

Lauren Tave

December 19, 2011 at 5:24 pm

I just want to say I’m new to blogs and definitely loved your web page. Very likely I’m going to bookmark your blog post . You actually have really good writings. Kudos for sharing your blog.

Humor News

December 22, 2011 at 9:21 am

Wow Excellent blog!

Kristen

January 15, 2012 at 5:02 am

Love the blog

Willis Spirko

January 22, 2012 at 12:02 am

Top notch post. Continue to keep up the very fantastic work.

Janetta Attianese

February 2, 2012 at 8:11 pm

Precious blogger, thank you for providing this amazing stuff! I found it excellent. Greets, !!!!

Related posts

6 Thoughts to “Import & Export Data Part 2”

Leave a Comment Cancel reply

Related posts

Don’t pass over synonyms

Is my string a number?

Replacing Data – Part 2

6 Thoughts to “Import & Export Data Part 2”

Leave a Comment Cancel reply