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 Svr 2005 Express in Excel 2003 VBA code?

Hello,

how can I use SQL Svr 2005 Express as database engine in background through VBA code in Excel 2003?

I want to CREATE and DELETE tables and SELECT, INSERT and UPDATE data. Is it possible to use ADO or other database objects to get in contact with SQL Svr 2005 Express?

Thanks a lot.

Christian

Yes, ADO is still supported... but... ADO 2.0 will take care of the new sophisticated features of SQL Server 2005 where ADO 1 doesn′t have a clue of, but at the end, ADO is still supported.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

This means VBA only supports ADO 1.0 and not ADO 2.0?

Do you have a code snippet for creating a connection, creating a table and selecting data from sql server express?

Thanks a lot.

Christian

|||Sorry, I was meaning ADO.NET 2.X not the old ADO.sql

How to use SQL Svr 2005 Express in Excel 2003 VBA code?

Hello,

how can I use SQL Svr 2005 Express as database engine in background through VBA code in Excel 2003?

I want to CREATE and DELETE tables and SELECT, INSERT and UPDATE data. Is it possible to use ADO or other database objects to get in contact with SQL Svr 2005 Express?

Thanks a lot.

Christian

Yes, ADO is still supported... but... ADO 2.0 will take care of the new sophisticated features of SQL Server 2005 where ADO 1 doesn′t have a clue of, but at the end, ADO is still supported.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

This means VBA only supports ADO 1.0 and not ADO 2.0?

Do you have a code snippet for creating a connection, creating a table and selecting data from sql server express?

Thanks a lot.

Christian

|||Sorry, I was meaning ADO.NET 2.X not the old ADO.

How to Use Delete Parameters

Hey gang I am trying to use the delete feature of a sqldatasource and having issues with the delete feature. My code is below the issue is if I just call the delete and don't have a paramter in my delete statement then it deletes the records in my table. However when I add a parameter to my delete query (@.maID) and try to set it then it doesn't delete the record. What am I doing wrong? The e.CommandArgument is passing the correct record ID (which is an Integer)....can someone please help?

PS I also tried without the @. symbol before my parameter and no luck either.

DELETE FROM tempMbrAccounts WHERE (maID = @.maID) is my delete query

Public Sub OnDeleteButtonClick(ByVal senderAs Object,ByVal eAs GridViewCommandEventArgs)Handles gvEnterAcct.RowCommandDim RecIDAs Integer If e.CommandName ="Delete"Then RecID = e.CommandArgument dsInsert.DeleteParameters.Add("@.maID", RecID) dsInsert.Delete() tempAcctTable()End If End Sub

If maID is your table's primary key, SetDataKeyNames="maID" for your gridview. You should be able to use the default DelectCommand of SqlDataSource without adding extra code.|||

I should add that I an not using the standard delete button that is implemented in a GridView. I am using an imagebutton.

I do have the datakey set to maID but that isn't working either.

|||

By using an ImageButton in a TemplateField, you can still use the standard way to do delete. Here is a sample which is working . ID is the Primary Key of the test table.

<div>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="ID"DataSourceID="SqlDataSource1"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ID"HeaderText="ID" ReadOnly="True"SortExpression="ID"/><asp:BoundFieldDataField="myCol"HeaderText="myCol"SortExpression="myCol"/><asp:TemplateField><ItemTemplate><asp:ImageButtonid="DeleteImgBtn"CommandName="Delete"ImageUrl="="~/images/delete.gif"runat="server"/></ItemTemplate>

</

asp:TemplateField>

</Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"DeleteCommand="DELETE FROM [test] WHERE [ID] = @.ID"SelectCommand="SELECT [ID], [myCol] FROM [test]"><DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters></asp:SqlDataSource></div>

How to use "delete" query for more than two tables?

hi all,
i need to perform a operation where in to delete tableA,tableB based on some
conditions, here is the query,
***********************QUERY************
**************
delete from tblSS_ShiftPeriodStamps,tblSS_ShiftStamp
s where
tblSS_ShiftPeriodStamps.StartTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.EndTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.ShiftStartTime <= @.PurgeTime and
tblSS_ShiftStamps.ShiftEndTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.ObjectID = tblSS_ShiftStamps.ObjectID
***********************QUERY************
**************
how will i perform the above operation so that the checks are performed, for
the deletion of first table, i need to check in the second table also.
i would be happy if my friends could help me out...
thanks in advance,
regards,
ChakkaradeepOne object per Delete statement only.
Jens Suessmeyer.