Post reply

Simple Store Procedure for delete rows in a SQL 2012 table

  • June 9, 2016 at 4:48 pm

    #319374

    Hello,

    I want to create a store procedure, which I have never done before and intimidating. I want to delete all the rows in a table my coding will be below

    USE [ATAS00_app]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Delete rows in General Ledger table

    DELETE dbo.GLDetl

    Do I need to add/code anything else will the table be close automatically? Do I have to worry about anything maintenance on the table? Thank you in advance

  • June 10, 2016 at 12:38 am

    #1884288

    why you need the stored procedure to delete your tables from your code you are deleting all the rows.

    what you want to do please explain in detail so that will help you with your query.

  • June 10, 2016 at 12:48 am

    #1884290

    How many rows are in your table?

    You could do something simple like

    DELETE FROM MyDB.dbo.MyTable WHERE <filter>...

    but if you are deleting a lot of rows, there may be better ways to do it.

  • June 10, 2016 at 12:35 pm

    #1884469

    Thanks subramanian.esakkimuthu 40485

    for your reply. What I really wanted to do is delete (Turncate) the rows from the table and insert data from db2 table on a nightly basis (unfortunately there is no field on that table I can use to update new data only into SQL table) I want to use SSIS package to do this. This is my first atempt on doing this. If you have any suggestion or anyone else I would appreciate your insights. Thanks again.

  • June 10, 2016 at 12:39 pm

    #1884470

    Are you aware of the TRUNCATE TABLE statement? It might be a better option if you're deleting all the rows in a table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 10, 2016 at 12:49 pm

    #1884474

    Hi,

    There is about 25,000 rows and growing I estimate about 60,000 rows at year end. What I really wanted to do is delete (Turncate) the rows from the table and insert data from db2 table on a nightly basis (unfortunately there is no field on that table I can use to update new data only into SQL table) I want to use SSIS package to do this nightly. This is my first atempt on doing this. If you have any suggestion or anyone else I would appreciate your insights. Thanks again.

  • June 10, 2016 at 3:41 pm

    #1884488

    Yes, and thank you.

  • June 11, 2016 at 5:23 am

    #1884500

    you can use truncate table "tablename"

    this statement will remove all the rows in the table.

  • June 11, 2016 at 1:03 pm

    #1884512

    Thanks your right, that is what I am going to do. Thanks again.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply