Tutorials
Practical T-SQL
Practical T-SQL
  • Practical T-SQL Pocket Guide For Beginners
  • Preface
    • Section I. A Note From The Author
    • Section II. Tutorial Overview
    • Section III. Running The Examples
    • Section IV. How To Report An Issue
    • Section V. Join The MSU Community On Discord
    • Section VI. Supplementary Material
  • Language Basics
    • Lesson 1. Obligatory Hello World
    • Lesson 2. Code Comments With T-SQL
    • Lesson 3. Basic Syntax
    • Lesson 4. Your First Query
    • Lesson 5. Filtering Data
    • Lesson 6. Sorting Data
    • Lesson 7. Complex Data Filtering
    • Lesson 8. Aliases
    • Lesson 9. String Functions
    • Lesson 10. Creating New Columns From Existing Data (Calculated Fields)
    • Lesson 11. Displaying Data Based On Conditions (Case Statement)
    • Lesson 12. Aggregate Functions
    • Lesson 13. Grouping And Summarizing Data
    • Lesson 14. Querying More Than One Table
    • Lesson 15. Combining Queries
    • Lesson 16. Subqueries
    • Lesson 17. Creating Data
    • Lesson 18. Updating Data
    • Lesson 19. Deleting Data
    • Lesson 20. Common Table Expressions (CTEs)
    • Lesson 21. Derived Tables
    • Lesson 22. Putting It All Together
  • Advanced Topics
    • Lesson 23. Selecting Unique Values
    • Lesson 24. Updating Data With A Join
    • Lesson 25. Data Types
    • Lesson 26. Casting Data Types
    • Lesson 27. Creating Tables
    • Lesson 28. Altering Tables
    • Lesson 29. Dropping Tables
    • Lesson 30. Variables
    • Lesson 31. Controlling Flow
    • Lesson 32. Looping
    • Lesson 33. Error Processing
    • Lesson 34. Temporary Tables
    • Lesson 35. Views
    • Lesson 36. Indexed Views
    • Lesson 37. User Defined Functions
    • Lesson 38. Stored Procedures
    • Lesson 39. BULK INSERT
    • Lesson 40. Loading Tables With MERGE
    • Lesson 41. Partitioning A Dataset
    • Lesson 42. Pivoting Data
    • Lesson 43. Dynamic SQL
    • Lesson 44. Cursors
  • Solutions To Real World Problems
    • Lesson 45. Listing All Tables In A SQL Server Database
    • Lesson 46. Listing All Columns In A SQL Server Database
    • Lesson 47. Pull Records From A Table At Random
    • Lesson 48. A Better Alternative To WITH (NOLOCK)
    • Lesson 49. Boost Performance When Calling A Stored Proc From SSIS
    • Lesson 50. Setting Up Queries For Ablation Testing
    • Lesson 51. Reduce Code And Save Time With Default Column Values
    • Lesson 52. Finding Duplicate Records In A Table
    • Lesson 53. Why You Cannot Have More Than One Clustered Index On A Table
    • Lesson 54. Converting Dates To YYYYMMDD
    • Lesson 55. Sending Notification Emails With T-SQL Without Using Hardcoded Email Addresses
    • Lesson 56. Troubleshooting Long Running Queries
    • Lesson 57. Loading Large CSVs Into Data Warehouse Staging Tables
    • Lesson 58. The Only Bloody Good Reason To Use Cursors
    • Lesson 59. Loading A Type II Slowly Changing Dimension With SQL Merge
    • Lesson 60. A Clearer Explanation Of The Parameters Of The Numeric Data Type
    • Lesson 61. Why You Cannot Join On Null Values
    • Lesson 62. A Deep Dive On How The Where Clause Functions
    • Lesson 63. Using HASHBYTES() To Compare Character Strings
    • Lesson 64. Using Pipe To Hash Multiple Columns For Matching
    • Lesson 65. Why People That Indent Code Drive Me Nuts
    • Lesson 66. How To Rapidly Stand Up A Data Warehouse From Scratch
    • Lesson 67. How To Pivot Data With T-SQL When Columns Are Not Predefined
    • Lesson 68. Prepopulating A Junk Dimension
