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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<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> |
The code snippet changes the recovery model from FULL to BULK LOGGED by using the ALTER DATABASE statement.
1 2 3 4 5 |
<span style="color: #008000;">-- CHANGE THE RECOVERY MODEL ALTER DATABASE BSA SET RECOVERY BULK_LOGGED; GO </span> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<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> |
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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<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> |
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.
1 2 3 4 5 6 7 8 |
<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> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<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> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<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> |
Last but not least, we should backup the transaction log file to reclaim the space and change the recovery model to FULL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
<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> |
The code snippet changes the recovery model from BULK LOGGED to FULL.
1 2 3 4 5 |
<span style="color: #008000;">-- CHANGE THE RECOVERY MODEL ALTER DATABASE BSA SET RECOVERY FULL; GO </span> |
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 |
Saved, I enjoy your site! :)
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.
Wow Excellent blog!
Love the blog
Top notch post. Continue to keep up the very fantastic work.
Precious blogger, thank you for providing this amazing stuff! I found it excellent. Greets, !!!!