Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | How to import CSV DATA using BULK INSERT?

In this puzzle you have to insert the CSV data into SQL SERVER table using BULK INSERT statement. For more details please refer sample input and expected output

BULK INSERT command is used to Imports a data file into a database table or view in a user-specified format in SQL Server.

Syntax

--

BULK INSERT   
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]   
      FROM 'data_file'   
     [ WITH   
    (   
  .... 
[ [ , ] FIRSTROW = first_row ] 

--

Sample INPUT

Expected OUPUT

Id Nm
1 Sharlee
2 Pawan

Use below script to create table and insert sample data into it.

--

CREATE TABLE InsertCSV
(
	 Id INT
	,Nm VARCHAR(20)
)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solutions 🙂

SOLUTION 1

--

BULK
INSERT InsertCSV
FROM 'E:\V.csv'
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM InsertCSV

--

OUTPUT 1

--

Id          Nm
----------- --------------------
1            Sharlee
2            Pawan

(2 rows affected)


--

Related Puzzle

1 https://msbiskills.com/2018/03/01/sql-puzzle-can-you-skip-header-row-in-bulk-insert/

Reference

1 https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server
2 https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements