Automatic query to export data sql 2008 ...

Question

Hi guys,

I'm really a newbie in SQL, I hope you can help me with this.

I know how to export data with server management studio, but now I have to do this on daily basis and are several tables so takes too much time. I want an implement some query or job procedure to make this automatic.

The options I use:

And for the tables use:

There is possible to do something to do that automatically???

Thanks so much !!!

Kind regards.

Answer 1

Yes, you can add headers and text qualifiers too, but I shall be honest to say that it's not really beginner-level material.

Anyway, I have an article on my web site about bulkload, and includes examples for creating files with headers: https://www.sommarskog.se/bulkload.html.

Answer 2

Hello Carlos and Happy New year

Your request includes two tasks which you need to learn about: (1) Export the data as csv - we will use a small Utility which come with SQL Server: bcp in order to export the data to csv file (1) SQL Server Agent Job - Automat execution of the bcp each day or in any interval you want, using SQL Server Agent Job

Step one: Find the bcp command which your needs

bcp is a small utility which can be execute from the command line or any other command shell. It comes as part of the Microsoft Command Line Utilities. Before we automated the procedure, first confirm that you know how to use bcp in order to get the result which fit your needs.

https://learn.microsoft.com/en-us/sql/tools/bcp-utility

Step 2: Execute the bcp from the SQL Server Agent Job (replace the information in the following sample to fit your server)

USE msdb ;    
GO    
EXEC dbo.sp_add_job    
    @job_name = N'QnA answer' ;    
GO  
  
-- replace the following information according to your server   
-- User_Name; Your_Password; Server_Path; Instance_Name; Tabel_Name  
EXEC msdb.dbo.sp_add_jobstep   
    @job_name = N'QnA answer',   
 @step_name=N'Execute bcp',  
 @cmdexec_success_code=0,   
 @on_success_action=1,   
 @on_fail_action=2,   
 @retry_attempts=0,   
 @retry_interval=0,   
 @os_run_priority=0,   
 @subsystem=N'PowerShell',   
 @command=N'bcp Instance_Name.dbo.Tabel_Name out "C:\qq\table_name.csv" -c -U User_Name -S Server_Path -P Your_Password -C 65001',   
 @database_name=N'master',   
 @flags=0  
GO  
  
EXEC msdb.dbo.sp_add_jobschedule   
    @job_name = N'QnA answer',  
 @name=N'RunInterval',   
 @enabled=1,   
 @freq_type=4,   
 @freq_interval=1,   
 @freq_subday_type=1,   
 @freq_subday_interval=0,   
 @freq_relative_interval=0,   
 @freq_recurrence_factor=1,   
 @active_start_date=20211230,   
 @active_end_date=99991231,   
 @active_start_time=0,   
 @active_end_time=235959  
GO  
  
EXEC sp_attach_schedule    
    @job_name = N'QnA answer',  
 @schedule_name = N'RunInterval';    
GO    
  
EXEC dbo.sp_add_jobserver    
    @job_name = N'QnA answer'  
GO  

Answer 3

Hi @Carlos Arizmendi ,

It is suggested that you can create a job

additional: Automated Export to CSV Using SQL Server Management Studio

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Perfect !!!

Thanks so much !!!

I have a doubt related to BCP:

I create this command:

bcp TestDataBase.dbo.TestTable out c:\Penny\TestTable.csv -c -t "~" -T

But have 2 questions:

1. I won't make all the data with text qualifier with "
2. Its possible to export headers to ???

Thanks so much !!!

Kind regards.

Answer 5

Perfect !!!

Thanks so much !!!