{"id":1255,"date":"2011-11-30T02:12:01","date_gmt":"2011-11-30T02:12:01","guid":{"rendered":"http:\/\/craftydba.com\/?p=1255"},"modified":"2011-11-30T14:55:32","modified_gmt":"2011-11-30T14:55:32","slug":"import-export-data-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1255","title":{"rendered":"Import & Export Data Part 2"},"content":{"rendered":"

The BULK INSERT<\/a> 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<\/a> 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.<\/p>\n

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<\/a> and change the recovery model to bulk . <\/p>\n

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.<\/p>\n

 -- DECLARE VARIABLES\r\nDECLARE @VAR_DAY VARCHAR(3);\r\nDECLARE @VAR_PATH VARCHAR(125);\r\nDECLARE @VAR_NAME VARCHAR(125);\r\nDECLARE @VAR_FILE VARCHAR(250);\r\nDECLARE @VAR_DESC VARCHAR(250);\r\n\r\n-- TARGET DIRECTORY & NAME\r\nSET @VAR_PATH = 'C:\\MSSQL\\BACKUP';  \r\nSET @VAR_NAME = 'BSA';  \r\n  \r\n-- WEEKDAY BACKUP PLAN\r\nSELECT @VAR_DAY = \r\n    CASE DATEPART(dw, GETDATE())\r\n        WHEN 1 THEN 'SUN'\r\n        WHEN 2 THEN 'MON'\r\n        WHEN 3 THEN 'TUE'\r\n        WHEN 4 THEN 'WED'\r\n        WHEN 5 THEN 'THU'\r\n        WHEN 6 THEN 'FRI'\r\n        WHEN 7 THEN 'SAT'\r\n    END;\r\n\r\n-- MAKE UP FILE NAME\r\nSELECT @VAR_FILE = @VAR_PATH + '\\' +  UPPER(@VAR_NAME) + '\\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_FULL.BAK';\r\n    \r\n-- SHOW THE FINAL NAME\r\nPRINT 'BACKUP THE FOLLOWING DATABASE TO FILE:'\r\nPRINT '    ' + @VAR_FILE;\r\n\r\n-- MAKE UP THE DESCRIPTION\r\nSELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Full Database Backup';\r\n\r\n-- OVERWRITE EXISTING FILE\r\nBACKUP DATABASE @VAR_NAME\r\n  TO DISK = @VAR_FILE\r\n  WITH \r\n    FORMAT, \r\n    INIT,  \r\n    NAME = @VAR_DESC, \r\n    SKIP, \r\n    NOREWIND, \r\n    NOUNLOAD,  \r\n    STATS = 10;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The code snippet changes the recovery model from FULL to BULK LOGGED by using the ALTER DATABASE<\/a> statement.<\/p>\n
-- CHANGE THE RECOVERY MODEL\r\nALTER DATABASE BSA SET\r\n  RECOVERY BULK_LOGGED;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
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.  <\/p>\n
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.<\/p>\n
-- Select BSA database\r\nUSE BSA;\r\nGO\r\n\r\n-- Remove the table\r\nDROP TABLE [STAGE].[TBL_RANK];\r\nGO\r\n\r\n-- Table w\/o default fields\r\nCREATE TABLE [STAGE].[TBL_RANK]\r\n(\r\n\t[RANK_ID] [int] IDENTITY(1,1) NOT NULL,\r\n\t[RANK_DESC] [varchar](50) NOT NULL,\r\n    CONSTRAINT [PK_TBL_RANK] PRIMARY KEY CLUSTERED ([RANK_ID] ASC)\r\n) \r\nGO\r\n\r\n-- Import Comma Seperated Value File\r\nBULK INSERT BSA.STAGE.TBL_RANK\r\n   FROM 'C:\\TEST\\RANK1.CSV'\r\n   WITH\r\n   (\r\n       FIRSTROW = 2,\r\n       FIELDTERMINATOR = ', ',\r\n       ROWTERMINATOR = '\\n',\r\n       KEEPIDENTITY,\r\n       MAXERRORS = 0\r\n   );\r\nGO \r\n<\/span><\/pre>\n<\/p>\n
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?<\/p>\n
-- Add orginal fields\r\nALTER TABLE [STAGE].[TBL_RANK] ADD\r\n\t[ROW_GUID] [uniqueidentifier] ROWGUIDCOL NULL,\r\n\t[MODIFIED_DTE] [datetime] NULL,\r\n\t[MODIFIED_NM] [varchar](20) NULL;\r\nGO\r\n\r\n-- Add orginal constraints\r\nALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_ROW_GUID]  DEFAULT (newsequentialid()) FOR [ROW_GUID]\r\nGO\r\n\r\nALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_MODIFIED_DTE]  DEFAULT (getdate()) FOR [MODIFIED_DTE]\r\nGO\r\n\r\nALTER TABLE [STAGE].[TBL_RANK] ADD  CONSTRAINT [DF_TR_MODIFIED_NM]  DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]\r\nGO \r\n<\/span><\/pre>\n<\/p>\n
That is where format files come in handy.  We are going to use the BCP<\/a> 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<\/a>.  All files used in the examples can be found at the end of the article.<\/p>\n
rem\r\nrem Run from cmd shell, create non xml format file, copy to final target and edit.\r\nrem\r\n\r\nbcp BSA.RECENT.TBL_RANK format nul -T -n -f rank2-all.fmt\r\ncopy rank2-all.fmt rank2.fmt\r\n\r\n<\/span><\/pre>\n<\/p>\n
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.<\/p>\n
-- Remove data from table\r\nTRUNCATE TABLE STAGE.TBL_RANK;\r\nGO\r\n\r\n-- Import Tab Delimited File\r\nBULK INSERT BSA.STAGE.TBL_RANK\r\n   FROM 'C:\\TEST\\RANK2.TAB'\r\n   WITH \r\n   (\r\n       FIRSTROW = 2,   \r\n       LASTROW = 9,   \r\n       BATCHSIZE = 2,\r\n       FORMATFILE = 'C:\\TEST\\RANK2.FMT'\r\n   );\r\nGO \r\n<\/span><\/pre>\n<\/p>\n
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.<\/p>\n
-- Remove data from table\r\nTRUNCATE TABLE STAGE.TBL_RANK;\r\nGO\r\n\r\n-- Remove data from table\r\nTRUNCATE TABLE RECENT.TBL_RANK;\r\nGO\r\n\r\n-- Add trigger to staging table\r\nCREATE TRIGGER TRG_STAGE_2_RECENT\r\nON [STAGE].[TBL_RANK] FOR INSERT\r\nAS \r\n  INSERT INTO [RECENT].[TBL_RANK] (RANK_DESC)\r\n  SELECT i.RANK_DESC FROM inserted i\r\nGO\r\n\r\n-- Import Data with triggers\r\nBULK INSERT BSA.STAGE.TBL_RANK\r\n   FROM 'C:\\TEST\\RANK2.TAB'\r\n   WITH \r\n   (\r\n       FIRSTROW = 2,   \r\n       FIRE_TRIGGERS,\r\n       FORMATFILE = 'C:\\TEST\\RANK2.FMT'\r\n   );\r\nGO \r\n<\/span><\/pre>\n<\/p>\n
Last but not least, we should backup<\/a> the transaction log file to reclaim the space and change the recovery model to FULL.<\/p>\n
-- DECLARE VARIABLES\r\nDECLARE @VAR_DAY VARCHAR(3);\r\nDECLARE @VAR_PATH VARCHAR(125);\r\nDECLARE @VAR_NAME VARCHAR(125);\r\nDECLARE @VAR_FILE VARCHAR(250);\r\nDECLARE @VAR_DESC VARCHAR(250);\r\n\r\n-- TARGET DIRECTORY & NAME\r\nSET @VAR_PATH = 'C:\\MSSQL\\BACKUP';  \r\nSET @VAR_NAME = 'BSA';  \r\n  \r\n-- WEEKDAY BACKUP PLAN\r\nSELECT @VAR_DAY = \r\n    CASE DATEPART(dw, GETDATE())\r\n        WHEN 1 THEN 'SUN'\r\n        WHEN 2 THEN 'MON'\r\n        WHEN 3 THEN 'TUE'\r\n        WHEN 4 THEN 'WED'\r\n        WHEN 5 THEN 'THU'\r\n        WHEN 6 THEN 'FRI'\r\n        WHEN 7 THEN 'SAT'\r\n    END;\r\n    \r\n-- MAKE UP FILE NAME\r\nSELECT @VAR_FILE = @VAR_PATH + '\\' + UPPER(@VAR_NAME) + '\\' + UPPER(@VAR_NAME) + '_' + @VAR_DAY + '_LOG.TRN';\r\n\r\n-- SHOW THE FINAL NAME\r\nPRINT 'BACKUP THE FOLLOWING LOG TO FILE:'\r\nPRINT '    ' + @VAR_FILE;\r\n\r\n-- MAKE UP THE DESCRIPTION\r\nSELECT @VAR_DESC = UPPER(@VAR_NAME) + ' - Transaction Log Backup';\r\n\r\n\r\n-- OVERWRITE EXISTING FILE\r\nBACKUP LOG @VAR_NAME\r\n  TO DISK = @VAR_FILE\r\n  WITH  \r\n    FORMAT, \r\n    INIT,  \r\n    NAME = @VAR_DESC, \r\n    SKIP, \r\n    NOREWIND, \r\n    NOUNLOAD,  \r\n    STATS = 10;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The code snippet changes the recovery model from BULK LOGGED to FULL.<\/p>\n
-- CHANGE THE RECOVERY MODEL\r\nALTER DATABASE BSA SET\r\n  RECOVERY FULL;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
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.
\nThe BCP <\/a>utility fixes this defect by allowing data to be exported or imported.  I will be exploring using the utility next time.<\/p>\n
Files Used In Examples<\/p>\n\n\n\n\n\n\n
rank1.csv<\/a><\/td>\nScout Rank Data (CSV)<\/td>\n<\/tr>\n
rank2.tab<\/a><\/td>\nScout Rank Data (TAB)<\/td>\n<\/tr>\n
rank2.fmt<\/a><\/td>\nModified format file<\/td>\n<\/tr>\n
rank2-all.fmt<\/a><\/td>\nBCP format file based on table<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[253,254,261,252,249,258,255,262,12,248,15,260,256,257,259,28,29],"class_list":["post-1255","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-backup-database","tag-backup-log","tag-batchsize","tag-bulk-insert","tag-export","tag-fieldterminator","tag-firstrow","tag-formatfile","tag-free-code","tag-import","tag-john-f-miner-iii","tag-keepidentity","tag-lastrow","tag-maxerrors","tag-rowterminator","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1255"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1255\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}