Import & Export

Tools Overview

Tool	Type	Use
bcp	Command-line	Fast import/export of flat files
BULK INSERT	T-SQL statement	Import directly from a file in T-SQL
Import and Export Wizard	GUI (SSMS)	Simple import/export via a wizard
SSIS	ETL platform	Complex import/export with transformations and flows

---

1. bcp (Bulk Copy Program)

Command-line tool for bulk export and import.

-- Export: table to file
bcp DatabaseName.dbo.TableName out file.csv -S ServerName -T -t ":"

-- Import: file to table
bcp DatabaseName.dbo.TableName in file.csv -S ServerName -T -t ";"

Option	Meaning
out	Export from a table
in	Import into a table
-S	Server name
-T	Windows authentication (trusted connection)
-t	Delimiter (field separator)

---

2. BULK INSERT

T-SQL statement for importing a file into a table.

BULK INSERT dbo.TableName
FROM 'C:\path\to\file.csv'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2        -- skip the header row
);

---

3. SQL Server Import and Export Wizard

A GUI wizard, reachable via SSMS or directly:

Object Explorer → right-click a database → Tasks → Import Data... / Export Data...

Supports multiple sources and destinations: CSV, Excel, other databases, ...

---

4. SQL Server Integration Services (SSIS)

For complex ETL workflows with transformations, sequencing and error handling.

Core Components

Component	Function
Control Flow	Determines the order of execution (containers, tasks)
Data Flow	Processes the data (read, transform, write)
Flat File Source	Reads data from a flat file (CSV, TXT)
OLE DB Destination	Writes data to a SQL Server table
Lookup	Looks up a value in another table
Derived Column	Computes or transforms a column value
Loop Container	Repeatedly runs a set of tasks (e.g. per file)

Tips

Maximum insert commit size

Set the Maximum insert commit size to 100000 on large imports for better performance.

Data type conversion

Fields such as birthdate in a CSV are often text — use a Derived Column with substring and concatenation to convert them to the correct date data type.

Order

Use containers to sequence imports correctly. Some imports can run simultaneously — but this isn't mandatory.