Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Tuesday, 22 July 2014
# Monday, 17 February 2014
( BCP | SQL )

http://www.sswug.org/articles/viewarticle.aspx?id=19531

-T Trust Windows authentication to connect to the database.
-E  Keep identities
-c character
-n native
-b batch size
-e error log

Preferred using native format

Stated using this as getting errors on import with character separtors

select 'bcp ' + db_name() + '..' + name + ' out c:\temp\data\bcp\' + name + '.dat -n -SMyServerName\SQL2012 -T -e ' + name + ‘errors.txt’

select 'bcp ' + db_name() + '..' + name + ' in e:\temp\bcp\' + name + '.dat -n -S.\SQL2012 -T -E -e ' + name + 'Error.txt'

**if you get weird errors, check in ViM that the – characters are correct or just replace if copy and paste from here

out.bat > output.txt

**check the first one goes okay.  I put in an echo ‘hello’ on the 2nd line

Export using double pipe character as separator

select 'bcp ' + db_name() + '..' + name + ' out c:\temp\data\bcp\' + name + '.txt /c -t\^|^| -SMyServerName\SQL2012 -T /b500'
from sysobjects
where type = 'U'
AND name != 'tbl_TableDontWant'
AND name != 'dtproperties'
order by name

Query –> Results to File –> bcpexportall.bat

Notice the escaped double pipe for a separator.  Finding a good separator takes time.

Import

select 'bcp ' + db_name() + '..' + name + ' in e:\temp\bcp\' + name + '.txt /c -t\^|^| -S.\SQL2012 –T -E /b500 -e ' + name + 'Error.txt'
from sysobjects
where type = 'U'
AND name != 'tbl_TableDontWant'
AND name != 'dtproperties'
order by name

image
The import script

| | #