Import data from Text File or CSV file into table is a popular task in software development area.The usual way to solve this is to use programming languague. For example, we can use CSharp, Java or anything else to read CSV file into many lines, parse string data of each lines, then insert this row into table.This is common approach way!
In SQL Server 2000 or later, it supports SQL statement to import data effectively and quickly. The SQL Statement I want to mention here is BULK INSERT.
Now, take a look at simple requirement as following
++ The input : CSV file
1,4rapid,dev,4rapiddev.com,20110501,[email protected].com 2,Hoan,Huynh,HoanHuynh,19830601,[email protected].com 3,Quynh,Nguyen,QuynhNguyen,19830701,[email protected].com |
CSV Member Info data
++ The output : Data in CVS file are inserted into below table
Table MemberInfo
Steps to import data for this sample
1. Create table MemberInfo with the below statement
CREATE TABLE MemberInfo
(
ID INT NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NULL,
FullName NVARCHAR(50) NULL,
Birthday DATETIME NULL,
Email NVARCHAR(30) NULL
)
2. Run BULK INSERT statement to import data into table MemberInfo
CREATE TABLE MemberInfo ( ID INT NOT NULL, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NULL, FullName NVARCHAR(50) NULL, Birthday DATETIME NULL, Email NVARCHAR(30) NULL ) |
Please change your Text file path on your computer.
BULK INSERT MemberInfo FROM 'D:\4RapidDevDemo_Memeber.txt' -- Text Path File WITH ( FIELDTERMINATOR = ',', -- Field terminator ROWTERMINATOR = '\n' -- Row terminator ) |
3. Perform checking imported data :
SELECT [ID] ,[FirstName] ,[LastName] ,[FullName] ,[Birthday] ,[Email] FROM [dbo].[MemberInfo] |
Table MemberInfo After Import
4. Copy full script here
USE [rapiddev.com] GO -- Create imported table CREATE TABLE MemberInfo ( ID INT NOT NULL, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NULL, FullName NVARCHAR(50) NULL, Birthday DATETIME NULL, Email NVARCHAR(30) NULL ) -- Perform importing data into table MemberInfo GO BULK INSERT MemberInfo FROM 'D:\4RapidDevDemo_Memeber.txt' -- Text Path File WITH ( FIELDTERMINATOR = ',', -- Field terminator ROWTERMINATOR = '\n' -- Row terminator ) -- Check imported data GO SELECT [ID] ,[FirstName] ,[LastName] ,[FullName] ,[Birthday] ,[Email] FROM [dbo].[MemberInfo] |
Hope it helpful for you :)!
