EXPORTING AND IMPORTING Data:
The different facilities provided by sql server to do export & import.
- Export and import wizard
- Bcp [bulk copy program]
- Bulk insert [sql 2008]
(I) SQL Server Export and Import Wizard :-
Data is converted into file when we want to transfer data from one location to another.
To do export :
Select DB in object explorer
(example: DB 11 AM)
↓
Right click
↓
Select task
↓
Export data.
Wizard opens → click next→
Data Source : Select data source as SQL native client.
Server name : Name of computer where sql server install.
Authentication in SQL Server :
- Window
- Sql server authentication
Database : Name of database
Choose a destination : Target file -> Flat file destination
File name : c:\emp.txt -> Next
- Copy data from one or more file view
- Configure flat file destination
- Source table (or) view [mydb] [dbo].[emp]
- Row divimiter
- Column divimiter
BCP-BULK Copy Program :
It is command line utility[which runs on command prompt]
Syntax :- BCP dbtable/query in/out/query out target file options.
Out : Use out option to export the data to text file.
In : Use in option to import the data from text file.
EXPORTING AND IMPORTING:-
BULK INSERT (SQL 2008) :
Syntax :-
Bulk insert into cust from ‘c:\cust .txt’ With (columnterminator : ‘,’Rowterminator :’\n’).
Export Import
Import & export wizard yes yes
Bcp yes yes
Bulk insert no yes