How to write inserted table records to text file using SQL Server

Data and Databases
1

I need help with saving data from an inserted table to a text file. I’m trying to do this inside a trigger but running into issues.

Here’s what I’m attempting:

create trigger dataCapture on employees 
for insert 
as
declare @command varchar(8000)

SELECT @command = 'bcp "select * from inserted" queryout C:\Output\data.txt -c -t -T -S MYSERVER\SQLEXPRESS'

exec xp_cmdshell @command

go

This approach fails because I can’t reference the inserted table directly in bcp without proper context. However, when I use a regular table with full naming convention, it works fine:

declare @command varchar(8000)

SELECT @command = 'bcp "select * from mydb.dbo.employees" queryout C:\Output\data.txt -c -t -T -S MYSERVER\SQLEXPRESS'

exec xp_cmdshell @command

The problem is that bcp doesn’t recognize the inserted table context. Has anyone found a way to export inserted table data using bcp? I’m looking for a solution that works within triggers.

2

yeah, using a temp table is a good idea! you can get the data from inserted into it, then bcp that temp table. just make sure to drop it after you’re done to keep things clean!

3

hmm, have you considered using a regular table as a staging area instead? you could insert the data from inserted into a staging table first, then run bcp on that. curious though - are you trying to capture every single insert or just specific ones? also wondering if there’s a particular reason you need bcp over other export methods?