bcp in

In a previous post, i talked about getting text data out of a table with bcp utility.  That same CLI application can be use to push data into a table.  You run the utility, indicate in rather than out or outquery, you provide the path to the data file, and the destination database.

Ah, first problem.  Do we have a table to bcp into?  If we do and it has data, we might want to truncate the table.

select count(*) from MyTable
truncate table MyTable
select count(*) from MyTable

If not, we can quickly create a new blank table using t-SQL and SSMS.  Select the table, the right-click menu for “script table as.., create to…, new query editor window”.  this generates a t-sql script to create the table, you need to change the table name (at least two places, one is in comment) and then after checking the syntax, run the script.  This will create MyTable_test (you DID rename it, right?) and you can bcp into it.  

It’s actually as easy as bcp out.

exec master..xp_cmdshell 'bcp databse..MyTABLE_test in                     "E:\datbase\filename.txt" -c -T -t"|"'

although we have specified character data in our bcp export and here in import, because the database fields are specified, the utility does an implicit conversion and we get the right type of data.  This process can take several (4X?) times longer to run than the export, so plan accordingly.

 

 

Advertisements