Export sql rows to individual text file on a condition

1

I have created a trigger to insert the required records from one table to another.

Now I have create one text file for one SQL row on a path.

I have tried few thing but it dint work.

Can someone provide a sample on how do I do this.

The table keeps getting inserted from another table.

I need to keep creating one text file for every new row inserted.

2

Trigger would not be the best place to do such a thing.
Use a scheduled powershell or python script.
Why do you need to do this ?
What is the requirement?

3

The requirement is to export each row to a text file when it meets the condition immediately as soon as it is inserted.

4

What for what is the purpose

5

hi subbu

please see below link .. it has alternatives to xp cmdshell

1 Like
6

The text file will be pick by other software and processed.

7

hi subbu

please see this way of text file from TSQL

https://blog.sqlauthority.com/2019/08/04/how-to-write-in-text-file-from-t-sql-in-sql-server-interview-question-of-the-week-236/

1 Like
8

Any solution on this topic ?

9

one idea is start thinking of other ways ... !!!! if xp cmd shell does not work

or

may be you can discuss with
your manager
or
guys whom you are doing the work for

for alternate ways !!

HOW WAS IT BEING DONE BEFORE !!

HOW others ARE DOING IT

:+1:
:slight_smile:

10

SQL Part

use sqlteam
go

create table products(id int, name varchar(50))

create table products_logger(id int, isExported bit default(0) )


create trigger it_products on products
for insert
as
begin

	insert into products_logger(id)
	Select id from inserted
end
go

insert into products
select 1, 'rice' union
select 2, 'onion'
select 3, 'Meat'

Powershell part (schedule it to run every x Minute)

#Variables - details of the connection, stored procedure and parameters
$connectionString = "server=localhost;database='sqlteam';trusted_connection=true;";

#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = 'select p.id, name From products_logger l join products p on l.id = p.id where isexported = 0';

#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet) 

#Get single table from dataset
$data = $dataSet.Tables[0]

#Loop through each row of data and create a new file
foreach($row in $data)
{ 
    $filename = "C:\_work\TeachYourself\PowerShell\" + $row.id 
    $row | export-csv "$filename.csv" -notypeinformation   
    
    #trip exported rows
    $update = "update tgt set tgt.isexported = 1 from products_logger tgt where id = " + $row.id 
    Invoke-Sqlcmd -Query $update -ConnectionString $connectionString
    Write-Output $update
}

#Close SQL Connection
$sqlConnection.Close();
1 Like
11

another more robust approach is service broker

12

Will this trigger pick the last row inserted and create one text file for each row ?

13

Yes, try it

14

hi yosiasz

Could you please explain ( how service broker works here in this situation ) in 1 or 2 sentences how it works !!

i could google also ..

Many thanks for your help