triggers - export inserted table data to .txt file in SQL server

Question

I want to export data of inserted table( temporary table have note of inserted data of the table) to .txt file, I used like this inside trigger

create trigger monitorTrigger on test 
for insert 
as
declare @sql varchar(8000)

SELECT @sql = 'bcp "select * from inserted" queryout I:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

exec xp_cmdshell @sql

go

this is not working since I didn't give full context(means database.shemaName.tableName) of inserted table. But the same code is working with normal table since I give full context as

declare @sql varchar(8000)

SELECT @sql = 'bcp "select * from test2.dbo.test" queryout I:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

exec xp_cmdshell @sql

I don't know how to query inserted table in bcp, anyone have any idea?

Answer 1

You can create another table for temporary storing the results from INSERTED before calling bcp.

create trigger monitorTrigger on test 
AFTER insert 
as
declare @sql varchar(8000)

--delete it every time
TRUNCATE TABLE test2.dbo.tempInserted

--populate it from inserted
INSERT INTO test2.dbo.tempInserted
SELECT * FROM INSERTED

--use it in bcp
SELECT @sql = 'bcp "select * from test2.dbo.tempInserted" queryout I:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

exec xp_cmdshell @sql

EDIT: Apparently this will not work, because table tempInserted is locked at the time bcp is called.

Here is a workaround idea, maybe not the most elegant solution but should work (if you are not on express edition). You can use trigger just to store the inserted data into this table and you can create a job that runs periodically (every 5 mins let's say) and read from that table, copy to file and delete.

So trigger would be just:

create trigger monitorTrigger on test 
AFTER insert 
as
BEGIN
  INSERT INTO test2.dbo.tempInserted
  SELECT * FROM INSERTED
END

and Stored Procedure to copy to file - that you can run from the job:

CREATE PROC transferToFile 
AS
BEGIN
 declare @sql varchar(8000)

 SELECT @sql = 'bcp "select * from test2.dbo.tempInserted" queryout I:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

 exec xp_cmdshell @sql

 --delete at the end
 TRUNCATE TABLE test2.dbo.tempInserted
END