Prevent duplicate insertation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Prevent duplicate insertation

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 09:27:43

Hello,

I have a question to insert an unique value.

Table xxxx has four fields : SKU_ID, PKG_PRC_OBJ_ID and others. It has 44 rows.

SKU_ID is the primary key.

Table yyyy has five fields: PKG_PRC_OBJ_ID and others. It has 191 rows.

All the PKG_PRC_OBJ_ID values in table xxxx can be found in table yyyy.

That means column PKG_PRC_OBJ_ID values in table xxxx is a subset of column PKG_PRC_OBJ_ID values in table yyyy.

Now I am going to insert new rows to table xxxx but no duplicate PKG_PRC_OBJ_ID values from table yyyy.

So what is the best way?

Thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-23 : 09:30:59

what do you mean by

Now I am going to insert new rows to table xxxx but no duplicate PKG_PRC_OBJ_ID values from table yyyy.

you want to insert into xxxx and if not present in yyyy you want to insert there too?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 09:45:00

just check before inserting if the PKG_PRC_OBJ_ID values already exist and if not then insert. use IF NOT EXISTS() and then perform insert. b/w how are you planning for insert? is it by means of procedure or are you just trying for bulk insert?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 09:46:04

Well, sorry for my bad English. Let me give a simple example.
Suppose xxxx has three rows.
SKU_ID PKG_PRC_OBJ_ID A B
1 123 2 3
2 124 3 7
3 222 5 4

yyyy has 5 rows.
PKG_PRC_OBJ_ID C D E F
123
124
222
321
245

Now I am going to add a new row to xxxx. The PKG_PRC_OBJ_ID value must not be within {123,124,222,321,245}.
In another words, inserting 321 or 123 is prohibited. Inserting 666 is ok.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 09:47:17

For bulk insert.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 09:50:01

quote:
Originally posted by zhshqzyc

Well, sorry for my bad English. Let me give a simple example.
Suppose xxxx has three rows.
SKU_ID PKG_PRC_OBJ_ID A B
1 123 2 3
2 124 3 7
3 222 5 4

yyyy has 5 rows.
PKG_PRC_OBJ_ID C D E F
123
124
222
321
245

Now I am going to add a new row to xxxx. The PKG_PRC_OBJ_ID value must not be within {123,124,222,321,245}.
In another words, inserting 321 or 123 is prohibited. Inserting 666 is ok.

use like this

IF NOT EXISTS (SELECT 1 FROM yyyy WHERE PKG_PRC_OBJ_ID =@PKG_PRC_OBJ_ID)
INSERT INTO xxxx
VALUES (othervalues,@PKG_PRC_OBJ_ID)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-23 : 09:53:46

quote:
Originally posted by zhshqzyc

Well, sorry for my bad English. Let me give a simple example.
Suppose xxxx has three rows.
SKU_ID PKG_PRC_OBJ_ID A B
1 123 2 3
2 124 3 7
3 222 5 4

yyyy has 5 rows.
PKG_PRC_OBJ_ID C D E F
123
124
222
321
245

Now I am going to add a new row to xxxx. The PKG_PRC_OBJ_ID value must not be within {123,124,222,321,245}.
In another words, inserting 321 or 123 is prohibited. Inserting 666 is ok.

and that means after the INSERT xxxx is no more a subset of yyyy?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 09:56:15

quote:
Originally posted by zhshqzyc

For bulk insert.

in that case you need to first place it in a staging table and then insert into your table

INSERT INTO xxxx
SELECT fields
FROM staging s
LEFT JOIN yyyy y
ON y.PKG_PRC_OBJ_ID=s.PKG_PRC_OBJ_ID
WHERE y.PKG_PRC_OBJ_ID IS NULL

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 09:56:53

and that means after the INSERT xxxx is no more a subset of yyyy?

yes.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 10:52:19

What does it meant by?

quote:
select 1 from ...

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-23 : 10:55:35

quote:
Originally posted by zhshqzyc

What does it meant by?
quote:
select 1 from ...

EXISTS (or NOT EXISTS) returns 1 if there is at least 1 row returned by the sub-query, else it returns 0. Actual columns returned does not matter, thats why SELECT '1' is used in sub-query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 10:56:20

quote:
Originally posted by zhshqzyc

What does it meant by?
quote:
select 1 from ...

it just returns a value to indicate if table contains any rows satisfying given condition. IF NOT EXISTS(..) will be true only when this query does not returns 1 (no rows)

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-07-23 : 10:58:37

Can I understand it as:
if PKG_PRC_OBJ_ID =@PKG_PRC_OBJ_ID, return 1?
Otherwise returns 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 11:07:45

quote:
Originally posted by zhshqzyc

Can I understand it as:
if PKG_PRC_OBJ_ID =@PKG_PRC_OBJ_ID, return 1?
Otherwise returns 0.

it returns 1 only if you have atleast 1 record in yyyy table satisfying condition PKG_PRC_OBJ_ID =@PKG_PRC_OBJ_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources