EXPORTING AND IMPORTING Data:

The different facilities provided by sql server to do export & import.

  1. Export and import wizard
  2. Bcp [bulk copy program]
  3. Bulk insert

(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