How To Avoid Duplicate Cells When Trying To Join 3 Tables?

Jun 3, 2008

Hi all,

 Below are my tables:




Rowid

Name


1

John


2

Peter


3

Jack Table




Rowid

Rowid1


1

1


1

2


1

3


2

1


2

2


3

2


3

3 Table1




Rowid1

Country


1

USA


2

UK


3

JAPAN Table2

I tried to get the Country for all the people in the first table.

My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1

My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?




Name

Country


John

USA



 

UK



 

JAPAN


Peter

USA



 

UK


Jack

UK



 

JAPAN

Any advice will much appreciated.

 

View 3 Replies


ADVERTISEMENT

How To Avoid Duplicate Value

Mar 2, 2007

Hello,

I have the following query,

SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )

It's out put is as follow:

Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha

Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,


Group ID GroupName
0 Abc
1 Beta
2 Alpha

Hope I explained what I need to see in result pane.

Thanks,

Junior

View 1 Replies View Related

How To Avoid Duplicate Data

May 7, 2015

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sectionexpenses]
(@sectionname varchar(30),
@ExpensesName varchar(max),

[code]....

View 3 Replies View Related

Avoid Duplicate Primary Key Error

Aug 20, 2006

How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?
Thanks in advance !
stephen
 

View 3 Replies View Related

Searching A Column For A Value To Avoid Inserting A Duplicate Value

Jul 17, 2007

Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
 Cheers!

View 3 Replies View Related

T-SQL (SS2K8) :: Query To Avoid Duplicate Records (across Columns)

Jan 3, 2015

rewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?

Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),

[Code] ....

--> In this table AccountID is uniquee

--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)

-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates

SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1

[code]....

View 9 Replies View Related

Insert From Formview And Checking Database To Avoid A Duplicate Entry

Apr 6, 2007

I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists.  is there a way to do this with the formview insert command.  Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.

View 1 Replies View Related

T-SQL (SS2K8) :: Avoid LEFT Join

Aug 20, 2013

I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records.building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.

scripts (sample):

CREATE TABLE [dbo].[tbl_PersonDetails](
[PersonID] [int] NOT NULL,
[LeaveTimeId] [int] NOT NULL
) ON [PRIMARY]

[code]...

Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :

1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails
2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails.
3. not using LEFT join

View 9 Replies View Related

Transact SQL :: Avoid Duplicates From Join

Jun 8, 2015

I am doing some audit and i  have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...

I am using SP_Who2 and sql server  Audit for auditing all data happening on sql server databases  and dumping them to tables  Audit_DBAudit  abd Audit_sp_who2  and from then i am trying to get data which is not repeating/duplicate ...
 
SELECT
A.ProgramName
,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement
FROM Audit_DBAudit as F 
 Join [Audit_sp_who2] AS a
on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))

View 11 Replies View Related

SQL Server 2012 :: Product Join - How To Avoid

Oct 27, 2015

This query below is giving product join for me, is there a way to avoid this?

SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)

View 5 Replies View Related

Copy Specific Cells Between Tables Based On Login ID

May 26, 2007

I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.
Q1) How can this be done?  (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).
Any suggestions/comments would be appreciated. Thank you in advance.

View 1 Replies View Related

Transact SQL :: Comparing Two Tables By NOT NULL Cells In Different Columns

Oct 22, 2015

We have two tables:

Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5

Table2
Servers|Databases|Users|Names
NULL    |Database1|NULL |Name1
NULL    |NULL        |User1|Name2
NULL    |NULL        |NULL |Name3
Server5|Database5|NULL |NULL

I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:

Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4

I used query like this:

SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c

But for many rows (like 100 000) it takes ages to get results, any better way to work on this? 

View 2 Replies View Related

How To Avoid Duplicates In Two Tables?

Dec 16, 2007

Hi all,
I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion.
Thank U.
 

View 13 Replies View Related

Can I Avoid Temp Tables, Etc.

Sep 14, 2006

I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?

View 6 Replies View Related

How To Avoid Temp Tables And Do It In One Query?

Feb 5, 2008

