CODEWITHSUNDEEP
sql-servert-sqldynamic-sqlbcpxp-cmdshell
Amirhossein Payon
Amirhossein Payon

Reputation: 41

bcp in SQL Server

I want to use bcp in SQL Server to transfer data of a table to a text file.

After that I want to truncate that table and move the transferred data from created text file to that table.

I want to check status (something like error status) to understand if there is are errors or exceptions when I move the data from table to text file, or move the data from text file to table (before and after I truncate the table) and rollback any thing and stop the process.

How can I do that?

Here is the query

SET @sql = 'bcp "select * from [db].[table]" queryout "C:\textFile.txt" -c -T'
EXEC master..xp_cmdshell @sql

TRUNCATE [db].[table]

SET @sql = 'bcp [db].[table] in "C:\textFile.txt" -c -T -q'
EXEC master..xp_cmdshell @sql

Upvotes: 1

Views: 502

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Based on your comment, you should use another table rather than a text file:

select *
into temp_table
from [db].[table];

truncate table [db].[table];  -- backup before you do this

-- do whatever changes you want

-- re-insert into the table

There is no need for the data to leave the database.

Upvotes: 1

Related Questions