如何每秒自动将SQL表导出到* .CSV文件 [英] How to export SQL table to *.CSV file automatically for every sec

问题描述

我是SQL和SSIS工作的新手。我想使用SSIS将SQL数据表动态导出到CSV文件 everysec 。列名称RECIPE STATUS之一，值为1或2（int）（1为新配方，2为旧配方），另一列名为RECIPE NAME，值为AAABBB（varchar）更多列值为数据库表，只有一行数据avaialble，它将改变ev秒。因此我们尝试将其导出到csv文件以记录不同/唯一的RECIPENAMES和数据进行分析。



表架构是



SELECT TOP 5 [RowID]

，[RowInsertTime]

，[TransId]

，[RecipeStatus]

，[RecipeName]

，[RecipeTagName]

，[Value]

，[ReadStatus]

，[sData1]

，[sData2]

，[sData3]

，[nData1]

，[nData2]

，[nData3]

来自[MES]。[dbo]。[MESWrite_RecipeData]



导出时，根据RECIPE STATUS值（如果为1），然后在CSV中插入/创建一个新行并导出。 （它是一个简单的插入TSQL语句，插入csvfile（来自数据库表的值）



如果值2表示RECIPENAME值已经存在于CSV中的AAABBB。所以查找并更新其他列值。所以在这种情况下不要在csv中创建一个新行（我可以说使用数据库表中的值更新所有其他列到csv，其中RECIPENAME =AAABBB），如T-SQL中的更新查询



。最后如果我们必须将此SSIS包发送给客户，它可以是可执行文件。或者如何设置安全？请帮助我..

I am new to SQL and SSIS work. I'd like to dynamically export SQL datatable to a CSV file everysec using SSIS. one of column name RECIPE STATUS, value is 1 OR 2 (int) (1 is new Recipe, 2 is old recipe which is existed), Another column name is RECIPE NAME, value is AAABBB (varchar) some more columns with values In Database table, only one row data avaialble, it will be changed ev sec. So we are trying to export it to csv file to log different/unique RECIPENAMES and data for analysis.

Table Schema is

SELECT TOP 5 [RowID]
,[RowInsertTime]
,[TransId]
,[RecipeStatus]
,[RecipeName]
,[RecipeTagName]
,[Value]
,[ReadStatus]
,[sData1]
,[sData2]
,[sData3]
,[nData1]
,[nData2]
,[nData3]
FROM [MES].[dbo].[MESWrite_RecipeData]

While exporting, based on RECIPE STATUS value if 1, then Insert/create a new row in CSV and export. (Its a simple insert TSQL statement, insert into csvfile (values from databsetable)

if value 2 means RECIPENAME value is AAABBB existed in CSV already. so find and update other columns values. so in this case don't create a new row in csv. (i can say update all othercolumns with values from database table to csv where RECIPENAME="AAABBB") like Update Query in T-SQL

. finally if we have to send this SSIS package to a customer, it can be executable file. or how to make secured? Please help me ..

推荐答案

这需要一个两步的过程：

过程一：向主表中添加一个触发更新的触发器并将其写入另一个数据库表进入导出文件。



过程二：在第二个表上添加一个触发器，只有在插入记录时才会将文件导出到磁盘。我不是看到任何理由尝试更多地导出文件通常比这更好。



替代流程二：教他们谁想要这个如何从他想要查看CSV文件的任何地方查看新数据库表。例如，您可以将SQL Server表直接添加到Excel电子表格中，而无需通过CSV。



祝你好运

This will need to be a two step process:
Process one: Add a trigger to your main table that catches the updates and writes to another database table everything that should go in the export file.

Process two: Add a trigger on that second table that will export the file to disk ONLY when a record was inserted. I don't see any reason to try and export the file more often than that.

Alternate process two: Teach whoever wants this how to look at your new database table from wherever he wants to look at the CSV file. For example, you can add a SQL Server table directly into an Excel Spreadsheet without having to go through the CSV.

Good Luck

这篇关于如何每秒自动将SQL表导出到* .CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！