:::: MENU ::::

Posts Categorized / ETL

  • Apr 11 / 2013
  • 0
dbDigger, ETL, T-SQL Interview Questions, T-SQL Tips and Tricks

Using TOP in DELETE statement for T-SQL

Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.

-- Create a table with only one column in it  
 CREATE TABLE DemoTable (id SMALLINT identity(1, 1))  
 GO  
 -- Insert 100 values in identity column  
 INSERT INTO DemoTable DEFAULT VALUES   
 GO 100  
 -- Verify the values   
 SELECT * FROM DemoTable  
 GO  
 -- Try to delete statement for top 10 rows  
 DELETE TOP 10 FROM DemoTable  

but following error message is generated

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ’10’.

It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?

Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.

 -- Using TOP in delete statement  
 DELETE TOP (10) FROM DemoTable  

So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.

 -- Drop DemoTable  
 DROP TABLE DemoTable  

However it is notable that according to BOL
“TOP cannot be used together with UPDATE and DELETE statements on partitioned views.”.

  • Jul 27 / 2011
  • 0
dbDigger, ETL, System Administration

How to easily get row count in a flat file (.txt file)

Note pad is handy tool for data transportation processes. It lacks fancy editing and formatting tools but is simple and efficient. If you have got a .txt file and it is required to get total data rows in the file, then go through following couple of short cut keys.
Assuming that .txt file is opened on your PC.

  1. Press Cntrl + End
  2. Press Cntrl + G

It would give you total number of rows + 1, in your flat file. e.g If generated  information box showed 3405 then 3404 is number of total rows in that flat file.

How to easily get row count in a flat file

Consult us to explore the Databases. Contact us