I have a table T1 with columns: Date, Salesman, Sales. Salesman is a userid for each person and Sales is a decimal capturing sales done on Date.

From the data in T1 I would like to have a table with the following:

Salesman Sales-To-Date Sales-Last-Week

I have been able to do it with a temporary table, temp, as follows:


select Salesman, Sum(Sales) as ToDate into temp from T1 group by Salesman


with T2 as (

select Salesman, Lastweek=sum(Sales) from T1 where week>='1/28/2008' and week<='2/3/2008' group by Salesman

) select temp.Salesman, ToDate, Lastweek from temp full join T2 on temp.Salesman=T2.Salesman



How can I do the above in one query statement?

Thanks

View 4 Replies View Related

How To Avoid Having Locked Tables During Update In Sql Server 2005?

May 30, 2006

We are running a shopping mall in Korea and got a database including a table
of 4 million product prices, which is to be updated hourly basis.  Updating 4million
records requires at least 10 minutes to complete. During the update, our shopping mall
exposed to customers does not respond quickly in fact very very slowly
and we investigated and found out that many tables of SQL database during the update were being
locked.  As you know, site speed is top priority. We studied and found out that there are
two ways to avoid having locked tables during update, those are "read uncommitted" and
"snapshot" using the following lines.

     set transaction Isolation level read uncommitted
     set transaction Isolation level snapshot

We tried numerous times the above two lines and still find our tables being locked during update
and our customers are being disappointed.

My questions:

1. Is it possible at all in view of "the state of the art" to avoid having locked tables  during update of 4 million records ?

2. if it is possible, would you please teach me like I am the beginner of database studies?

For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).

Sincerely
 

Daeyeon Jo

View 6 Replies View Related

Inner Join Duplicate Data

Jun 13, 2000

i have 2 tables not connected in any way
but both have orderid filed (same filed).
In one table this filed (and onther one) are keys,
the second table dose not hace a key at all.
The same order_id CAN repeat itself in each table.
When i try to join the tables (some rows just in one table and some in both):
Select tab1.name, tab1.orderid, tab2.sku
from tab1 inner join tab2 on tab1.orderid=tab2.orderid
The result i get is duplicate.
each row is multiple.
What I'm doing wrong?

View 3 Replies View Related

Duplicate When Do Left Join

Jan 27, 2014

select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a
left join [assembly_Doc] doc
ON a.id = doc.assembly_id
left join [assembly_Recog] re
ON a.id = re.assembly_id
where [activate] =1
and doc.doc_type = 1

I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below:

CusNoDocType DocName
CUS-022 1B1489-01 Rev 1
CUS-022 1B1489-01 Rev 1
CUS-022 1B149-02 Rev 2
CUS-022 1B149-02 Rev 2

View 2 Replies View Related

SQL 2012 :: Duplicate Records On A Join?

Apr 8, 2014

[URL]

I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.

Use Sales_Builder
Go
SELECT DISTINCT
GBPriceList.[Mfr_Part_Num],

[Code]....

View 7 Replies View Related

Join Returns Duplicate Rows

Oct 23, 2007

Hi,
I'm having a little trouble with the following code:

SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr

I want it to return the 2 rows that is present in the tables but it returns 4.

1262007-10-20 10:14:00
1262007-10-20 10:14:00
1262007-10-20 10:17:00
1262007-10-20 10:17:00

View 18 Replies View Related

Fact Table With 3 Keys From Dimension Tables - Avoid Populating NULLs

Jun 10, 2014

I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..

View 3 Replies View Related

Duplicate Result Rows From 2 Table Join

May 7, 2012

I am using SLQ Server 2008 R2. The database was designed by another company.

I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.

Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.

I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.

My SQL is:

select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init,
b.city AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first

Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?

View 5 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

How To Remove Duplicate Rows From Full Join Query

Jan 26, 2008

I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610        c@d.com
1          1          abcxyz              2008-01-20 23:41:52.970        a@b.com
1          2          xyzabc              2008-01-21 00:17:14.360        c@d.com
1          2          xyzabc              2008-01-20 23:43:17.110        a@b.com        
1          2          xyzabc              2008-01-20 23:42:43.937        a@b.com
1          2          xyzabc              NULL                                      NULL
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
Now, I want unique rows from the above result set like :- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610        c@d.com
1          2          xyzabc              2008-01-21 00:17:14.360        c@d.com
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
 