Powered by GitBook
On this page
  1. Solutions To Real World Problems

Lesson 57. Loading Large CSVs Into Data Warehouse Staging Tables

When I load large CSVs into staging, I want to use BULK INSERT. The problem with BULK INSERT is that it will not load a file into a table that does not match the schema of the file. That is problematic as staging tables have audit columns that certainly will not be in the file. This is the process I use to get around this problem.

  1. In your load proc, remove all keys and indexes from the target table. Those things slow down the load.

  2. Create a view of the target table with the exact same name as the target table and place it in the dbo schema. The view should only contain those columns in the file.

  3. Use BULK INSERT to load the view which will load the table. Set the parameters of BULK INSERT to fit the scenario.

  4. In your load proc, add back all the keys and indexes.

Examples

Comprehensive Load Example

We are going to use the sample dataset "Flights Table From the nycflights13 Dataset".

In [ ]:

USE demo

DROP TABLE IF EXISTS temp.FlightsStaging
DROP SCHEMA IF EXISTS temp
DROP VIEW IF EXISTS FlightsStaging

GO

CREATE SCHEMA temp



CREATE TABLE temp.FlightsStaging(
ETLKey uniqueidentifier NOT NULL,
year NVARCHAR(255) NULL,
month NVARCHAR(255) NULL,
day NVARCHAR(255) NULL,
dep_time NVARCHAR(255) NULL,
sched_dep_time NVARCHAR(255) NULL,
dep_delay NVARCHAR(255) NULL,
arr_time NVARCHAR(255) NULL,
sched_arr_time NVARCHAR(255) NULL,
arr_delay NVARCHAR(255) NULL,
carrier NVARCHAR(255) NULL,
flight NVARCHAR(255) NULL,
tailnum NVARCHAR(255) NULL,
origin NVARCHAR(255) NULL,
dest NVARCHAR(255) NULL,
air_time NVARCHAR(255) NULL,
distance NVARCHAR(255) NULL,
hour NVARCHAR(255) NULL,
minute NVARCHAR(255) NULL,
time_hour NVARCHAR(255) NULL,
UniqueDims varbinary(35) NULL,
UniqueRows varbinary(16) NULL,
SourceSystem nvarchar(255) NULL,
Cleansed bit NULL,
ErrorRecord bit NULL,
ErrorReason nvarchar(255) NULL,
Processed bit NULL,
RunDate datetime NULL,
CONSTRAINT PK_FlightsStaging PRIMARY KEY CLUSTERED 
(
       [ETLKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_ETLKey  DEFAULT (newid()) FOR ETLKey
GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_SourceSystem  DEFAULT (N'Flights System') FOR SourceSystem
GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_Cleansed  DEFAULT ((0)) FOR Cleansed
GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_ErrorRecord  DEFAULT ((0)) FOR ErrorRecord
GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_Processed  DEFAULT ((0)) FOR Processed
GO

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT DF_FlightsStaging_RunDate  DEFAULT (getdate()) FOR RunDate
GO

CREATE VIEW FlightsStaging
AS
SELECT
year,
month,
day,
dep_time,
sched_dep_time,
dep_delay,
arr_time,
sched_arr_time,
arr_delay,
carrier,
flight,
tailnum,
origin,
dest,
air_time,
distance,
hour,
minute,
time_hour
FROM temp.FlightsStaging
GO
--Begin Load
ALTER TABLE temp.FlightsStaging DROP CONSTRAINT PK_FlightsStaging WITH ( ONLINE = OFF )
GO

--Insert into the view
BULK INSERT FlightsStaging
FROM 'E:\flights.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
FIRSTROW = 2
);

ALTER TABLE temp.FlightsStaging ADD  CONSTRAINT PK_FlightsStaging PRIMARY KEY CLUSTERED 
(
   ETLKey ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--but the records went to the actual table
SELECT *
FROM temp.FlightsStaging



DROP TABLE temp.FlightsStaging
DROP SCHEMA temp
DROP VIEW FlightsStaging
PreviousLesson 56. Troubleshooting Long Running QueriesNextLesson 58. The Only Bloody Good Reason To Use Cursors

Last updated 3 years ago