Text file output for few sql entries

1

I am looking for text file output in path when ever there is a new entry based on a condition.

Please advise on how to achieve this and a sample if possible.

2

Ssis or powershell script running on schedule . Reads table, based on conditions exports to file.

Maybe have another table that keeps track of the exported row so it is not exported again

3

hi

first please explain what you mean by text file output for few SQL entries ..

are you saying you have a table with rows ...
those sort of details help us understand clearly !!

people on this forum are highly senior and experienced .. they can suggest a solution or Solution's

4

Thanks Yosiasz.

I need one text file in path for every new insertion on the sql table. The text file should contain only the particular row. For example if there are 100 rows inserted, I need 100 text files in the path.

Are you able provide me some sample scripts please.

5

Thanks Harish for your reply.

I have a live sql database where the data will be via the application. I need to output the particular row whenever the data is inserted whenever the condition is met.

For example if there are 20 stores, I need to monitor 4 stores. So whenever there is a new row inserted for that 4 stores, I need to export 4 text files to a shared path.

6

what are you using to populate the data, stored procedures or is it an application layer via something like entity framework ..... ? Please answer this first?

Here are possible 2 ways this can be done. The below methods are just to get your appetite whetted.

  • with powershell
$filename = "C:\_work\TeachYourself\PowerShell\juicy"
$query = "SELECT * FROM [sqlteam].[dbo].[results]" 
Invoke-Sqlcmd -Query $query -ServerInstance "localhost" |
Export-Csv -Path "$filename.csv" -NoTypeInformation
  • with python
import pyodbc 

def fetch_table_data(table_name):
    cnx = pyodbc.connect("Driver={SQL Server};"               
               "Server=localhost;"
               "Database=sqlteam;"
               "username=sqlteam;"
               "password=sqlteam;"
               "Trusted_Connection=yes;")
    cursor = cnx.cursor()
    
    cursor.execute('select * from ' + table_name)

    header = [row[0] for row in cursor.description]

    rows = cursor.fetchall()

    # Closing connection
    cnx.close()

    return header, rows


def export(table_name):
    header, rows = fetch_table_data(table_name)

    # Create csv file
    f = open(table_name + '.csv', 'w')

    # Write header
    f.write(','.join(header) + '\n')

    for row in rows:
        f.write(','.join(str(r) for r in row) + '\n')

    f.close()
    print(str(len(rows)) + ' rows written successfully to ' + f.name)


# Tables to be exported
export('results')
7

from the back end .. i mean database

one idea is to ..

create a trigger .. on insert into table .. output inserted to file using SP ..

8

Yes its a software layer which will receive the data from hardware and insert into Sql database.

How do I lookup for new data inserted for particular store out of many.

9

Thanks Harish I will try the SQL triggers.

Does it have an option to monitor insertion from particular store for example.

10

you can code for that ..

add a filter

where store = @store

@store is variable parameter

11

Thank you

12

hi subuu

I can "remote desktop" to your computer and .. show you or do it !!!!

what ever you want !!

if you are not able to do !!!

13

Thanks Harish !

14

Yes but does it use a stored procedure to insert new data or straight sql insert?

Either way you will probably need a logging table to keep track of these new records by adding them from the stored procedure that populates the target table or by triggers.
Otherwise doing a direct save to file from a trigger sounds out of place imo. What if record is rolled back etc

15

Hi Harish

I have the software and database setup now and tried few things.

Can you please help on this ?

And how do I contact you and provide remote session.

16

Hi Yosiasz

I have tried the powershell script, but it hasnt created the csv file.

And also the data which was supposed to be inserted in the table is not available.

Once I have deleted the trigger, I found the data is inserted into the table and available.

17

Show us your trigger and what does it do?

18

I have used your powershell script.

$filename = "C:\test"
$query = "SELECT * FROM [sqlteam].[dbo].[results]"
Invoke-Sqlcmd -Query $query -ServerInstance "localhost" |
Export-Csv -Path "$filename.csv" -NoTypeInformation

19

show us your trigger

20

I had ran the powershell script directly.

And tried creating the sql trigger as below

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

SELECT @sql = 'bcp "select * from inserted" queryout c:/test.txt -c -t -T -S localhost\SQLEXPRESS'

exec xp_cmdshell @sql

go