Our programs have and would like to continue keep the same data in multiple database. Example phone numbers. Us DBAs are tring to convince them not to.
Other than keeping the data current, what avantages are there to keep data in one location for multiple application to access.
Hello,
I have a question regarding duplicate records, the thing is I'm able to query for duplicated records if I type the following:
select ColumnName from TableName
where ColumnName in
(
select ColumnName from TableName
group by ColumnName having count(*) > 1
)
That gives me duplicate records for one column, but I need find duplicate records in more than one column (4 columns to be exact), but the way I need to find these records is they all have to be duplicate, what I'm trying to say is I don't don't want to find the following:
First Last Age Email
John Smith 25 jsmith@hotmail.com
John Smith 26 jsmith@hotmail.com
John Smith 25 jsmith4@hotmail.com
I need to find the following:
First Last Age Email
John Smith 25 jsmith@hotmail.com
John Smith 25 jsmith@hotmail.com
John Smith 25 jsmith@hotmail.com
So all the columns must be exactly the same, that's the only condition I want to show the records, is there any way to do this?
For the record, I'm using MS SQL Server 2000, thank you.
I am new in SQL programming world, following is query that i had created
select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime
from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid
where netflowsources.LastTime NOT LIKE GETDATE()
which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example
nodeid value = a,a,a,b,b,c,c,c,c,d,d,d
But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.
have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[code]....
I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
Have a table that is used during a conversion. It is hit pretty heavily with inserts by mulitple session ids. For performance reasons I cannot add a unique constraint on the column that is getting duplicates (it is an encrypted cell in varbinary).
I do not want duplicates in this Encrypted Column. So before each insert the insert programs reads the table and verifies that the Encrypted Column value does not already exist. But unfortunately several duplicates are falling through the cracks. What are my options for not allowing duplicates?
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[Code]....
I'm in the process of trying to identify duplicate contacts. I doing this for millions of contacts and have gotten stuck and could use some elegant solutions!
The business rule is this:
Any contact that has the same name, phone and email address are the same contact
Any contact that has the same name, and email address are the same contact
Any contact that has the same name, email address, but different phone are a different contact.
Any contact that has the same name, email address, and a blank phone can be the same contact as one that has the same name, email address, and has an email address
Rank by the DataSource_fk. 1 being the highest
Put another way:
If 3 contacts have the same name, 2 have phone '1112223344' and all three have the email address 'johndoe@gmail.com' they are the same contact and the lowest DataSource_fk should be ranked the highest.
I've used the Row_number over (Partition by) in the past, but am unsure how to deal with the blanks in email and phone.
DROP TABLE [dbo].[TestBusinessContact];
GO
CREATE TABLE [dbo].[TestBusinessContact]
(
[TestBusinessContact_pk] INT IDENTITY(1,1)NOT NULL,
[Business_fk]INT NOT NULL CONSTRAINT DF_TestBusinessContact_Business_fk DEFAULT(0),
[Code] ......
I have to extract data from 5 different oracle databasee with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies View RelatedI have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies View RelatedI have a temp table with the following columns and data
drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.
SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,
[Code] ....
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
View 2 Replies View RelatedHi All,
i have mutiple text file. let us say,a1.txtb1.txtc1.txt
i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table)
now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3
using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process.
so, can any one give ur valuable sugession to solve this issue.
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
View 6 Replies View RelatedI want to make a, I want to make an exact replica of one database in sql server 2005 express edition so I have a database called dbTest and I want to create a database called dbTest2 that is the exact replica of dbTest.I am using sql server 2005 express edition.
View 3 Replies View RelatedIs there a way to copy/duplicate and rename a database on the same server? Ineed to make a copy for another application. I appreciate any tips or adviceyou can provide!TIA - Rob
View 3 Replies View Relatedhi,
I am using SQL Server 2000 and i wanted to know what is the best way to duplicate a database?
Hello guys! Is it possible to duplicate a primary key?
I would like my database to accept data with the same primary key.
Is it possible?
How do you declare ON DUPLICATE KEY UPDATE?
Please help me. Thanks in advance.
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record.
Thanks.
I am new to SQL server 6.5.
I will need to stress test a sql server 6.5 test database by duplicating
data. To do so, I need to know how to modify the primary key(which are
numbers in character data type) to duplicate the data several times over.
The primary key is character data.
What I have done ion the past is use insert statments -let's say 20 -- and then copy them and change the date in a text editor and change the primary key column and the other coluimns with thea replace of different letters and numbers. This is slow and tedious.
Does any one have a script I can run to do so?
Can I do this sql or do I need some sort of stored procedure? Any help
would be appreciated. Thanks.
DAvid Spaisman
would be greatly appreciated. THanks.
David Spaisman
Hello all,
I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database.
I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added.
Can anyone help?
Thanks
I have a table called emails with a field named emailaddress and some emailaddress are entered more than once. I want to be able to list all emailaddress just once. is there an sql statement that I could use to generate this.
View 3 Replies View RelatedHello people, not sure how to do this. I have a unique Claim ID and when joined with the code table has 4 different Code ID and the Claim Amt is also unique to the Claim ID (One Claim Amnt per one Claim ID). I need all of these data for the report my problem is in getting the correct summation of the claim amt since this looks like I have 4 amounts of $1773.31 when in actuality there is only one. The Code ID is also part of the report parameter so I tried assigining the $ amount to just one of the Code ID but that will not work incase that particular Code ID is not selected in the parameter, the claim would read as a zero. Any help would be appreciated. Thanks
Claim ID: Code ID Claim Amt
07089000296 757.39 1773.31
07089000296 V05.3 1773.31
07089000296 V30.01 1773.31
07089000296 V72.19 1773.31
hi all,
How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks the submit button, i want to check through the database if the exact records exist in the database before the data is saved. if the user is registered on the database, he wont be allowed to login. how can i acheive this?
i thought of using the comparevalidator but i'm not sure how to proceed.
thanks
Hi,
I've got a database called Webmine which is running with multi users.
I want to duplicate it to generate archive.
My problem is that I can't duplicate the MDF file (and the ldf file optionally) without detach the webmine db and reattach after the duplication.
Detach this database is a problem durring this processing of duplication because we can have users always working on Webmine.
Is there a way to proceed without detach Webmine ? Or perhaps it's stupid or dangerous to duplicate a DB if users are always working (that's my position but here, everybody does not agree with this position so can anybody certify
I've got or not reason ?
Thanks to your answers
Best regards
Axel
Instead of me testing my scripts on my main commerce database I was wondering if there was an easy way to duplicate my most currnet database as another database...
that way if I screw up I can just delete it and try again
I looked at the restore as in enterpirse manager, but I don't think thats it
I have built a template database which I'm finally pleased with, however I want to periodically duplicate the design - not data into a new database. How can I duplicate a database? I was hoping to right mouse, copy, then right mouse, paste, and then be prompted for the new name but no such luck.
View 6 Replies View Relatedi have duplicate records, but they have unique ids.
the duplicates resulted as a result of using a view to enter the data using BCP to load data from text file that had many dulicate records as part of consolidting the data i would like to get rid of the duplicate
is there some way of checking if the Name, Address, Telephone and Image are the same as on a record with another then remove them.
the table has the following colunms:
ID, Name, Address, Telephone, Image
1, adam, 45 tree gate road, 00000 000000, adam.jpg
2, uno, 44 garstang road, 00001 000001, uno.jpg
3, adam, 45 tree gate road, 00000 000000, adam.jpg
4, brian, belgarth house, 00022 000022, adam.jpg
5, karen, 3 chester close, 00002 000002, adam.jpg
6, uno, 44 garstang road, 00001 000001, uno.jpg
7, adam, 45 tree gate road, 00000 000000, adam.jpg
from the sample data you can see that there are multiple records is there a way of clensing the data using a sql command?
is there any issues such as performance on clensing it etc?
are there any strategies that should be implemented at the data tier so as to prevent duplication of data?
Hi folksI know just enough about MS-SQL to be dangerous, so feel free to warnme off this it's too much for a non-expert.We are using SQL 2000 as the back-end for website. We would like tocreate a duplicate of the database (on the same server). Then we'd usethem like this:Database A: read-write DB accessed by web administratorDatabase B: read-only 'live' DB accessed by the web serverThe smarts: setting up a way to maintain Database A, have it beaccessible internally (maybe different IP, maybe different port) andwhen a change is approved, have that change replicate to Database B tobe visible on the website.I'm wondering if it's something I can do myself with the rightreading, or I'm going to have to hire some SQL know-how to help meout.Dave
View 1 Replies View RelatedI am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.