{"id":1584,"date":"2012-02-07T03:22:30","date_gmt":"2012-02-07T03:22:30","guid":{"rendered":"http:\/\/craftydba.com\/?p=1584"},"modified":"2012-02-16T16:46:58","modified_gmt":"2012-02-16T16:46:58","slug":"import-export-data-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1584","title":{"rendered":"Import & Export Data – Part 3"},"content":{"rendered":"

The BCP<\/a> command line utility is the Cadillac<\/a> of ETL programs for text based data files. It is REALLY FAST. It can perform both imports and exports and it can generate format files from existing objects.<\/p>\n

Today, I am going implement the same business algorithms I did earlier (Part 2) using the BCP program instead of BULK INSERT. We will be working again with the Boy Scouts of America (BSA) hypothetical database. I will be using the xp_cmdshell to execute BCP from a query window inside of SQL Server Management Studio (SSMS).<\/p>\n

I want to stress that the following steps should be done before, during, and after a major data load.<\/p>\n\n\n\n\n\n\n\n\n\n
TIMING<\/td>\nTSQL COMMAND<\/td>\nBUSINESS RULE<\/td>\n<\/tr>\n
BEFORE<\/td>\nBACKUP DATABASE<\/td>\nBackup database before changes.<\/td>\n<\/tr>\n
BEFORE<\/td>\nALTER DATABASE<\/td>\nSet recovery model to bulk logged.<\/td>\n<\/tr>\n
DURING<\/td>\nBCP<\/td>\nExecute ETL processes.<\/td>\n<\/tr>\n
DURING<\/td>\nBACKUP LOG<\/td>\nKeep log growth to minimum.<\/td>\n<\/tr>\n
AFTER<\/td>\nALTER DATABASE<\/td>\nSet recovery model to full.<\/td>\n<\/tr>\n
AFTER<\/td>\nBACKUP DATABASE<\/td>\nBackup database after changes.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The BSA database has a STAGING schema for loading external data. The BCP utility in its simplest form must have the source data file match the number of columns in the target table. Therefore, we need to re-create the table to have just two fields.<\/p>\n

There are many arguements that can be used to change how the statement executes. Here are some used that are used in the examples below.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
BCP SWITCH<\/td>\nACTION<\/td>\n<\/tr>\n
-F<\/td>\nfirst row starts at row x.<\/td>\n<\/tr>\n
-L<\/td>\nlast row ends at row y.<\/td>\n<\/tr>\n
-t<\/td>\nfield terminator is defined as .<\/td>\n<\/tr>\n
-r<\/td>\nrow terminator is defined as .<\/td>\n<\/tr>\n
-T<\/td>\nuse a trusted connection.<\/td>\n<\/tr>\n
-m<\/td>\nmax errors to allow.<\/td>\n<\/tr>\n
-c<\/td>\nperform operation using char data type.<\/td>\n<\/tr>\n
-E<\/td>\nuse identity values in file.<\/td>\n<\/tr>\n
-f<\/td>\nuse the specified format file.<\/td>\n<\/tr>\n
-b<\/td>\nz rows per batch before commit.<\/td>\n<\/tr>\n
-h<\/td>\nspecify advance hints to be use.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The first example re-creates the table and imports the data.<\/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    [RANK_ID] [int] IDENTITY(1,1) NOT NULL,\r\n    [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-- BCP - Import Comma Seperated Value File\r\nDECLARE @bcp_cmd VARCHAR(1000);\r\nSET @bcp_cmd = 'BCP BSA.STAGE.TBL_RANK IN \"C:\\TEST\\RANK1.CSV\" -T -F 2 -t, -r \\n -E -m 1 -c '\r\nPRINT @bcp_cmd\r\nEXEC master..xp_cmdshell @bcp_cmd\r\nGO \r\n<\/span><\/pre>\n
\"\"<\/a><\/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 than 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
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 DOS command shell. 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\n\r\ncopy rank2-all.fmt rank2.fmt\r\n<\/span><\/pre>\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-- BCP - Import Tab Delimited File\r\nDECLARE @bcp_cmd1 VARCHAR(1000);\r\nSET @bcp_cmd1 = 'BCP BSA.STAGE.TBL_RANK IN \"C:\\TEST\\RANK2.TAB\" -T -F2 -L9 -b2 -f \"C:\\TEST\\RANK2.FMT\"'\r\nPRINT @bcp_cmd1\r\nEXEC master..xp_cmdshell @bcp_cmd1\r\nGO \r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
The last arguement that I want to introduce today allows triggers to be executed when BCP 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-- BCP - Import Data with triggers\r\nDECLARE @bcp_cmd1 VARCHAR(1000);\r\nSET @bcp_cmd1 = 'BCP BSA.STAGE.TBL_RANK IN \"C:\\TEST\\RANK2.TAB\" -T -F2 -L9 -b2 -f \"C:\\TEST\\RANK2.FMT\" -h \"FIRE_TRIGGERS\"'\r\nPRINT @bcp_cmd1\r\nEXEC master..xp_cmdshell @bcp_cmd1\r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
Importing data by using the BCP command line utility is the best choice for moving large amounts of data. There are many options that can be specified that change the behavior of the program. A format file can be used to skip columns or define additional mappings.<\/p>\n
Many ETL programs such as IWAY Data Migrator use a graphical interface to draw process flows. But under the covers, the core engine uses BCP<\/a> or SQL Loader<\/a>, a data file and format file to get the job done quickly.<\/p>\n
The BCP utility is a great choice for creating ETL processes. I will be exploring using the utility next time to export data.<\/p>\n
Files Used In Examples<\/p>\n\n\n\n\n\n\n\n
FILE NAME<\/td>\nPURPOSE<\/td>\n<\/tr>\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 BCP command line utility is the Cadillac of ETL programs for text based data files. It is REALLY FAST. It can perform both imports and exports and it can generate format files from existing objects. Today, I am going implement the same business algorithms I did earlier (Part 2) using the BCP program instead of BULK INSERT. We will be working again with the Boy Scouts of America (BSA) hypothetical database. I will be using the xp_cmdshell to execute BCP from a query window inside of SQL Server Management…<\/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,349,249,258,255,262,12,248,15,260,256,257,259,28,29],"class_list":["post-1584","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-backup-database","tag-backup-log","tag-batchsize","tag-bcp","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\/1584","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=1584"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1584\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}