Deleting Duplicate Records.
I need a sql statement to delete duplicate records.
I have a college table with all colleges in the nation.
I noticed that all of the colleges were listed twice.
How do I delete all of the duplicate records.
Here is my table.
Colleges
-------------------
schoolID - smallint NOT NULL,
schoolName - varchar(60) NULL
Can someone help me out with the sql statement???
I'm running SQL Server 6.5.
- ted
View Complete Forum Thread with Replies
Related Forum Messages:
Deleting Duplicate Records
Hi All, I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records. It is something like this, MyCol 7 7 7 7 7 7 7 7 7 7 Now i m trying to delete 10th record or any record then it gives me error "Key column information is insufficient or incorrect. Too many rows were affected by update." What should i do if i want only 4 records insted 10 records in my table? How do i delete the 6 records from table? Plz help me. Regards, Shailesh
View Replies !
Deleting 'consecutive' Duplicate Records Alone
Rajarajan writes "Kindly don't ignore this as regular case. This is peculiar. I need to delete one of duplicate records only if they occurs consecutively. eg. 1. 232 2. 232 3. 345 4. 567 5. 232 Here only the first record has to be delete. Kindly help me out. Thank you. Regards, R.Rajarajan"
View Replies !
Deleting Duplicate Records From Lots Of Tables
Hi All, So.. I'm a complete newb to SQL stuff. I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14). -- Add a new column Alter table dbo.tblMyDocsSize add NewPK int NULL go -- populate the new Primary Key declare @intCounter int set @intCounter = 0 update dbo.tblMyDocsSize SET @intCounter = NewPK = @intCounter + 1 -- ID the records to delete and get one primary key value also -- We'll delete all but this primary key select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK) into #dupes from dbo.tblMyDocsSize group by strComputer, strATUUser having count(*) > 1 order by count(*) desc, strComputer, strATUUser -- delete dupes except one Primary key for each dup record deletedbo.tblMyDocsSize fromdbo.tblMyDocsSize a join #dupes d ond.strComputer = a.strComputer andd.strATUUser = a.strATUUser wherea.NewPK not in (select PKtoKeep from #dupes) -- remove the NewPK column ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK go drop table #dupes Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily. Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE. So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day? Thanks for your help....
View Replies !
For Deleting Duplicate Row???????
Hi, I want to delete duplicate row from a very big table. Actually this table is used by a SP, and it's a very important. Due to duplicate record entry it's falling. I use bellow method for discarding the dulicate record. PLz tel me it's the most efficent way to this job or u have some other way 1. I drop the primary key 2. Then I let all the duplicate record came into the table 3. then I removed them by using Group by clause and setting rowcount(1 - group by count). 4. Put primary key back and update the statistics. Code is If Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions')) DROP INDEX AdjustmentTransactions.PrimaryKey Insert into AdjustmentTransactions (UrnABS, UrnBar, .................Description) Select a.UrnAbs, a.UrnBar, a.TxnUrn..................... a.Description from TxnProcess a where a.InsUpFlag = 'I' and a.Processed = 'N' and ASCII(b.TxnType) = 65 Set @row_count=0 Declare dup_cursor cursor for Select UrnBAR,TxnUrnBarPat,count(*) counts from AdjustmentTransactions group by UrnBAR,TxnUrnBarPat having count(*) > 1 Open dup_cursor Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count While (@@Fetch_Status = 0) Begin Select @row_count=@count-1 Set rowcount @row_count Delete from AdjustmentTransactions where UrnBAR=@VUrnBAR and TxnUrnBarPat=@VTxnUrnBarPat Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count End Set rowcount 0 Close dup_cursor Deallocate dup_cursor If not Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions')) CREATE UNIQUE INDEX [PrimaryKey] ON [dbo].[AdjustmentTransactions]([UrnBAR], [TxnUrnBarPat]) ON [PRIMARY] Update Statistics AdjustmentTransactions Thanks Sandipan
View Replies !
Deleting Duplicate Rows
I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten. There is no Primary Key in the csv file that can be used. I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates. When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want. Any thoughts as to why this needs to be run twice? Or is a better approach available? Here is my code - SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*) INTO temppkgactions FROM pkgactions GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1 DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 ) FROM dbo.pkgactions DROP TABLE temppkgactions Thanks
View Replies !
Deleting Some Of Duplicate Rows
This is an imaginary problem while discussing ROWID in ORACLE. Consider a table without primary key, unique key, uniuqe index. A row has inserted into the table many times. I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated) will be deleted. In ORACLE i can achieve this using ROWID as follows: Delete from Table_name where < all column values > and ROWID <> ( Select max(rowid) from Table_name where < all column values > ) How can this be achieved in MS SQL Server 6.5 ? According to Dr. Codd's Golden rules for RDBMS one is that One should be able to reach each data value in the database by using table name, row idenfication value and column name. Does MS SQL Server 6.5 satisfy this requirement ? Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5 Satisfy? Which doesn't ? Any discussion about Codd's Rules is welcome. - Gunvant Patil gunvantp@yahoo.com
View Replies !
Deleting Duplicate Rows
Hai I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error. The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99' If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time. Thanks
View Replies !
Deleting Duplicate Rows In SQL Table
I have an SQL tables [Keys] that has various rows such as: [ID] [Name] [Path] [Customer] 1 Key1 Key1 InHouse 2 Key2 Key2 External 3 Key1 Key1 InHouse 4 Key1 Key1 InHouse 5 Key1 Key1 InHouse Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only: [ID] [Name] [Path] [Customer] 1 Key1 Key1 InHouse 2 Key2 Key2 External I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires). Any help would be greatly appreciated. Thanks,
View Replies !
Urgent - Deleting Duplicate Rows
Hi folks, I need to delete the duplicate rows from a table. How to do that in SQL server 7.0 ? If possible write an example, so that it will be much useful for me.. Thanks for ur help.. rgds, vJ
View Replies !
Problem Deleting Duplicate Data
I have a table that contains more than 10,000 rows of duplicate data. The script below copies the data to a temp table then deletes from the original table. My problem is that after it runs, I now have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the triplicate data and returns 0 rows. I've use three different versions of delete dup row scripts with the same result. There are no triggers or constraints on the table, not even a primary key. What am I missing?------------------------------------------------------------------- /********************************************** Delete Duplicate Data **********************************************/ --Create temp table to hold duplicate data CREATE TABLE #tempduplicatedata ( [student_test_uniq] [bigint] NULL, [test_uniq] [int] NULL, [concept_id] [smallint] NULL, [test_id] [varchar](12) NULL, [questions_correct] [smallint] NULL, [questions_count] [smallint] NULL, [percentage_correct] [decimal](6, 3) NULL, [concept_response_count] [smallint] NULL ) --Identify and save dup data into temp table INSERT INTO #tempduplicatedata SELECT * FROM crt_concept_score GROUP BY student_test_uniq, test_uniq, concept_id, test_id, questions_correct, questions_count, percentage_correct, concept_response_count HAVING COUNT(*) > 1 --Confirm number of dup rows SELECT @@ROWCOUNT AS 'Number of Duplicate Rows' --Delete dup from original table DELETE FROM crt_concept_score FROM crt_concept_score INNER JOIN #tempduplicatedata ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq AND crt_concept_score.concept_id = #tempduplicatedata.concept_id AND crt_concept_score.test_id = #tempduplicatedata.test_id AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct AND crt_concept_score.questions_count = #tempduplicatedata.questions_count AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count --Insert the delete data back INSERT INTO crt_concept_score SELECT * FROM #tempduplicatedata --Check for dup data. SELECT * FROM crt_concept_score GROUP BY student_test_uniq, test_uniq, concept_id, test_id, questions_correct, questions_count, percentage_correct, concept_response_count HAVING COUNT(*) > 1 --Check table -- SELECT * FROM crt_concept_score --Drop temp table DROP TABLE #tempduplicatedata GO
View Replies !
Deleting Duplicate Rows In SQL Table
I have an SQL tables [Keys] that has various rows such as: [ID] [Name] [Path] [Customer] 1 Key1 Key1 InHouse 2 Key2 Key2 External 3 Key1 Key1 InHouse 4 Key1 Key1 InHouse 5 Key1 Key1 InHouse Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only: I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires). Any help would be greatly appreciated. Thanks,
View Replies !
Deleting The Duplicate Record From Table
Hi , i am using sql server 2005. i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them my problem is table structure have idendtity column which is unique. that is m table structure is something like recid citycode hospcode doctorcode otherdesp 1 0001 hp001 d0001 ... 2 0002 hp002 d0002 ... 3 0001 hp001 d0001 ... 4 0002 hp002 d0002 ... please suggest thank you
View Replies !
Deleting Duplicate Rows Within A Single Table
I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this: SELECT * INTO TempUsersNoRepeats FROM TempUsers2 UNION SELECT * FROM TempUsers3 This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables. Thanks, Ron
View Replies !
Scheduling An Upload And Deleting Duplicate Rows
Hi, New to this Database and this forum as I am I would like to ask for a couple of prompts. My SQL2000 tables are ready and I need to schedule Daily upload of .txt files. These contain a rolling 7Days of Stats. Q1: How best to schedule the automiatic uploading of this data to the respective Tables in SQLServer.(Field names are identical), and Q2: How to schedule a Daily Deletion of those rows which are in the tables already (Each day 6 Days must be Deleted and 1 kept) Appreciate your help, Gezza
View Replies !
For Deleting Duplicate Rows Subquery Or Cueser Is Better In Performance????????????
For deleting duplicate rows, i can use cursor and subquery. cursor code Declare dup_cursor cursor for select acctnumber from LinkUrnABSADMBAR group by acctnumber having count(*) > 1 Open dup_cursor Fetch Next from dup_cursor INTO @acctnumber While (@@Fetch_Status = 0) Begin Delete from LinkUrnABSADMBAR where acctnumber = @acctnumber Fetch Next from dup_cursor INTO @acctnumber End Close dup_cursor Deallocate dup_cursor Subquery code delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1) My question is which one is Better in performance???????????? Thanks Sandipan
View Replies !
Deleting Records That Get Too Old
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?
View Replies !
Deleting Records
I have a table with a load of orphaned records (I know... poor design) I'm trying to get rid of them, but I'm having a brain cramp. I need to delete all the records from the table "Floor_Stock" that would be returned by this select statement: SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE FROM PRODUCT_MASTER INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) I was thinking along the lines of: DELETE FROM FLOOR_STOCK INNER JOIN (SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE FROM PRODUCT_MASTER INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT = FLOOR_STOCK.PRODUCT LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT = BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON FLOOR_STOCK. PRODUCT = F. PRODUCT AND FLOOR_STOCK.SITE = F.SITE ... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
View Replies !
Deleting Records
I have a couple SQL tables that have been appended to daily over the last two years. There is now about 50,000,000 records in the table. Does anyone know the fastest way to delete records before a certain date to shorten these tables? Delete queries and everything else I've tried is taking way too long.
View Replies !
Deleting Records
Whenever I try to delete records from a table joined to another table, like so: DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3) I get the error: Major Error 0x80040E14, Minor Error 25501 > DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3) There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = FROM ] This is an SQL CE database, and SQL Server Management Studio. Any ideas?
View Replies !
Deleting Records
Hey all, Here is the scenario. I'm working with two tables: Contact1 Conthist Contact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record. The link between the two tables is a column called accountno. I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno. Any help would be appreciated. Thanks, Tony
View Replies !
Deleting Lots Of Records
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59. So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed. DTS the ones to keep to another table, drop the old and rename the new table? Bulk copy out, truncate and bring back in? DTS to text, truncate and import back? Other ways? Never worked with such a large table and need a little experienced guidance. Thanks for the help
View Replies !
Deleting Records In The Logfile
I have a database that is used to store a lot of data. We load the data on adaily basis, several thousand records per day. The Log file is not needed,so whats the best way to delete the records in it and reduce the sizeThanksDerrick
View Replies !
Deleting Numerous Records
My Web Host does not provide administrative privilages to the SQL server I have access to. I would like to delete tens of thousands of records from two of my tables without writing to the Transaction Log. Is what I'm trying to do is delete these records quickly without utilizing any of the alotted space my web host has set aside for my transaction log (they give me 50 mb and I go way over that when I run a DELETE statement) What is the best way to do this?
View Replies !
Deleting Related Records.
Hi I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it. Thanks
View Replies !
Problem In Deleting Records
help me out on this one. i have 2 text boxes in my page. user enter any number in those two text boxes. i slect that many record randomly from my main table, and put it into two another tables. now the problem is coming in how to delete those records which were randomly selected from main table in main table. for eg main table contains srNo. UswerID 1 abcd 2 trtr 3 tret 4 yghg 5 jjhj user enters in text box1 '2' and in text box2 `1' so total of 3 random records are selected and put it into two another table say table1 sr.no UserID 2 trtr and table2 contains sr.no. userid 3 tret 5 jjhj now i want to delete these records which are sr.no 2,3,5 from the main table. how do i do it as user can enter any number in the text box.so writing multiple delete statements would not be possible. how do i write statements or help me with logic.
View Replies !
Sql Keywords Deleting Records?
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random) For example. CREATE TABLE [Employee] (Id int, Name varchar(50)) CREATE TABLE [Company] (Id int, Name varchar(50)) CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int) CREATE VIEW [dvEmployee] AS SELECT * FROM [Employee] INNER JOIN [EmployeeCompany] ON [Employee].[Id] = [EmployeeCompany].[EmployeeId] CREATE TRIGGER [dvEmployeeUpdate] ON [dbo].[dvEmployee] INSTEAD OF UPDATE AS BEGIN UPDATE EmployeeCompany SET Status = INSERTED.Status FROM EmployeeCompany, INSERTED WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId END Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table? Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables. I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
View Replies !
Deleting Records From 2 Tables At The Same Time
Hello all, I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have Delete From TblTemp date(Day(-7), CurrentStamp). But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table. Thanks in advance
View Replies !
Deleting Records In Associated Foeign Key Table
Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks
View Replies !
Deleting Records Returned By Datareader
I have a function that opens a connection to an SQL database, issues a SELECT command, and reads the records with an OleDbDataReader. As the records are read, any that match certain criteria are deleted with a DELETE command. Simplified example code is shown below: Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand() dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString) dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID" dbCmd.Connection.Open() Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection) While reader.Read() If reader("SomeColumn") = SomeCalculatedValue Then Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand() dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString) dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID") dbCmd2.Connection.Open() dbCmd2.ExecuteNonQuery() dbCmd2.Connection.Close() End If End While reader.Close() This code worked well with an MS Access database, but when I changed to SQL Server, I get a database timeout error when attempting to do the DELETE. I suspect the reason is that the connection the reader has open has the record locked so it cannot be deleted. The SQL connection string I am using is something like this: UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname" The connection string I used for MS Access included the property "Mode=Share Deny None". I wonder if there is some similar way to tell SQL Server to allow editing of records that are open for reading with an OleDbDataReader. Any help would be appreciated.
View Replies !
Deleting Records In Bulk/batch?
I've got a table with a unique column, "id". I've got the id values of about 300,000 records. These records need to be DELETEd from this table. Is there a way to do this in batch? I can't imagine the only way to do it is: DELETE FROM Table WHERE id = 1 OR id = 2 OR id = 3... OR id = 300000
View Replies !
Deleting Records Based On The Date Difference Using SP
I am having a table where i have the following columns where the date format is dd/mm/yyyy Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008 My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure? Please provide me with full stored procedure Thanx in advance
View Replies !
Deleting Records To Save DISK Space...
Hi, In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately??? Should I run some DBCC command to get some disk space ? if SO ..!! What should I do after deleting the records..???? Thanks Cheriyan.
View Replies !
Sql Server Merge Replication Deleting Records
Hello, I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged. Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me. Corey
View Replies !
Retaining Records Of Top N Rows And Deleting The Rest
Hi All, I am writing a SP where I need to pass an value to maintain records of last n days. In this SP I am deleting a couple of tables based on the value passed to this SP. For e.g. If the SP is passed the value 10, then only TOP 10 records is maintained, the rest are deleted. I have formed the following logic, which I feel can be improved vastly. I create a temp table and CREATE TABLE #TempAuditTbl (Rownum int PRIMARY KEY, Orderid uniqueidentifier) INSERT INTO #TempAuditTbl SELECT ROW_NUMBER() OVER (ORDER BY orderdate desc) AS rownum, Orderid FROM Orders DELETE Orders FROM Orders INNER JOIN #TempAuditTbl adt ON adt.Orderid = Orders.Orderid AND rownum > @TopnRows DROP TABLE #TempAuditTbl OR DELETE FROM Orders WHERE orderid NOT IN ( SELECT TOP @TopnRows OrderID FROM Orders ORDER BY OrderDate desc) This way I am able to keep the top n records. Which of these two solutions is more efficient? Is there a more efficient way to achieve the same. Please help. Thanks & regards Sunil
View Replies !
Deleting Records In An Excel Sheet Using SSIS
I'm using SSIS to export data from a SQL table to an Excel SpreadSheet. The first row of the Excel sheet contains the headers. I would like to delete all the records in the sheet before exporting the data. I'm using an Execute SQL task and the ConnectionType is set to Excel, the connection is set to the correct Excel Connection Manager, the ResultSet is set ton none and the query is DELETE FROM [ExcelDeneme$] GO. When I run the package I receive the following error :[Execute SQL Task] Error: Executing the query "DELETE FROM [ExcelDeneme$] " failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. What can i do to solve this problem?
View Replies !
Is It Possible For A Table To Increase Size When Deleting Records?
No transaction log involved, only the table itself. Use sp_spaceused "table_name" to check the space used. It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased. Recovery mode set to be simple, autoshrink turned on. The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%. Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue Thanks!
View Replies !
Lock Problem When Inserting And Deleting Records
Hi all. I have an application that is using a SQL compact edition database to save/process information. I run a test that is creating two threads: 1. one is inserting data in Table1 2. the other one is deleting records from Table1 When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table. After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted. Any thoughts? Thanks.
View Replies !
Deleting Records From Multiple Tables In SQL Server
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem. I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names): Table 1: Classes --Columns: ClassID, ClassName Table 2: Roster--Columns: ClassID, StudentID, Student Name Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName Table 4: Scores--StudentID, AssignmentID, Score What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement. What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3. The stored procedure I created was DELETE FROM Classes WHERE ClassID=@classid I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said: The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated. What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting? I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error. Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old. Further, is there something else I need to do besides assigning primary keys and foreign keys?
View Replies !
SSMSE Hangs Deleting Multiple Records
Hi All I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005. The problem I have is within SQL Server Management studio experess. I have a table called OriginalDrawings that looks like this CREATE TABLE [dbo].[OriginalDrawings]( [DrawingID] [int] IDENTITY(1,1) NOT NULL, [OriginalFileName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, [PartNumber] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, [SheetNumber] [int] NULL, [Revision] [varchar](50) COLLATE Latin1_General_CI_AS NULL, [OriginalDirectory] [varchar](100) COLLATE Latin1_General_CI_AS NULL, [DrawingCategory] [int] NOT NULL, [ProductFamily] [int] NOT NULL, [IsSalvage] [bit] NULL, [FileSize] [int] NULL, [DataQueryFlag] [bit] NOT NULL, [DataQueryCode] [int] NULL, CONSTRAINT [PK_OriginalDrawings] PRIMARY KEY CLUSTERED ( [DrawingID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] I've loaded it with 40,000 records from my test app. If I open it in SSMSE hit Ctrl-A to select all and then press delete SSMSE appears to hang - it freezes for 10 mins+ (after which I restart my PC. I can delete 1000 records at a time OK, I can delete all quite quickly from my test app by walking through the recordset and deleting each record. Does anyone know why it appears to hang when I try the delete all? Thanks Alec SQL Server 2005 Express 9.00.3042.00 Microsoft SQL Server Management Studio Express 9.00.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
View Replies !
DELETING RECORDS FROM TABLE WITH NESTED WHERE CLAU
Hi, I have a query that is executing properly but i want to delete the results of the query. I am trying to do it but i am messing up somewhere in the syntax Can anybody help me out with this problem? Below is the query DELETE FROM DPT_NEW_BINS WHERE( Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS o WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_temp_NEW_BINS i WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)) CAN SOMEBODY POINT OUT MY MISTAKE?
View Replies !
Deleting All Records From Table W/stored Proc
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected. Current code(works) Public Function DelAll() MZKDB = MZKHRFin If sloption = "L" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;" ElseIf sloption = "S" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";" End If sqlConn.Open() sqlTrans = sqlConn.BeginTransaction() sqlCmd.Connection = sqlConn sqlCmd.Transaction = sqlTrans Try sqlCmd.CommandText = sqlStr sqlCmd.ExecuteNonQuery() sqlTrans.Commit() frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf SetCursor() Catch e As Exception Try sqlTrans.Rollback() Catch ex As SqlException If Not sqlTrans.Connection Is Nothing Then frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf SetCursor() End If End Try frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf SetCursor() Finally sqlConn.Close() End Try ResetID() End Function If cbGenFY.Checked Then sqlStr = "DELETE FROM FIN_FiscalYear" TableName = "dbo.FIN_FiscalYear" DelAll() ClearCounts() timeStepStart = Date.Now GenFY() timeStepStop = Date.Now DispOneCounts() End If If cbGenFund.Checked Then sqlStr = "DELETE FROM FIN_Fund" TableName = "dbo.FIN_Fund" DelAll() ClearCounts() timeStepStart = Date.Now GenFund() timeStepStop = Date.Now DispOneCounts() End If If cbGenFunc.Checked Then sqlStr = "DELETE FROM FIN_Function" TableName = "dbo.FIN_Function" DelAll() ClearCounts() timeStepStart = Date.Now GenFunc() timeStepStop = Date.Now DispOneCounts() End If If cbGenObject.Checked Then sqlStr = "DELETE FROM FIN_Object" TableName = "dbo.FIN_Object" DelAll() ClearCounts() timeStepStart = Date.Now GenObject() timeStepStop = Date.Now DispOneCounts() End If If cbGenCenter.Checked Then sqlStr = "DELETE FROM FIN_Center" TableName = "dbo.FIN_Center" DelAll() ClearCounts() timeStepStart = Date.Now GenCenter() timeStepStop = Date.Now DispOneCounts() End If If cbGenProject.Checked Then sqlStr = "DELETE FROM FIN_CodeBook" TableName = "dbo.FIN_CodeBook" DelAll() sqlStr = "DELETE FROM FIN_BudgetAccnt" TableName = "dbo.FIN_BudgetAccnt" DelAll() sqlStr = "DELETE FROM FIN_Budget" TableName = "dbo.FIN_Budget" DelAll() sqlStr = "DELETE FROM FIN_Project" TableName = "dbo.FIN_Project" DelAll() ClearCounts() timeStepStart = Date.Now GenProject() timeStepStop = Date.Now TableName = "dbo.FIN_Project" DispOneCounts() End If If cbGenProgram.Checked Then sqlStr = "DELETE FROM FIN_Program" TableName = "dbo.FIN_Program" DelAll() ClearCounts() timeStepStart = Date.Now GenProgram() timeStepStop = Date.Now DispOneCounts() End If If cbGenGL.Checked Then sqlStr = "DELETE FROM FIN_gl" TableName = "FIN_gl" DelAll() ClearCounts() timeStepStart = Date.Now GenGL() timeStepStop = Date.Now DispOneCounts() End If If cbGenRevenue.Checked Then sqlStr = "DELETE FROM FIN_Revenue" TableName = "FIN_Revenue" DelAll() ClearCounts() timeStepStart = Date.Now GenRevenue() timeStepStop = Date.Now DispOneCounts() End If If cbGenBank.Checked Then sqlStr = "DELETE FROM FIN_VendorBankAccnt" TableName = "dbo.FIN_VendorBankAccnt" DelAll() sqlStr = "DELETE FROM FIN_VendorBank" TableName = "dbo.FIN_VendorBank" DelAll() sqlStr = "DELETE FROM FIN_bankAdd" TableName = "dbo.FIN_bankAdd" DelAll() sqlStr = "DELETE FROM FIN_bankTERMS" TableName = "dbo.FIN_bankTerms" DelAll() sqlStr = "DELETE FROM FIN_bank" TableName = "dbo.FIN_bank" DelAll() ClearCounts() timeStepStart = Date.Now GenBank() timeStepStop = Date.Now TableName2 = "dbo.FIN_bankTERMS" TableName3 = "dbo.FIN_BankAdd" TableName4 = "dbo.FIN_VendorBank" TableName5 = "dbo.FIN_VendorBankAccnt" DispTwoCounts() End If If cbFinAP.Checked Then sqlStr = "DELETE FROM FIN_Period" TableName = "FIN_Period" DelAll() ClearCounts() timeStepStart = Date.Now GenPeriod() timeStepStop = Date.Now DispOneCounts() End If If cbFinVM.Checked Then sqlStr = "DELETE FROM FIN_vendorClass" TableName = "FIN_vendorClass" DelAll() sqlStr = "DELETE FROM FIN_vendorAdd" TableName = "FIN_vendorAdd" DelAll() sqlStr = "DELETE FROM FIN_vendor" TableName = "FIN_vendor" DelAll() sqlStr = "DELETE FROM FIN_AddressType" TableName = "FIN_AddressType" DelAll() sqlStr = "DELETE FROM FIN_VendorStatus" TableName = "FIN_VendorStatus" DelAll() sqlStr = "DELETE FROM States" TableName = "States" DelAll() sqlStr = "DELETE FROM Country" TableName = "Country" sqlStr = "DELETE FROM FIN_IndustrialCodes" TableName = "FIN_IndustrialCodes" DelAll() ClearCounts() timeStepStart = Date.Now GenIndCodes() timeStepStop = Date.Now DispOneCounts() DelAll() ClearCounts() timeStepStart = Date.Now FinVendStat() timeStepStop = Date.Now TableName = "FIN_VendorStatus" DispOneCounts() ClearCounts() timeStepStart = Date.Now FinAddrType() timeStepStop = Date.Now TableName = "FIN_AddressType" DispOneCounts() ClearCounts() timeStepStart = Date.Now GenCountry() timeStepStop = Date.Now TableName = "Country" DispOneCounts() ClearCounts() timeStepStart = Date.Now GenState() timeStepStop = Date.Now TableName = "States" DispOneCounts() ClearCounts() timeStepStart = Date.Now FinVM() timeStepStop = Date.Now TableName = "FIN_Vendor" TableName2 = "FIN_VendorAdd" DispTwoCounts() End If If cbFinbudget.Checked Then sqlStr = "DELETE FROM FIN_BudgetAccnt" TableName = "FIN_BudgetAccnt" DelAll() sqlStr = "DELETE FROM FIN_Budget" TableName = "FIN_Budget" DelAll() sqlStr = "DELETE FROM FIN_CodeBook" TableName = "FIN_CodeBook" DelAll() ClearCounts() TableName = "FIN_Budget" timeStepStart = Date.Now FinBudget() timeStepStop = Date.Now DispOneCounts() ClearCounts() TableName = "FIN_Codebook" TableName2 = "FIN_budgetAccnt" timeStepStart = Date.Now FinCodeBook() timeStepStop = Date.Now DispTwoCounts() ClearCounts() End If
View Replies !
|
TRACKER
