Search notes:

BCP - export and import data on SQL Server

The following simple scripts try to demonstrate how bcp can be used to export and import data from and into tables on SQL Server.

Export and import tables

Two tables with data to be exported (exp_test_p and exp_test_c) as well as the corresponding import tables (imp_test_p and imp_test_c) are created: 
set nocount on

create table exp_test_p (
   id    integer primary key,
   txt   varchar(10)
);

create table exp_test_c (
   id_p  integer references exp_test_p,
   val   decimal(4,1)
);

select * into imp_test_p from exp_test_p;
select * into imp_test_c from exp_test_c;
Github repository about-MSSQL, path: /tools/bcp/export-import/tables.sql

Inserting data

The export tables are populated with some sample data: 
set nocount on

insert into exp_test_p values (1, 'abc');
insert into exp_test_p values (2, 'def');
insert into exp_test_p values (3, 'ghi');
insert into exp_test_p values (4, 'jkl');
insert into exp_test_p values (5, 'mno');

insert into exp_test_c values (1,  17.3);
insert into exp_test_c values (1,  12.8);
insert into exp_test_c values (2,   8.2);
insert into exp_test_c values (2,  99.9);
insert into exp_test_c values (2,  42.0);
insert into exp_test_c values (3, - 8.2);
insert into exp_test_c values (5,  34.9);
insert into exp_test_c values (5,  60.0);
Github repository about-MSSQL, path: /tools/bcp/export-import/data.sql

Exporting the data

The following batch file exports a subset of the data into the files data_p.exp and data_c.exp.
The subset of the data is specified with the variable %CONDITION_C%.
The -t flag specifies the field separator. In our case, the vertical bar.
The -T flag tells bcp to use a trusted connection. 
setlocal

rem
rem     Condition to select only select a subset of (child-) data:
rem
set CONDITION_C=val between 10 and 40

rem
rem     Create SQL statement for parent data:
rem
set SQLSTMT=                                                  ^
  select                                                      ^
     *                                                        ^
  from                                                        ^
     exp_test_p                                               ^
  where                                                       ^
     id in (select id_p from exp_test_c where %CONDITION_C%)

rem
rem     Export data
rem
bcp "%SQLSTMT%" queryout data_p.exp -c -T -t"|" -d %DB%


rem
rem     Create SQL statement for child data:
rem
set SQLSTMT=                                                  ^
  select                                                      ^
     *                                                        ^
  from                                                        ^
     exp_test_c                                               ^
  where                                                       ^
     %CONDITION_C%

rem
rem     Export data
rem
bcp "%SQLSTMT%" queryout data_c.exp -c -T -t"|" -d %DB%
Github repository about-MSSQL, path: /tools/bcp/export-import/export.bat

Importing the data

The data in data_p.exp and data_c.exp can now be imported into the destination tables: 
bcp imp_test_c in data_c.exp -T -c -t"|" -d %DB%
bcp imp_test_p in data_p.exp -T -c -t"|" -d %DB%
Github repository about-MSSQL, path: /tools/bcp/export-import/import.bat

See also

SQL Server: Import / export

Index