Delete Records In A Table Using Stored Procedure
Feb 26, 2006Hi, I would like to delete some records in a table older than a specified date using a stored procedure. Can anyone help?
Thank you,
Cynthia
ADVERTISEMENT
Stored Procedure - Delete Some Rows (table)
Dec 5, 2006How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..
Code:
CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw
/***************************************************************
** CREATED: 12/04/2006
****************************************************************
** DESCRIPTION: Clean the tblSoldRaw by deleting any data that
** is over 90 days old. We need keep the database
** as small as possible so the performance won't
** suffer on the server's and the client's machine.
**
****************************************************************
** NOTES: Hooked up to BookItOut Data Importer Windows Service
****************************************************************
** MODIFICATIONS:
**
** DATE WHO MODIFICATION DESCRIPTION
** -------------------------------------------------------------
**
***************************************************************/
/***************************************************************
** Uses: tblSoldRaw
***************************************************************/
AS
DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90)
RETURN ROWCOUNT
ERROR_HANDLER:
RETURN -1
SET NOCOUNT OFF
Thanks...
Stored Procedure For Archiving The Records In Another Table
Mar 27, 2008I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved
1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column
Pls provide me with full stored procedure code.
Can I Insert Records Into A Table From A Stored Procedure
Oct 9, 2007I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.
I use the logic in other sprocs too.
Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....
INSERT INTO Insurance (Name)
Exec sp_AddNamesList
Thanks,
Mike
Create A String Of Records From A Table In A Stored Procedure,
Jul 20, 2005I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View 6 Replies View RelatedTransact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?
Jun 29, 2015I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
HOW TO: Delete All Records From A Table Where A Child Record From Another Table Does Not Exist.
Mar 17, 2008I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
How To Delete The Records In The Table
Jul 20, 2005:confused: Urgent!!
Hi there. I use MS SQL server. I would like to separate the data from one table to two tables refer to two reference tables and the following conditions:
Let say these two reference tables are called:
Table A & Table B
Group A:
1. Same date in Table A & Table B
2. Same ID in Table A & Table B (ID is not unique)
3. Same name in Table A & Table B (Name is not unique)
Combine all of these three conditions for unique identifier.
I used the following SQL code to separate the required data that match the above conditions to the new table.
(Code)
select a.Project, a.Site, a.S_number, a.Field_ID, a.Method, a.Analyte, a.Result, a.Units,
a.Qualifier, a.Dilution_Factor, a.Reporting_limit, a.Recovery_, a.Matrix, a.CAS_Number, a.Sample_Date,
a.Received_Date, a.Prep_Date, a.Analysis_Date, a.Batch_ID, a.Data_Package_num_SDG, a.Lab_Sample_ID,
a.Lab into APPL_union_exist from APPL_union_update a, Before_01012004_report b
where a.Field_ID = b.[Field Sample] and a.Sample_Date = b.Collected and a.Analyte = b.Analyte
However, I don't know how to delete the data that copied to the new table in original table, or separate that to the new table. Wish someone can help me. Thanks a lot
How To Delete Duplicate Records From A Table
Nov 29, 2004Hello All,
Please show me how to delete duplicate records from a table.
Thanks in Advance
How Can I Delete Dupilcate Records In A Table
Oct 6, 2006hi,
i have a table in which 100 rows are there . i have duplicate rows. so ho can i remove it . suggest me a command
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
How To Delete Duplicate Records From A Table ?
Jul 20, 2005I uploaded some data about 2 or 3 times and it keep appending it to thetable.Now I want to keep only first duplicate and delete rest of.Suppose part number 123 has been added 3 times so I want to keep only 1record.Thanks
View 1 Replies View RelatedHow To Delete Duplicate Records From Table ?
Mar 4, 2008Hello Frnds....Can anybody give the answer of this question as How to Delete duplicate records from Table ?
I Know that with check option and also with Unique Constraint we can avoid to enter duplicate records in table but How to delete from table which does not have any constraints ?
Considerations When Delete Records From Table.
Nov 15, 2006Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).
Is this mechanism safe to the dbms?
Aren't there round-robin(?) style table?
Delete Records In Destination Table
Jul 2, 2007Short Question: How do I delete all records from a destination table prior to appending new data to that table?
I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc.
I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database.
Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.
Unable To Update Or Delete SQL Table Records
Jun 13, 2007Hello,
I have been serching for weeks to resolve this problem. I am new to ASP.NET and trying to make the migration from ASP which I have programmed in for years. I am using Microsoft Visual Web Developer 2005 Express Edition and SQL Express Edtion. I have been working through the Microsoft Video Training at http://msdn.microsoft.com/vstudio/express/beginner/learningpath/ and created a web site using Tier 3 Lesson 8 as the model. My new web site which is a simple phone book applicaiton lets me read the table and select the record without any problem. But the update form lets me edit but when I attempt to Apply the update I get the following error.
Server Error in '/Phonebook' Application.
ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Update' that has parameters: FirstName, LastName, PhoneNumber, BossGroup, Department, BossPickup, ShowInPhonebook, Type, Original_FirstName, Original_LastName, Original_PhoneNumber, Original_BossGroup, Original_Department, Original_BossPickup, Original_ShowInPhoneBook, Original_Type, Original_ItemID.
Description: An unhundled expception occured during the execution of the current web request. Please review the stack trace for more information about the error and where it originiated in the code.
The Stack Trace basiclly showes the same error as above.
Also, when I attempt to delete the record I do not get an error but the record does not delete.
What is interesting is that I can add a record so I do not believe that it is a security permissions issue. I have the ISS Authinication Method Enable Anonymous Access set on with full control.
If anyone has any insight as to why this is occuring please let me know.
Delete Records In A Table Older Than 90 Days . Help
Aug 5, 2002Hi ,
I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .
Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .
Any thoughts how to do it through query and schedule it ???
Thanks
Delete Records In SQL Table, Which Are Present In Excel
May 5, 2006Hi everyone,
I have a task to create a dts package, which will delete records in a SQL table, which are present on a sheet tab in Excel. I know how to transfer records from Excel to SQL table and vice versa, but not sure how I can delete the records in SQL table, which are present in Excel. Note that there are just 2 columns - one is a key and the other name.
Thank you all in advance,
Saurav
Delete Records From A Massive Table (heap)
May 21, 2013So I've stumbled across an audit table on one of our systems that has reached a hearty 180M rows in size.
The table is a heap (no indexes whatsoever).
Each record has a datetime value indicating when it was created.
I need to delete everything that was created prior to the last 6 months; what is my best plan of attack?
T-SQL (SS2K8) :: Delete Table Records With Backup?
May 2, 2014using below query
DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30
am able to delete old records morethan 30 days, but i want to results to be saved in file.
before deleting i want to a craete a file and save the to be deleted records.
Cannot Delete Records In High Cardinality Table
Jul 22, 2013DELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]
gives this error
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SCS_NAV2009R2_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
~ 7,000,000 records
now i have set the log to autogrow and set the max size to 2tb
what else can i do to get this going?
The Best Way To Delete A Huge Number Of Records In Table
May 30, 2008Hi Everyone,
We have a large test database with million of records for more than company site Code. Sometime we want to refresh the data of that database for one or more site Codes.
In order to do that I have to delete all records of the site code we want to refresh on the test database first then copy a new set of data from production database over. Since we refresh data based on the site code therefore I have to use the Delete command instead of Truncate.
Since this is a huge database with thousand of tables and million of records per table I have a performance issues with delete command. So what would be the best to delete a large number of records without writing any information to database log file?
FYI: The Recovery model of this database is Simple
Regards,
Jdang
Resetting The Id (key Field) To 1 When Using DELETE To Clear Records From A Table
Feb 17, 2008Is there a way that I can reset the key field to 1 when using DELETE to clear a table?
(Note: if there is a separate command that I could use after the DELETE, that is fine too.)
Thank you for your help with this,
-DJ
SQL Server 2008 :: Job To Delete All Records Then Copy All From Another Table?
Apr 22, 2015I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END
Delete Records From A Table Using Records In Another Table.
Jul 20, 2005I have been searching many postings and I cant seem to find anyonethat has this answer so I decided to post. I am using SQL(Transact-SQL).If I have 2 tables with columnsacct_num,activity_date,and pay_amt and I want to delete one instanceof a record in table 1 for every instance of that record in table 2how could I do that. For example.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00Table 2-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00I need a delete statement that will find 2 of the 5 records(It doesn'tmatter which 2) and delete them.Leaving table one looking like this.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00How can I do this??
View 7 Replies View RelatedStored Procedure For Delete
Jul 25, 2001Hi,
I need to create a stored procedure which would
delete just the photo of a student once he graduates.
I found out that a delete SQL statement will delete a whole row.
Is there a way to just delete the Photo and leave the rest of the data
untouched.
Thanks.
Regds,
Ran
Little Help On Delete Stored Procedure
Jul 14, 2004I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table
Please help!
************************************************** ***
My contrat DDL is follows
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
************************************************** ***
My insert stored procedure is follows
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char],
@contactId[int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @contactId = contactId
and @studentId = studentId)
begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
go
Stored Procedure Using Delete Row
May 31, 2006I want to delete some rows in a table using a Stored procedure. I am using MS sql 2000 using Enterprise manager. I want to delete all rows that have col. status listed as shipped.
/Create Procedure [DeleteOld] AS
/
/Delete Equipment
/FROM Yahoo
/WHERE Status <> 'shipped'
I have two rows of data, one row has a col. names status, and in that row of data says "shipped". The other row says "hold" in the place of "shipped".
The procedure is written, and is setting under that database ready to be run. I do not get any error's when I excute the procedure from SQL query, but it just does nothing when run!
I need help to make this procedure work properly
Thank You,
Ernie
P.S. I am new to this forum!
Delete Stored Procedure
Sep 24, 2007i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up
View 7 Replies View RelatedNeed Help With Delete Stored Procedure
Mar 9, 2008Hi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.
How can I delete the results of this procedure? Thanks!
SELECT rssName, [Days Old] FROM
(SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime
SQL Server 2012 :: Delete Unmatching Records In Child Table
Feb 24, 2015I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
Using SSIS With SqlBulkCopy For ETL: How To Delete Processed Records From Staging Table
Jul 27, 2007I'm looking at SSIS and SqlBulkCopy as a possible method to quickly insert and process large amounts of data, my current method uses the sp_xml_preparedocument and OPENXML to parse an xml document of the data I want to process and insert into the database, however I'm noticing the performance of SqlServer parsing the xml document isn't that good.
I found the C# SqlBulkCopy method (new in .NET 2.0) and I was thinking it would be faster to use that to load my data into a staging table and then use SSIS to extract the data from the staging table, process and transform it as necessary and finally load it into the final destination tables. I was able to create an Integration Services Project that selects the data from the staging table, does a bit of processing on one of the fields (by calling a stored procedure), and finally loads the processed data into the final table.
The problem with this is I need to clean out the rows that were extracted from the staging table and I'm not sure how I can accomplish this (and I can't just issue a "delete from staging_table" because there maybe new records in the staging table that were not processed), perhaps I can either delete each record as it is proccessed or somehow get the last proccessed identity id from the staging table and delete all records less than or equal to that id.
thanks in advance for any help you can provide, maybe there is an easy way to accomplish what I'm trying to do.
-Matt Palmerlee
Stored Procedure To Delete Data Which Is 2 Yrs Old
Sep 24, 2007Table Strucure - :
TestEven(TestNo,EmpNo,TestDate)
I jst want to write a stored procedure to delete all data which is 2 years old. thank you in adavance.
Hussain
SQL Server 2008 :: Archive And Delete Old Records In Table With No Date Column
Feb 4, 2015I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). How to archive the data and then delete the archive data from both tables.
View 9 Replies View Related