"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

January 09, 2010

BCP Basics

I didn’t know bcp command. I had to restore few tables. I learnt it anyways.

Bcp utility copies data between an instance of SQL Server and a data file

To bcp out data, Get data from a Table
BCP Syntax - bcp DB.SCHEMA.TableName OUT FileLocation -SServerName -T –c

T - trusted connection
c - Performs the bulk copy operation using a character data type. This option does not prompt for each field

Use TempDB
--STEP 1
--CREATE A TABLE
CREATE TABLE DBO.TEST
(
  Id INT Identity(1,1),
  Name VARCHAR(20)
)

--STEP 2
INSERT INTO DBO.TEST (Name)
Values('Data')
Go 50

--STEP 3
--From Cmd Prompt Run below query
bcp tempdb.DBO.TEST out D:\bcp_TEST.dat -T -c

To bcp in data (Restore Data to a table from a data file)
BCP Syntax - bcp DB.SCHEMA.TableName in FileLocation -SServerName -T –c

--STEP 4
CREATE TABLE DBO.TEST12
(
  Id INT Identity(1,1),
  Name VARCHAR(20)
)

--STEP 5
--To bcp in data, run from cmd prompt
bcp Tempdb.dbo.TEST12 in D:\bcp_TEST.dat -T -c

--STEP 6
SELECT * FROM TEST12

Reference - bcp Utility, BCP Basics, The ABCs of Bcp

No comments: