Check multiple records before inserting records

i have a table which is having size and pieces information

Size, pieces, sizeID, sizecombID

S, 1, 1, 1

M, 3, 2, 1

L, 3, 3, 1

XL, 1, 4, 1

I have two questions :-

1. If user enters the same information i.e. same sizes and same pieces then my stored procedure should not add these four records, instead it should return existing value of SizecombID(which stands for size combination ID)

2. If a totally new information is entered then that should be added in the table and sizecombID should be updated with highest value + 1. So for example if there is only one record in table as shown above then once a new combination is entered, my stored procedure should return 2..

can anybody help on this...

One possibility:

Code Snippet

SELECT SizeCombID

FROM MyTable

WHERE ( Col1 = NewValue1

AND Col2 = NewValue2

AND Col3 = NewValue3

)

IF ( @.@.ROWCOUNT = 0 )

INSERT INTO MyTable

( Col1,

Col2,

Col3

)

VALUES

( NewValue1,

NewValue2,

NewValue3

)

|||

Actually the code snippet is only checking 1 record which is not the case.

1. I will be sending all the entered records to my stored procedure, what is the parameter to be used for sending multiple records

2. All records entered by user are to be checked. In exmaple which was provided, user has entered four sizes so it will be four records. All four records are to be checked.

3. Can you also tell how i can increment the sizecombid automatically to generate next value. Before Insert statement, stored procedure should get the last sizecombID value, add 1 in it and then add that value also to SizecombID.

Pls advs..

|||

1.Passing Multiple Records:

Use XML string, you can get back those records in same structure you want.
There is no limitation since you can use either text or ntext as parameter datatype.

(Ex)

Code Snippet

Declare @.Param as Varchar(8000);
Select @.param = '<Root>
<Row Size="S" pieces="1" sizeID="1" sizecombID="1"/>
<Row Size="M" pieces="3" sizeID="2" sizecombID="1"/>
<Row Size="L" pieces="3" sizeID="3" sizecombID="1"/>
<Row Size="XL" pieces="1" sizeID="4" sizecombID="1"/>
</Root>'

Declare @.iDoc as Int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.param;

SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

EXEC sp_xml_removedocument @.iDoc;

2. You can store the xml result in Table variable and you can validate those with your main tables.

(ex)

Code Snippet

Declare @.Input table
(
Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int
);
insert Into @.Input
SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

3. This can be achived Max(id) + 1 but you have to take care the concurrent users issue.

|||

appreicate your reply on this, i have few more questions :-

1. Is xml the only way to acheive this, can't I pass a set of rows directly to stored procedure without using xml.

2. once all the records are available inside stored proc, do i have to loop through all records using cursor or temp table or i can get the results directly through a sql statement

3. I also thought of Max(id) but bcos of concurrency issues, i was hesitant. Can you suggest any other way ? i just want the value unique not necessarily it should be in sequence (i don't want to use guid)

4. Do you think that i should change my table structure to make it as a xml field for sizes instead of multiple rows, just wanted to know some suggestion on that..

5. how do u get these code snippet blocks in your post, these are very nice.

|||

1. No SQL Server wont support parameter as array value - it should be csv or xml

2. You can get the results directly from the SQL, most of the cursor used batches we can rewrite using SQL queries.

3. Then you can have a lookup table to fetch the unique id (SizeCombo Master table with Identity value) insert the new sizecombo on master table then use the @.@.IDENTITY / SCOPE_IDENTITY on your other detailed table.

4. Don't store your data as XML it will be very difficult to maintaine & manupulations , store the data as rows

5. Its there in the forum text editor itself (next to the Smily Icon with green color {})

|||

Hopefully last question..

Regarding answer to point 2, can u pls provide me the sql query which can search and return sizecombID from a table...

Check for Primary Key before Inserting New Record

Hi,

Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?

As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.

I guess I could use try, catch within the .APSX page or would a stored procedure be better?

Thanks

Brett

one way you could do this is write a stored proc where you can check :

CREATE PROC ...

@.intResult INT OUTPUT

SET @.intResult = 0

IF NOT EXISTS (SELECT <col> FROM <table> WHERE <condition>

BEGIN

-- do the insert here

-- SET @.intResult to 1

END

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

|||

Thanks for the information.

Please can you let me know how can I check in my ASP.NET page the value of intResult?

Regards,

Brett

|||

ndinakar wrote:

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

Return Codes are not needed in languages supporting exceptions. Instead, throw an exception from your SP within SQL Server ...

IF EXISTS(SELECT * FROM <tb> WHERE <pk> = @.pk) BEGIN
RAISERROR('A Document with a number of %s already exists.', 16, 2, @.pk)
RETURN
END

In the ASP code, use a TRY/CATCH around the Execute method. If the error returned is a user defined error (50000), wrap the message in your own exception and send it directly back to the client.

|||

Thanks again for your help, could you please post me an example of how the code for the Try/Catch would look in ASP.NET using VB.
Regards,

Brett

|||check out the recent articles in my blog..I have some sample code that uses Try/Catch block's.
|||

I have read your article but I still don't understand how I can check the RAISERROR from the stored procedure. I then want to display an error to the user saying for example "Duplicate Name Found" if the RAISERROR occurs but if the record is added I would like a message saying "Record Added".

Are there any book you can recommend that deal with ASP.NET & SQL Stored Procedures.

|||I dont have sample code but am sure you;d find it if you google.

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
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:

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:
>

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
> >
> >
> >