SQL Server - Import or Export data in sql server using BCP

Asked By aman on 06-Sep-11 01:43 AM
hi all,

how exactly i can do perform task of import and export data using BCP in sql server.
what exactly BCP performs?
can anyone provide a simple example on the same, it would be great to have example soo that i can understand it in better manner.

thanks,
Aman
smr replied to aman on 06-Sep-11 01:49 AM
HI

The http://msdn.microsoft.com/en-us/library/ms162802.aspx (bcp) utility in Microsoft SQL Server enables database administrators to import bulk data into a table or export it from a table into a file. It also supports several options that define how data will be exported, where it will be imported and which data will be loaded.

The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp utility performs the following tasks:

  • Bulk exports data from a SQL Server table into a data file.

  • Bulk exports data from a query.

  • Bulk imports data from a data file into a SQL Server table.

  • Generates format files.

refer
http://community.spiceworks.com/scripts/show/534-sql-server-bcp-import-and-export-data
http://msdn.microsoft.com/en-us/library/aa337544.aspx
Reena Jain replied to aman on 06-Sep-11 02:39 AM
Hi,

Best way is to use to use import and export wizard and copy data from one database to other db

the other way is

select * into [db2.new_table] from [db1.old_table]//if table doesnt exists
//just need to specify the database name with the table 
// just below one 
    
  insert into [database1].dbo.[TableName] 
   Select * from [database2].dbo.[TableName]//if table exists 
  //where tablename table are in database database1 and database2 
  // here we are fetching record from database2 from table tablename 
  // and insert this record into table tablename into database database2 
hope this will help you