How to use TOP in DELETE

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

thk.

Hi,

1. not sure about this one...
2. delete top(100) [tablename] where ........

James Steele

|||

One approach for each question:

Q1:

In SQL Server 2005, we can use ROW_NUMBER() function

SELECT ROW_NUMBER() OVER(ORDER BY id ) AS Row_Number, * FROM yourTable WHERE Row_Number<=100 -- first 100

-----

WHERE Row_Number>100 and Row_Number<=200 --100-200

Q2:

DELETE

tbl_test1WHERE IDIN(

SELECT

TOP 100 IDFROM tbl_test1ORDERBY id)|||

To select the next 100 records, try using ROW_NUMBER(). You can get details from Books Online, or the article below shows how to use it for custom paging.

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

|||

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

New in SQL Server 2005, you can use TOP along with DELETE.

DELETE TOP (100) FROM yourTable

works!

But in SQL Server 2000, this one will not work.

In SQL Server 2000, use this instead:

DELETE yourTable FROM (SELECT TOP 10 * FROM yourTable) AS t1 WHERE yourTable.id=t1.id

How to use Sql Servers Temp table in vb.net

There r two types of temporary tables in sql servers
1) Local temporary table (#tablename)
2) Global temporary table(##tablename)

but when i m creating any temp table & using it in .NET it doesn't store all the data..
i am storing data one by one but only last inserted row is visible in that table...

any solution on that...

pls help

Thanks in advance...

Mukund TambeMake sure that you use one connection only, and that you do not close the connection between the inserts. Local temporary tables (#) are deleted when the connection through which they were created are closed. Global temporary tables (##) are deleted when the last connection referencing them are closed.|||Make sure that you use one connection only, and that you do not close the connection between the inserts. Local temporary tables (#) are deleted when the connection through which they were created are closed. Global temporary tables (##) are deleted when the last connection referencing them are closed.
let me check..

thanks a lot roac..|||You may also be a victim of "connection pooling" if you are running in an environment like ASP where you can have many threads sharing access to the same database. Each call to the database needs to stand on its own in a connection pooling environment, you can't depend on what may or may not have happened in a previous call to the database when you use connection pooling.

-PatP|||You have to let us know what you're doing...because it sounds dicey to begin with

Can you write a stored procedure?sql

how to use @tablename

i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @.tableName but i m unable to do so..
it says u need to define @.tablename

heres da code
CREATE PROCEDURE validateChildId
(
@.childId int,
@.tableName varchar(50),
@.fid int output
)
AS
(
SELECT @.fid=fid FROM @.tableName
where @.childId= childId
)

if @.@.rowcount<1
SELECT
@.fid = 0
GOyou need to write a Dynamic SQL Statement for this.

Declare @.SQL VarChar(1000)

SELECT @.SQL = 'SELECT * FROM '
SELECT @.SQL = @.SQL + @.TableName

Exec ( @.SQL).

Something like this.

Do let me know if you want the code for your scenario.

Thanks
Shankar|||exec ('Select * from ' + @.TableName)

...is, for its brevity and wanton violation of sound development principles, arguably the most concentrated example of bad code one could write.

I would no more encourage you to do this than I would recommend one brand of cigarettes over another.|||i understand its the crudest way of writing code, if there's any other method which can solve this problem, please do let me know. I can Improve my knowledge too. :-)|||I wasn't disparaging your code, which effectively solves the problem that was presented.

The solution isn't the problem. The problem is the problem.

It just isn't a good idea to develop applications with this type of functionality. It opens a gaping hole into the database that user's are free to jam anything into, intentionally or inadvertently.

Waqas needs to reconsider the design of his application.

I apologize if I inadvertently offended you.