Yet another SQL Server enthusiast

2017-08-22

SQL Server 2017 New features: SELECT … INTO … ON [FileGroup] and Importing CSV data with OPENROWSET

Filed under: Uncategorized — sa @ 8:35 AM

SELECT … INTO Schema.TargetTable ON [FileGroup] FROM …

It creates target table on specified filegroup. Previous behavior was to create target table on default filegroup.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql

Importing CSV data with OPENROWSET

OPENROWSET function comes with a new parameter (FORMAT) to indicate file type (‘CSV’).

Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

Example:

CREATE DATABASE AppData
 ON 
 PRIMARY (NAME = N'AppData_PR', FILENAME = N'E:\DB\SQL\DATA\AppData_PR.mdf'), 
 FILEGROUP [CustomObjects] (NAME = N'AppData_CustomObjects', FILENAME = N'E:\DB\SQL\DATA\AppData_CustomObjects.ndf'), 
 FILEGROUP [Import] (NAME = N'AppData_IntegrationServices', FILENAME = N'E:\DB\SQL\DATA\AppData_Import.ndf')
 LOG ON (NAME = N'AppData_log', FILENAME = N'E:\DB\SQL\LOG\AppData_log.ldf')
GO
-- It changes default filegroup from PRIMARY to CustomObjects
ALTER DATABASE [AppData] MODIFY FILEGROUP [CustomObjects] DEFAULT 
GO

SELECT *
INTO dbo.ImportedCars_2017_08_22 ON [Import] -- New feature
FROM OPENROWSET(
 BULK N'D:\SourceFiles\cars-2017-08-22.csv',
 FORMATFILE = N'D:\SourceFiles\cars.fmt', 
 FIRSTROW = 2, 
 FORMAT = 'CSV' -- New feature
) AS cars;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: