hi all,
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another process
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
So
Hi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So
|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>
|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>
|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============
CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============
The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
[vbcol=seagreen]
> Thx for your reply.
> But i want to check existing record with inserting record by columns of that
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts
Monday, March 19, 2012
Check duplicates when inserting
hi all,
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another proces
s
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============
CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============
The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
[vbcol=seagreen]
> Thx for your reply.
> But i want to check existing record with inserting record by columns of th
at
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
>
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another proces
s
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> process
> ID
> to
> address
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============
CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============
The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
[vbcol=seagreen]
> Thx for your reply.
> But i want to check existing record with inserting record by columns of th
at
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
>
Check duplicates when inserting
hi all,
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another process
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
> Thx for your reply.
> But i want to check existing record with inserting record by columns of that
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
> > Hi
> > Itzik Ben-Gan has written a greate example
> >
> > CREATE TABLE #Demo (
> > idNo int identity(1,1),
> > colA int,
> > colB int
> > )
> >
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (2,4)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (4,2)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (5,1)
> > INSERT INTO #Demo(colA,colB) VALUES (8,1)
> >
> > PRINT 'Table'
> > SELECT * FROM #Demo
> >
> > PRINT 'Duplicates in Table'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo <> B.idNo
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Duplicates to Delete'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > DELETE FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Cleaned-up Table'
> > SELECT * FROM #Demo
> >
> > DROP TABLE #Demo
> >
> > "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> > news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > > hi all,
> > >
> > > I am confuse now,Let me explain the process.
> > >
> > > In database table called "Individual " it's store all customer information
> > > user can insert in this table by .NET application but we have another
> > process
> > > that can insert individual from file.
> > >
> > > Sometimes same individual repeat again so before i insert into Individual
> > > table i have to check that it's exist or not but there is not any unique
> > ID
> > > for each customer so basically one customer in individual table have
> > > Indentity generated IndividualID so when i try to insert there is no way
> > to
> > > find out that is this same customer or diffrent.
> > >
> > > Indivivual table have another column like
> > > FirstName
> > > LastName
> > > BirthDay
> > > BirthMonth
> > > BirthYear
> > > Gender
> > > Maritial Status
> > > Street
> > > StreetName
> > > AddressLine1
> > > City
> > > PostalCode
> > >
> > > So how can i find that this is duplicate or not.
> > >
> > > I can actually match this column value with inserting individual value but
> > > you know coustomer can registered with little diffrent with names or
> > address
> > > like ("daniel" can register with second time with "dan")
> > >
> > > So is there is any trick to compare this duplicates.
> > >
> > > Thanks in advance
> > >
> > >
> > > So
> >
> >
> >
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another process
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
> Thx for your reply.
> But i want to check existing record with inserting record by columns of that
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
> > Hi
> > Itzik Ben-Gan has written a greate example
> >
> > CREATE TABLE #Demo (
> > idNo int identity(1,1),
> > colA int,
> > colB int
> > )
> >
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (2,4)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (4,2)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (5,1)
> > INSERT INTO #Demo(colA,colB) VALUES (8,1)
> >
> > PRINT 'Table'
> > SELECT * FROM #Demo
> >
> > PRINT 'Duplicates in Table'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo <> B.idNo
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Duplicates to Delete'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > DELETE FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Cleaned-up Table'
> > SELECT * FROM #Demo
> >
> > DROP TABLE #Demo
> >
> > "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> > news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > > hi all,
> > >
> > > I am confuse now,Let me explain the process.
> > >
> > > In database table called "Individual " it's store all customer information
> > > user can insert in this table by .NET application but we have another
> > process
> > > that can insert individual from file.
> > >
> > > Sometimes same individual repeat again so before i insert into Individual
> > > table i have to check that it's exist or not but there is not any unique
> > ID
> > > for each customer so basically one customer in individual table have
> > > Indentity generated IndividualID so when i try to insert there is no way
> > to
> > > find out that is this same customer or diffrent.
> > >
> > > Indivivual table have another column like
> > > FirstName
> > > LastName
> > > BirthDay
> > > BirthMonth
> > > BirthYear
> > > Gender
> > > Maritial Status
> > > Street
> > > StreetName
> > > AddressLine1
> > > City
> > > PostalCode
> > >
> > > So how can i find that this is duplicate or not.
> > >
> > > I can actually match this column value with inserting individual value but
> > > you know coustomer can registered with little diffrent with names or
> > address
> > > like ("daniel" can register with second time with "dan")
> > >
> > > So is there is any trick to compare this duplicates.
> > >
> > > Thanks in advance
> > >
> > >
> > > So
> >
> >
> >
Sunday, February 12, 2012
char type in MS SQL
Dear All,
I am new to MS SQL. One VB application that I need to maintain using it.
There is one problem reported but I couldn't explain: one field is declared
as Char (20), but the application seems able to insert a string with length
even 30. There is no character truncatd when retrieved neither.
However, I cannot simulate the case in the MSSQL database manager. When I executed a SQL script to insert, I was simply not allowed to do so.
Any explanation to the sDid anyone encounter same thing before?
Thanks for any hint.
RegardsMy first thought is that you may not be looking in the right place for something (either the column size or the data entered). You are right that normally trying to put 30 characters in a 20 character slot will result in an error. Does the VB script have "on error resume next"? Can you disect the VB script enough to see the insert statement itself? Post that insert statement and the result of sp_help tablename, if you can.|||Hi,
Thanks for the help. Below is the piece of codes, while t1_id was declared as CHAR(20) NOT NULL but a string with length 30 could be inserted and retrieved.
----------------------
On Error GoTo errors:
Dim conn As RDO.rdoConnection
Set conn = mrdoConnection.Connection
sqlStr = "INSERT INTO t1 (t1_id,t1_name) values ('" & s1 & "', '" & s2 & "')"
conn.Execute sqlStr
errors:
MsgBox err.Description
If Not conn Is Nothing Then
conn.RollbackTrans
conn.Close
End If
----------------------|||Must be using the winzip datatype.
create table test1 (col1 char(20))
insert into test1 values
('123456789012345678901234567890')
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Even if there was something (connection option of some sort) preventing you from getting an error returned, you would not be able to retrieve the value. When you insert the value, what do you get from this?
select * from t1
Here, I am assuming that t1 is some sort of permanent entity that is not being dropped and recreated for every run.
I am new to MS SQL. One VB application that I need to maintain using it.
There is one problem reported but I couldn't explain: one field is declared
as Char (20), but the application seems able to insert a string with length
even 30. There is no character truncatd when retrieved neither.
However, I cannot simulate the case in the MSSQL database manager. When I executed a SQL script to insert, I was simply not allowed to do so.
Any explanation to the sDid anyone encounter same thing before?
Thanks for any hint.
RegardsMy first thought is that you may not be looking in the right place for something (either the column size or the data entered). You are right that normally trying to put 30 characters in a 20 character slot will result in an error. Does the VB script have "on error resume next"? Can you disect the VB script enough to see the insert statement itself? Post that insert statement and the result of sp_help tablename, if you can.|||Hi,
Thanks for the help. Below is the piece of codes, while t1_id was declared as CHAR(20) NOT NULL but a string with length 30 could be inserted and retrieved.
----------------------
On Error GoTo errors:
Dim conn As RDO.rdoConnection
Set conn = mrdoConnection.Connection
sqlStr = "INSERT INTO t1 (t1_id,t1_name) values ('" & s1 & "', '" & s2 & "')"
conn.Execute sqlStr
errors:
MsgBox err.Description
If Not conn Is Nothing Then
conn.RollbackTrans
conn.Close
End If
----------------------|||Must be using the winzip datatype.
create table test1 (col1 char(20))
insert into test1 values
('123456789012345678901234567890')
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Even if there was something (connection option of some sort) preventing you from getting an error returned, you would not be able to retrieve the value. When you insert the value, what do you get from this?
select * from t1
Here, I am assuming that t1 is some sort of permanent entity that is not being dropped and recreated for every run.
Subscribe to:
Posts (Atom)