Published:
07 Aug 2006
|
Abstract
In this article you will learn how to import and export data using Bulk Copy Program utility. |
|
by Krishnamoorthy D
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
33287/
56
|
|
|
Introduction |
I dedicate this article to my mother Prajavathi.
This article shows how to import and export from a File to a
table/view and vice versa using SQL Server 2000 Bulk Copy Program (BCP)
utility. BCP is the fastest and easiest way to import and export from a file. This
article will be very useful to a developer by simply plugging in this stored
procedure wherever it is needed or to execute it to get the result. I have
used BCP for import/ export from a text file and a DOS shell command to execute
the BCP command.
|
How it Works |
Using the parameters which are passed to the stored
procedure, a bcp command is built dynamically and passed to the extended stored
procedure, xp_cmdshell, which calls the dos shell to execute the bcp command. The
direction can be In or Out. When you are copying data from a table/view to a
file use Out as the direction and when you are copying data from file to a
table/ view use In.
Parameter to be passed to the stored procedure
File path: Valid file path where your Sqlserver run not your
client machine.
Direction: Pass In for import or Out for export.
Table name/view name: Valid table/view name which is stored
in the current database.
Using the stored procedure you can export or import any
table/view from your database to a text file. BCP is the simplest way to
export a single table or view to a text file. When you export the data to a file
you need to give a valid file path and the table/view must be stored in it. A
file will be created and the data will export to the file. You need to specify
the direction as OUT. The file is exported in a format as character, data, and
type with tab space as a field separator and the new line as the row
terminator.
When you import from the file to a table you need to give a
valid file name which contains the data to import. The table/view should be
stored in the current database. Only a simple view, data from one single table,
can be used. You need to specify the direction as IN. The format should be of
character, data, and type with tab space as the field separator and the new
line as the row terminator.
|
BCP- Bulk copy |
BCP is used to copy the data between the SQL Server database
and the data file. Using BCP you can copy data from a table to a file or from
a file to a table.
Please refer to the on-line book under topic bcp utility to
know syntax.
Listing 1
BCP pubs.. Employee out c:\temp\export.txt’ -c -SKrishna -Usa -Psa12
In the above example pubs is the database and employee is
the table name with the
file path c:\temp\export.txt.
- c character data type
–S server name
-U username
-P password
You need to run the bcp script in the command prompt since
bcp is an operating system level command. On execution a new file will be created
under the c:\ temp as export.txt and the data will be copied.
In a similar way you can import the data from the file to
the table. Change the direction as In instead of Out and the file to import
should be of valid format since I have used –c for character data type, the default
format as /t tab space for field separator and /n new line for row terminator.
Listing 2
BCP pubs..Employee in c:\temp\import.txt -c -S Krishna -Usa -Psa12
Using the extended stored procedure xp_cmdshell to run the
BCP command, Xp_cmdshell calls the command prompt to run the bcp script. The
xp_cmdshell can be executed only through the sysadmin role or you will need to
give permission to the user to execute it.
For example:
Listing 3
Declare @str varchar(4000)
Set @str ='BCP pubs..Employee out c:\temp\export.txt -c -SKrishna –Usa –Psa12'
Exec master..xp_cmdshell @st
|
Export from a file to a table/view |
I have tested with pubs database using the table name
Employee.
Listing 4
Exec IMPEXPFROMDB ‘C:\EXP\export.txt’, ‘OUT’, ‘Employee’
A file will be created under EXP directory in the server as
export.txt in which the data will be copied. The file will be created in the
server (where Sqlserver runs) not in the client.
|
Import from a file to a table/view |
I have tested with pubs database.
Listing 5
Exec IMPEXPFROMDB ‘C:\IMP\Import.txt’, ‘IN, ‘Employee’
The file should exist in the server in the valid directory. The
file should contain the default format as tab space for field separator and new
line for row terminator.
|
Stored Procedure to Import and Export |
You need to pass three parameters, a valid file path,
direction either IN or out, and a valid table name or view name. Using these
parameters, a BCP command is built as a dynamic sql as pass to extended stored
procedure xp_cmdshell.
Listing 6
Create procedure dbo.IMPEXPFROMDB
@filepath varchar(255), --filepath
@direction varchar(5) , --direction(In/Out)
@tablename varchar(255) --Valid tablename/viewname
as
begin
set nocount on
Declare @cmd varchar(1000)
Declare @dbname varchar(1000)
/* Validation for filepath */
If (@filepath is null)
begin
Raiserror('Please enter the FilePath ',16,1)
Return
end
/* Validation for Direction */
If (@direction is null) or (@direction Not In('In','Out'))
begin
Raiserror('Please enter the Direction(In/Out)',16,1)
Return
end
/* Validation for Table name */
If (@tablename is null)
begin
Raiserror('Please enter the Tablename or Viewname',16,1)
Return
end
else if (Object_id(@tablename)is null)
begin
Raiserror('Please enter a Validate Tablename or Viewname',16,1)
Return
end
--Get the database name.
set @dbname = db_name()
--bcp command.
set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -S -U -P'
--To run the bcp using extended stored procedure.
exec master..xp_cmdshell @cmd
set nocount off
end
|
Conclusion |
Using this article you can easily import and export from a
text file. This is an alternative way, instead of DTS, when you need to copy a
large amount of data from table or view.
|
|
|
User Comments
Title:
Chido one
Name:
Mario
Date:
2007-04-27 1:35:29 PM
Comment:
I read many webs, but this is easy. Tks a lot , a lot, a lot.
|
Title:
BCP Programming
Name:
sivakumar R
Date:
2006-12-26 7:56:35 AM
Comment:
Fine , Thanks a lot
|
Title:
Example is wrong
Name:
Faiz
Date:
2006-12-11 10:19:41 PM
Comment:
Following Example wromg . Example : if you like to copy from differant server also SP displaying 2 times for table name. Thanks
|
Title:
Bulk article
Name:
Radhakanta
Date:
2006-11-14 3:09:37 AM
Comment:
nice article and is very useful.
|
Title:
BCP Article
Name:
Siva Kumari K
Date:
2006-09-13 4:07:27 AM
Comment:
Very Usefull Article, Really i wasted lot time before, thank u very much
|
Title:
BCP UTILITY
Name:
BALAJI SINGH
Date:
2006-08-15 11:45:25 AM
Comment:
Bulk made easy and understandable, with this simple article.
Thanks.
|
Title:
Now we need a BCP Format article
Name:
Darren Bates
Date:
2006-08-11 1:18:25 PM
Comment:
Thanks for the overview. What I'm trying to work with is the format file to define the data import from a text (or native) file into a table where the schema doesn't exactly match
|
Title:
Good
Name:
Manohar
Date:
2006-08-08 2:58:19 AM
Comment:
Good
|
Title:
BCP Article
Name:
G.Balasubramanian
Date:
2006-08-08 12:40:03 AM
Comment:
Very usefull, automates lot of process, thanks..
|
Title:
BCP Article
Name:
Aravind Kumar
Date:
2006-08-08 12:21:20 AM
Comment:
Very useful article. This will automate lot of process and save the worktime.
Aravind
|
Title:
Bulk article
Name:
Esmeralda Pita
Date:
2006-08-07 12:05:39 PM
Comment:
Very usufull, thanks
|
|
|
|