0

I'm using a software that generates and feeds data continuously. This data is shown in my own software and can be extracted using report generation (option available).

The limit of data storage is 500 rows, so after every 500 rows of data, I have to extract using report and then open SQL Server and use truncate (table_name) and execute it to delete it.

I want a SQL command which extracts data after every 500 rows and saves that data in a defined location and then clears (truncate) the base table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ajay Tiwari
  • 1
  • 1
    Your scenario can be achieved through Stored Procedure. [Check this for reference](https://www.mssqltips.com/sqlservertutorial/161/creating-a-simple-stored-procedure/). Count the number of records in that table. if it is equal to 500, then copy data to another location and truncate the table. – Viki888 Nov 22 '16 at 08:21
  • Do you control how data is inserted? ie: Is it possible for new data to appear between your extraction and truncate? – geofftnz Nov 22 '16 at 08:22
  • 1
    what kind of saving methodology you are looking for ?`saves that data in a defined location` – TheGameiswar Nov 22 '16 at 08:31

1 Answers1

0

In SQL Server you can do this in a stored proc using IF... ELSE construct. Here is the MSDN Documentation that explains how to use the construct...

The counting part will look like this-

IF ((SELECT COUNT(PC.id) FROM [server08].[db01].[dbo].[tblesrc01] PC) > 500) 
    BEGIN
        TRUNC ... 
        INSERT ...
    END

And for the saving data part : you can use bcp command. It allows you to export the result set from a Transact-SQL statement to a data file. The Transact-SQL statement can be any valid statement that returns a results set, such as a distributed query or a SELECT statement joining several tables.

The following example exports the names from the AdventureWorks2008R2 Person.Person table into the Contacts.txt data file. The names are ordered by last name then first name. The example is executed from the Microsoft Windows command prompt:

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T

Executing bcp inside a stored procedure--

DECLARE @job NVARCHAR(100)
SET @job ='execute_bcp' 

EXEC msdb..sp_add_job @job_name = @job, 
  @description = 'Execute bcp command', 
  @owner_login_name = 'sa',
  @delete_level = 1

EXEC msdb..sp_add_jobstep @job_name = @job,
  @step_id = 1,
  @step_name ='Command Shell Execution', @subsystem = 'CMDEXEC',
  @command = 'bcp "SELECT Name FROM [DatabaseName].[dbo].[Employees]" queryout "filepath.txt" -c -T',
  @on_success_action =1

EXEC msdb..sp_add_jobserver @job_name =@job

EXEC msdb..sp_start_job @job_name = @job
Dastagir Husain Yasin
  • 382
  • 4
  • 15