Duplicate Removal And Intelligent T-SQL
Jul 20, 2005
I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db. Finding and removing the
existing dups is not rocket science, but I am looking for a complete
solution.
We also want to be able to come up with a T-SQL script to be
intelligent enough to know if:
"Bill Jones" is the same as "Billy Jones" or
"Bill Jones" is the same as "William Jones" or
"Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
St."
Purchasing scripts from a company is not out of the question. Its a
matter of script cost versus my hourly rate to create them... Needless
to say, something cheap.
We have only heard of one app that is dynamic enough to handle this,
and the cost was way, way out of our range, 6 figures is almost 2
years salary.
Any ideas would be greatly appreciated.
View 4 Replies
ADVERTISEMENT
Nov 15, 2015
I have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
View 8 Replies
View Related
Jun 20, 2008
SELECT * from table where city like '%woshington%'
Now this would return nothing, as there is no city like woshington. but google find results and ask as r u looking washington. so would u please tell how I may write an intelligent SQL like query
View 5 Replies
View Related
Dec 3, 2007
Hi, friends.
I make a big mistake like this:
First, I installed the sql server 2005, Second, I installed the VS.net 2005 (vb.net). After that, I uninstalled the VS.NET 2005. Then I found after I uninstalled the VS.NET, the Business Intelligent Management Studio is gone too. I install the VS.NET 2005 again.
Now I could use the Business Intelligent Management Studio to open a SSIS project and a package, but there is nothing in the toolbox in it. Also the IDE could not recognize any object inside the ETL package, like the Task, the Compoment, etc. So I think that the Business Intelligent Management Studio is still not be really installed. The IDE is the VB.NET, and something needed to deal with the ETL package is missing.
How to fix the Business Intelligent Management Studio? I mean, how to make my vb.net to deal with an ETL package agian?
Thanks!
View 3 Replies
View Related
May 22, 2007
Hi all, I run several contact websites. One of them is www.ramc.org.uk - they used to fill in a form and results emailed to me. I then manually copied and pasted into page on server. Very tiresome.
I was hoping with sql express i could create a page where they enter there details into the database after verifying details via a link..... Then as the new details are added they would be displayed on the contacts page in alphabetical order.
Is this really possible or even feasable.
Cheers, your all stars. Mark
View 5 Replies
View Related
May 26, 2008
hi all
i try to get data from sql as
select * from datetable where @d between firstdate and lastdate
i add parameter @d and the type is datetime
when i excute the quiry
An error occurred while reading data from the query result set.Arithmetic overflow error converting expression to data type smalldatetime.
how can i fix that plz
View 4 Replies
View Related
Mar 31, 2007
Hi all,
I desperately need to setup the AD-HOC reporting on SQL server 2005.
I think one of the templates that I need is in Business Intelligent Project Templets Reporting Model Template in Visual Studio Templates.
But I do not see it in there.
Can some one please let me know where can I get the Reporting Model Template, or a link to where I can download or how to create it.
Thanks or any help.
Abrahim
View 3 Replies
View Related
Aug 10, 2007
Hello all,
I've been struggling with an interesting problem. I currently have a solution but it is very slow.
I will be cycling data through a table. Each cycle has 1 million records with 60 fields. One procedure I need to perform on this data is a character cleanse. I have a list of 12 characters that need to be removed.
Right now I have a stored procedure that pulls the characters from a table one at a time. It feeds it to a nested loop that replaces the character with nothing ('') on records that contain the character (something like "update tbl1 set FIELD = replace(FIELD, '&', '') where Field like '%&%'"). This works... but seems rather inefficient. It can take 10 minutes to do a 250,000 record table.
I have tried borrowing regular expressions from VBscript using com objects, it worked and seemed more efficient at first but then I threw a large file at it and it took a half hour to complete.
Im running SQL 2005 on a dual Xeon 3.4 box with 2 gb of ram.
Any advice would be greatly appreciated!!
~~~Thanks~~
View 5 Replies
View Related
Jul 11, 2007
We can use rs.exe to publish a report automatically, but how do we do the reverse - remove a report automatically?
Is there a way by using a MS utility?
Is there a way by writing code, using library objects?
Part of the problem we face is having servers with RS all set up with a particular connection string and published reports. We then want to change everything and we want to do it automatically.
View 7 Replies
View Related
Jun 27, 2007
I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.
View 5 Replies
View Related
Jan 8, 2004
Hi,
Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:MSSQLREPLDATAunc directories which seem to represent another log of some description.
Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.
Does anyone know what it is used for?
thanks
Pete Storey
View 5 Replies
View Related
Apr 29, 2015
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
View 9 Replies
View Related
Feb 15, 2006
Hi,
is there a way to ensure a merge subscription is synchronised before it is removed from the publisher using SMO? - (it is a push subscription)
I thought I had it as there is a MergeSynchronizationAgent object on the SubscriptionDatabase object, but calling Synchronize() on this throws an exception saying it can't contact the publisher.
Any help would be really appreciated, thanks.
Graham
View 4 Replies
View Related
Feb 20, 2007
I created a setup package and installed it on my SQL2005 server. I cant seem to find anywhere on the server where they are installed, but I can access them from the Job schedule tool, which work for me.
However, now I have to upgrade the deployed SSIS and I cant seem to install on top of an existing one (gave me no errors but after testing it i realized it is still running the old version.)
My questions are:
1) How can I find and delete these SSIS packages from the server?
2) How can I just upgrade for a new version of a SSIS package?
I need to be able to do both things as at some point I may want to just remove some packages.
View 11 Replies
View Related
Apr 20, 2007
Hello,
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Any suggestions gratefully recieved.
View 5 Replies
View Related
Mar 29, 2008
Hi everyone,
I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation).
Is there a possibility to empty a data table automatically every day (at a certain moment)?
Kind regards,Maxime
View 2 Replies
View Related
Oct 4, 2005
I need help using the TSQL Command.... I want to display car reviews by authors.... a car may be reviewed by multiple authors.... but the latest entry should be displayed... the older enteries will not be displayed....the following is the query </P><FONT color=#0000ff size=2>
<P><FONT color=#000000>SELECT CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, CarReviews.Id AS IdFROM CarReviews INNER JOIN CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN CarModel ON CarReviews.ModelId = CarModel.IdGROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, CarReviews.Id, CarReviews.Name, CarReviews.Company, CarReviews.DateORDER BY CarReviews.Date DESC</FONT></FONT></P><FONT color=#0000ff size=2>
<P></P></FONT>
<P>Results of above statement....
Date Company Reviewer MakeName ModelName YearName Image Id
----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- ----------------------------------------------------------- -----------
10/3/2005 5:12:25 PM LATimes.com Dan Neil MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 8
10/3/2005 5:11:41 PM Star-Telegram.com G. Chambers Williams III MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 7
10/3/2005 4:51:31 PM MotoFlare.com Joe Wiesenfelder MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 6
10/3/2005 4:49:22 PM MotoFlare.com Jim Flammang MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 5
10/1/2005 4:19:25 PM MotoFlare.com Erphan Rajput Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 3
10/1/2005 4:19:25 PM MotoFlare.com Jim Flammang Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 4 Problem is repeation... In the above statement .... the statement should return only 2 records... but it is displaying 6 records....record one should be of MINI Cooper S 2005 & second should be of Audi A4 (2005.5) 2005Urgent reply will be highy appreciated...Thanks,Erphan Rajput..
View 1 Replies
View Related
May 21, 2015
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
View 2 Replies
View Related
Jun 13, 2008
We have a N+1 SQL 2005 x65 SP2 + 3159 cluster which was running fine until the service account which runs Windows cluster and the SQL services was removed as a login from SQL. This was a new setup so the only SQL accounts are (SA) as SA, Windows Domain GROUP's which one group is configured as SA and the cluster account is added to this group. I cannot start SQL service on the cluster now. I've tried logging into one node as the service account which is member of a AD group which has SA rights to SQL as well as my personal account. If anyone knows a way ot fix this without reinstalling I'd appreciate your help.
View 8 Replies
View Related
Mar 21, 2006
Hello...
I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:
"ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","",""
My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:
"550""""N""FAIR OAKS"""
I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!
Thank you...
View 1 Replies
View Related
Sep 11, 2015
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies
View Related
Aug 14, 2015
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
View 6 Replies
View Related
Oct 2, 2007
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
View 4 Replies
View Related
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Sep 2, 2015
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
View 6 Replies
View Related
Aug 17, 2007
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to?
Thanks!
View 3 Replies
View Related
Mar 9, 2001
Hi,
The DTS or jobs failed due to the ignore duplicate key message. How do
I turn this dup key message off or ignore it so that the jobs will succeed?
thanks,
Rachel
View 1 Replies
View Related
Apr 22, 1999
How can I compare numerous rows in the same table that have the same fname, lname and access_id but different identity values (and other columns) and then keep only the one row which is the most current based on a date col?
This seems so easy, but today I just can't come up with it. Do I have to use a cursor of some kind?
Troy
View 1 Replies
View Related
Jan 12, 2007
Hi guys
How can i get id which are duplicate in a table.
Thanks
View 1 Replies
View Related
Jun 21, 2006
Hi guys,
i have the following tables:
Table1:
[PKID] WITH INDEX "IGNORE DUPLICATE KEY"
Table2:
[FKID]
and i have the following SQL statement:
insert into Table1
select distinct FKID
from Table2
where FKID not in (select PKID from Table1)
The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..
Please help !!!
TNT :s
View 4 Replies
View Related
Feb 10, 2007
Hi!
Very grateful for some advice... duplicate key.. but how do I solve it.
By Lookup ?
[OLE DB Destination [1647]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".
OLE db SQL: SELECT DISTINCT convert (char(8),date,112) as day, cast(datepart(year, date) as varchar(4)) + cast(datepart(week, date) as
varchar(2)) as weeknr, cast(datepart(year, date) as varchar(4)) + Substring(convert(varchar(10), date, 121), 6, 2) as month, YEAR(date) AS year FROM Purchase
View 5 Replies
View Related
Oct 25, 2007
I'm currently working on a Game DB, To where i can set Primary Key's and such, otherwise the Emu reads it wrong. The current table i am working on, has duplicate row's of every row. (3600 rows instead of 1800) and the duplicates have the same ID as the original row. How would i go about Deleting the Duplicate rows without harming the original? (Sql 2005)
Regards
Vision
View 1 Replies
View Related
Mar 22, 2006
I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.
I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.
When I try to execute this in a DTS package, it stops the package execution because an error message was returned.
I have tried setting the fail on errors to OFF, but this has no effect.
I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.
I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.
I have verified the service pack, re-booted, etc.....
I am trying this in MSDE 2000a.
Thoughts or comments? Thanks!
View 4 Replies
View Related