SQL Server script to Bulk Insert from a CSV file into a table

The following SQL Server script uses Bulk Insert to insert a CSV file into a target table via a staging table.

Here are steps:

1. Create Raw Staging Table With All Varchar Columns
2. Importing From CSV To Raw Staging Table
3. Create Target Table With Typed Columns
4. Copy Raw Staging Table To Target Table

-- ================================================
PRINT '1 CREATE RAW STAGING TABLE WITH ALL VARCHAR COLUMNS'

CREATE TABLE [dbo].[MyTableName_BULK_INSERT](
[SomeStringColumn] [nvarchar](255) NULL,
[SomeIntColumn] [nvarchar](255) NULL
) ON [PRIMARY]


-- ================================================
PRINT '2 IMPORTING FROM CSV TO RAW STAGING TABLE'

BULK INSERT MyTableName_BULK_INSERT
FROM 'C:\Temp\MySourceFile.csv'
WITH
(
FIRSTROW = 2,  -- If has a header row
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0,
ERRORFILE = 'C:\Temp\Bulk_Insert_Errors.log',
CODEPAGE = 'ACP',
DATAFILETYPE = 'widechar'
)

-- select count(*) from MyTableName_BULK_INSERT
-- select top 10 * from MyTableName_BULK_INSERT


-- ================================================
PRINT '3 CREATE TARGET TABLE WITH TYPED COLUMNS'

CREATE TABLE [dbo].[MyTableName](
[SomeStringColumn] [varchar](255) NOT NULL,
[SomeIntColumn] [int] NOT NULL
 CONSTRAINT [PK_MyTableName] PRIMARY KEY CLUSTERED 
 ([SomeStringColumn] ASC)
  WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)


-- ================================================
PRINT '4 COPY RAW STAGING TABLE TO TARGET TABLE'

INSERT INTO [MyTableName]
([SomeStringColumn]
,[SomeIntColumn])
(SELECT 
[SomeStringColumn]
,[SomeIntColumn]
FROM [MyTableName_BULK_INSERT])

-- select count(*) from [MyTableName]
-- select top 10 * from [MyTableName]


-- ================================================
PRINT 'FINISHED'

Using bcp command to export and import a SQL Server table between databases

Here's how to use the bcp command line tool to export a SQL Server table to disk and then import into another table that may be on a different database and server.


1) Script the table definition and create in the target database (Right click table > Script table as)
CREATE TABLE [dbo].[MyExampleTable](
[Id] [uniqueidentifier] NOT NULL,
[DateCreated] [datetime] NULL,
[DateUpdated] [datetime] NULL,
[OtherExampleColumn] [varchar](100) NULL
)


2) Export source table to disk using bcp:
bcp MySourceDatabaseName.dbo.MyExampleTable out C:\SomeFolder\MyExampleTable.dat -c -t, -S localhost -T


3) Import file into target SQL table using bcp:
bcp MyTargetDatabaseName.dbo.MyExampleTable in C:\SomeFolder\MyExampleTable.dat -c -t, -S localhost -T


For further options and details, see this link:
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

Reading mongodump bson file from Spark in scala using mongo-hadoop

I couldn't find a complete Scala version using mongo-hadoop v1.3.1 to read a mongodump bson file, so here's one I prepared earlier:

val bsonData = sc.newAPIHadoopFile(
"file:///your/file.bson",
classOf[com.mongodb.hadoop.BSONFileInputFormat].asSubclass(classOf[org.apache.hadoop.mapreduce.lib.input.FileInputFormat[Object, org.bson.BSONObject]]),
classOf[Object],
classOf[org.bson.BSONObject])

Note that (for v1.3.1) we need to subclass com.mongodb.hadoop.BSONFileInputFormat to avoid this compilation error: "inferred type arguments do not conform to method newAPIHadoopFile's type parameter bounds".  This isn't required if reading from Mongo directly using com.mongodb.hadoop.MongoInputFormat.

Also, you can pass a Configuration object as a final parameter if you need to set any specific conf values.

For more bson examples see here: https://github.com/mongodb/mongo-hadoop/blob/master/BSON_README.md

For Java examples see here: http://crcsmnky.github.io/2014/07/13/mongodb-spark-input/

Import CSV file into Microsoft SQL Server via SQL command

I find this method way easier than messing with SQL Server Import/Export Wizard.  Although it can be useful to have the wizard create the target table definition first.

CREATE TABLE [dbo].[TargetTable] (
    [Id] [int] NULL,
    [StringCol] [varchar](MAX) NULL,
    [IntCol] [int] NULL
    -- additional columns here
) ON [PRIMARY]
GO


BULK INSERT [dbo].[TargetTable]
    FROM 'C:\pathto\SourceFile.csv'
    WITH
    (
        FIRSTROW = 2,             -- 2 if there are headers
        FIELDTERMINATOR = ',',    -- field delimiter
        ROWTERMINATOR = '\n',     -- next row
        ERRORFILE = 'C:\pathto\_ImportErrors.txt',
        TABLOCK
    )
GO