View 8 Replies View Related

Group By After Outer Join - Getting Duplicate Or Null Values

Oct 18, 2013

I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.

My query

select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town

How to stop getting null values?

portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB

I'm looking for distinct values like this:

portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...

View 2 Replies View Related

How To Join 3 Tables Using Left Or Right Join Keyword?

Aug 17, 2007

Hi guys,

I'll appreciate any help with the following problem:

I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.

1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02

2. Master table TBCODE contain 5 records:

rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5

3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR

rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR



I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.

Right Join method:


SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE

RIGHT JOIN TBLOC C ON A.LOC = C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE



When I use Non-ASNI method it work:



SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C

WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

Result:

LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL


Please Help.

Thanks.






View 3 Replies View Related

Duplicate Tables

Jun 24, 2008

We are using SQL 2005 to drive a database application. All of the tables are supposed to be owned by user1, so the full table names should be user1.table1, user1.table2, etc.

We have a situation where we have duplicate tables:

dbo.table1
user1.table1
dbo.table2
user1.table2
dbo.table3
user1.table3

This has always been strange, but hasn't really caused many issues.

Part of the application is querying a table called "TOTALS" and the query isn't retunring any data. dbo.TOTALS is empty (since it shouldn't exist) and user.TOTALS has all of our information. Since nothing is being returned by the select query that is just being run against "TOTALS," I would assume it is actually running against dbo.TOTALS.

How can I force SQL to run

SELECT * (actually complex criteria, but * should work for this purpose)
FROM TOTALS

to actually run against user1.TOTALS without changing the query since that can't be altered?

Additionally, is there an effecient way to delete all of the dbo owned tables (this database has several hundred tables)?

View 4 Replies View Related

Non Duplicate Check Between Two Tables?

Apr 22, 2005

What I am trying to do is this. I have two tables that someone else created and now I have to fix there mess. Both tables have 3 like fields. I want to check and see if the ProjectNumber field from the Artifacts table does not find a match in the Projects table. If no match is found return the record from artifacts table.

Code:


SELECT *
FROM Artifacts INNER JOIN Project ON Artifacts.ProjectNumber = Project.ProjectNumber
WHERE Artifacts.ProjectNumber NOT IN (Project.ProjectNumber);


The next thing I have to do is insert a record of the three fields from the Artifacts table to the Projects table. Any help would be great.

View 3 Replies View Related

In Few Tables We Have Duplicate Data?

Apr 30, 2008

How to fetch that replicated records?


Anyone can share the query??

View 2 Replies View Related

Duplicate Rows But No Key On The Tables

Jul 23, 2005

Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther

View 1 Replies View Related

Dealing With MS SQL Tables That Contain Duplicate Rows

Jan 30, 2008

Hello,
I have a question, i loaded 2 files into SQL and the files have some cells that have the same model number.
how can I merge the cells together that have the same model number and (if possible take the avarage of their cell called price) 
(and combine their other cell called stock)
and make it into one cell.
Any help would be very very apriciated. Thank you. 
i tryed this but it does not work
SELECT Model_number FROM Products
Join Where Model_number='3CM3C1670800B'
I have also Tryed this, IT SHOULD work but I have an error someWhere:
delete from Productsfrom part_number a join
(select part_number, max(part_number) from part_number group by part_number having count(*) > 1) b
on a.part_number = b.part_number and part_number < b.part_number

View 3 Replies View Related

Checking Duplicate Values Two Tables

Feb 5, 2005

Hi All,

Is it possible in SQL to Restrict value in one table checking a value on anather tables.

Scenerio-1.

I have two table let say,
Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.

Scenerio -2.[/B]

I have table called [B]SIC.

This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.

Please Advise.
Vijay

View 1 Replies View Related

Deleting Duplicate Records From Lots Of Tables

Aug 29, 2006

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 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved