jcollazo81
asked on
Exporting a table to CSV format... job? stored prod?
We have clients that will need their position in csv format a handful of times over the next week or two. So, we need to figure out how to export positions from SQL to CSV for the following starting with AA(like statement will grab everything starting with AA, etc), AE and CF. I created a query to show the following data that i need for the next 2 weeks.
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
I do not want to do this manually everyday. Soo i want to schedule a job to run export the data maybe a DTS package and save it as a CSV? Do i need to create a Stored procedure or something? If so how do i do that?
Sorry I am new to SQL and ideas?????? Thank you again!
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
I do not want to do this manually everyday. Soo i want to schedule a job to run export the data maybe a DTS package and save it as a CSV? Do i need to create a Stored procedure or something? If so how do i do that?
Sorry I am new to SQL and ideas?????? Thank you again!
ASKER
I'm sorry but i don't think that article helped at all... Can you be a little more clear with details :). Sorry again i am new to SQL
You can use a cursor to do this, and write to the file using xp_cmdshell. Maybe if you look at this PAQ it might help?
https://www.experts-exchange.com/questions/22404100/VB-export-data-to-fixed-width-text-file.html
If you need more help just ask.
https://www.experts-exchange.com/questions/22404100/VB-export-data-to-fixed-width-text-file.html
If you need more help just ask.
Hi,
Creating a DTS package and Scheduling it daily could be a solution.
You can create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task
create the work flow and run it.
You can refer
DTS Designer: http://msdn2.microsoft.com/en-us/library/aa176285(SQL.80).aspx
DTS Tasks: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx
Workflow: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx
This may work... i am also new to SQL
Kavi
Creating a DTS package and Scheduling it daily could be a solution.
You can create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task
create the work flow and run it.
You can refer
DTS Designer: http://msdn2.microsoft.com/en-us/library/aa176285(SQL.80).aspx
DTS Tasks: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx
Workflow: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx
This may work... i am also new to SQL
Kavi
ASKER
Hi guys, for some reason this is not working. And DTS Package as well. I created a SP... These are the columns i only need.'root Underlying exchange'.... Any idea what i am doing wrong? Table is called mmPSYMBOL
CREATE PROCEDURE spMMPSYMBOLTEST
@File VARCHAR(255)
AS
DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord SYSNAME;
DECLARE @RecordData SYSNAME;
DECLARE @FileName SYSNAME;
SET @FileName = CAST(@File as SYSNAME);
SET @HeaderRecord = 'root Underlying exchange';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT
DECLARE mmPSYMBOL CURSOR FOR
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
OPEN CustomersList
FETCH NEXT FROM CustomerList INTO @RecordData
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
EXEC master..xp_cmdshell @CommandString, NO_OUTPUT
FETCH NEXT FROM CustomerList INTO @RecordData
END
CLOSE CustomerList
DEALLOCATE CustomerList
GO
CREATE PROCEDURE spMMPSYMBOLTEST
@File VARCHAR(255)
AS
DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord SYSNAME;
DECLARE @RecordData SYSNAME;
DECLARE @FileName SYSNAME;
SET @FileName = CAST(@File as SYSNAME);
SET @HeaderRecord = 'root Underlying exchange';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT
DECLARE mmPSYMBOL CURSOR FOR
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
OPEN CustomersList
FETCH NEXT FROM CustomerList INTO @RecordData
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
EXEC master..xp_cmdshell @CommandString, NO_OUTPUT
FETCH NEXT FROM CustomerList INTO @RecordData
END
CLOSE CustomerList
DEALLOCATE CustomerList
GO
ASKER
Soo i created a SP and it got the data that i was looking for when i called on the SP on Query Analyzer.
CREATE PROCEDURE spMMPSYMBOLTEST
AS
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
GO
Now how do i get to become a job schedule. I tried to create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task.
This does not work for me any suggestions?
CREATE PROCEDURE spMMPSYMBOLTEST
AS
SELECT *
FROM mmPSYMBOL
WHERE (root LIKE 'AA%') OR
(root LIKE 'AE%') OR
(root LIKE 'CF')
GO
Now how do i get to become a job schedule. I tried to create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task.
This does not work for me any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DTS is very well suited for this task. You can then schedule the package to run every day, or whenever you want to. Following the example in this link explains you exactly how to do it. You can also enter your query in the dts package.
http://www.sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk
good luck,
dready