my row in my data base consists of 12 coulmns, i have a duplicate values in 6 coulmns of it , i use select distinct for all it doesnt work for it it doesnot remove dupliacte , i cant make primary key or any constraint on this 6 coulmns together cause it contains duplicate values even if i choose ignore duplicate it doesnot work
example
id name adress telephone job gender deprtment
1 john dd 123 doctor m 1st deprtment
1 john dd 123 doctor m 2nd deprtment
so there is duplicate on id, name,telephone, job , gender and different in department so how i make a primary key on id , name , adress , telephone , job , gender together
also how to delete duplicate from it although i used distinct function and it doesnit remove duplicate
I am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions; $option_drop.ScriptDrops = $true;
I don't know if the question has been nailed down. Aside from deleting tables, can we delete the *content* of data within the tables. It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ). Reasons for this include:
- Security (a user may not have rights to see *my* data and should go refresh their own) - Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed) - Bad data (pre-production data feed is not good data) - User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server. Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)
Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....
But how does a user initiate this on their own (corruption aside)?
Previous time this question was asked, without a real resolution: [URL] ....
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
I have a package which loads data from a flat file (csv) to 4 tables in a database. Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this? Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this. Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
Hi i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table. regards vinod.t.v
I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure.... IF ((@TablesToDeleteFrom & 16) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_tt') AND (type = 'V')))) BEGIN DELETE FROM dbo.userclassset WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 hdhd END This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed (@TablesToDeleteFrom & 16) <> 0 AND Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database. Any ideas?
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months: -- Purge records in small portions to avoid locking production tables -- for a long time. The process takes longer, but can co-exist with -- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45 Incorrect syntax near 'Failed:'.
Edited by SomeNewKid. Please post code between <code> and </code> tags.
I have added a delete button to my datagrid, and put in what I think is the code to delete a member from the database at their Member ID, however when I have called a members details and the delete button is pressed, nothing happens!!?? Any ideas? Here is the code:
<%@ Page Language="VB" %> <script runat="server">
' Insert page code here ' Function GetMember(ByVal iD As Integer) As System.Data.SqlClient.SqlDataReader Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [oga].* FROM [oga] WHERE ([oga].[ID] = @ID)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD
sqlConnection.Open Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader End Function
Sub dgMember_Delete(sender as Object, e as DataGridCommandEventArgs) dgMember.EditItemIndex = -1
Sub btnView_Click(sender As Object, e As EventArgs) dgMember.DataSource = GetMember(memberID.Text) dgMember.DataBind() End Sub
Sub dgMember_SelectedIndexChanged(sender As Object, e As EventArgs)
End Sub Function DeleteMember(ByVal iD As Integer) As Integer Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "DELETE FROM [oga] WHERE ([oga].[ID] = @ID)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD
Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try
I wrote a script to archive and delete records rom a table back in 2005 and 2009.
I can't seem to get the syntax right. Any sample script to simply archive and delete records?
This is what I have so far.
DECLARE @ArchiveDate Datetime SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date) FROM tblCall ORDER BY Call_Date) --SELECT @ArchiveDate AS ArchiveDate DECLARE @Active bit
I have to delete a ton of data from a SQL table. I have a unique identifier called the version. I would like to use if not in these versions then delete. I tried to using the statement below, but learned the hard way that it created an error this is the message I got....
Msg 9002, Level 17, State 4, Line 3...
The transaction log for database 'MonthEnds' is full due to 'ACTIVE_TRANSACTION'.
I was reading about truncate, I am not sure how I would do this or how I would setup the statement.
Delete Products where versions were not in (('48459CED-871F-4971-B888-5083990332BC','D550C8D3-58C7-4C74-841D-1C1675F19AE3','C77C7817-3F04-4145-98D3-37BB1610DB35', '21FE83FA-476D-4604-80EF-2ED57DEE2C16','F3B50B81-191A-4D71-A406-011127AEFBE1','EFBD48E7-E30F-4047-909E-F14DCAEA4181','BD9CCC41-D696-406B- 'C8BEBFBC-D362-4D0F-A555-B281FC2B3023','EFA64956-C2CF-41FC-8E21-F060597DAFCB','77A8DE56-6F7F-4490-8BED-AA6809B947EF','0F4C1E5F-B689-4DCB-
I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.
On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.
To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.
The data is automaticaly deleting from one perticular table at every night from last week onwords. I have created a delete trigger to find it out. But Nothing was recorded. There is no jobs except maintainance plans. Nothing in event viewer too. The database recovery model is simple. How can i solve this problem Please advise me to solve this problem
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
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 **********************************************/
--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
I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me
Msg 102, Level 15, State 1,.......Line 1
Incorrect syntax near 'a'
The Select statement looks like:
select count(*) from schema.table a where a.customer_id=1234
The Delete looks like:
delete from schema.table a where a.customer_id=1234
What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix
When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.
Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).
Hi All, I have to delete 135 Million records from our production server, keeping only last three months data & the table doesnot have any index.
Can you please suggest the best possible approach to perform this activity.
Approach i am thinking :-
1)I will rename the existing table & create a new table with the same name (By this my application wont be interrupted) 2)then i am planning to create a nonclustered index on the date column sort order desc so that i can get the last three months data. 3)Once the index is created i can transfer the required records to the new table created in step 1.
Please sugeest your valuable feedback or suggestions to perform the task ASAP.
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece. thanks.
I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId) 2 { 3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection()); 4 5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier); 6 p1.Value = (Guid)companyId; 7 p1.Direction = ParameterDirection.Input; 8 9 deleteCommand.Parameters.Add(p1); 10 dataAdapter.DeleteCommand = deleteCommand; 11 12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds. 13 14 try 15 { 16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'")) 17 { 18 row.Delete(); 19 } 20 return dataAdapter.Update(companyDS.Tables["pg_Company"]); 21 } 22 catch 23 { 24 return 0; 25 } 26 finally { } 27 } I thank you in advance for any help here.
Hey everyone, Im using Microsoft SQL Server Management Studio Express to connect to our SQL Server 2005 (think its 2005, its version 9.000 something something). I have a table with one column that saves a lot of text. I have set the column type to text. When i have a row with lots of text in this column i cannot delete it. I get the message "string or binary data would be truncated" when i try to delete it. If i try to edit the row i get the same message. What do i do?
Hi, does anyone know how to delete data from the SQL database by calling the stored procedure in the Visual Basic.NET? Because I did the Delete hyperlink bounded inside a datagrid. I have already displayed the appointment date, time in the datagrid so I do not have to input any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS
BEGIN IF NOT EXISTS (SELECT MemNRIC, AppDate, AppTime FROM DasAppointment WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime)) RETURN -400
ELSE IF EXISTS (SELECT MemNRIC FROM DasAppointment WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10)) RETURN -401 ELSE DELETE FROM DasAppointment WHERE MemNRIC = @MemNRIC
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation?
Let me know if the strategy to be followed is okay.
1. Drop all the triggers 2. Drop all the indexes 3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error ) The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition. 4. Recreate Indexes and Triggers.
Please let me know if there are any other optimal solution.
I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query. Can someone please suggest me a better way? Any help will be appreciated.
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1
We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean
