How To: INSERT only UNIQUE values into a Table???

How To: INSERT only UNIQUE values into a Table???

Post by Alan Maile » Wed, 12 Sep 2001 05:32:29



As a relatively new SQL Server 7 user, I'm not sure the following is
even possible. But, if it is, can somebody let me know how I'd go
about it:

I'm using a Visual Basic app I'm writing to insert values into a SQL
Server table that happens to have only one column.  Values get
inserted into this table during a Visual Basic Loop which loops
through various INSERT INTO statements.

The challenge is this:  I don't want to enter the same value twice
into the table, but I want to continue entering 'new' values into it
using the various INSERT INTO statements that loop through my VB
proceedure.

So, I *guess* my question is this:  I know you can create a table that
will not allow the same value to be entered twice into a single
column...

...but is there a way to create a table that will allow *new* values
to be inserted and *skip* redundant values; without screwing up the
looping structure I've described above.

I don't know whether I'm explaining this well... or even asking the
right question.  Any feedback you'd care to provide would be very much
appreciated.  Thanks in advance.

 
 
 

How To: INSERT only UNIQUE values into a Table???

Post by Ross Norne » Wed, 12 Sep 2001 06:47:43


Maybe two different ways of approaching it.
One, you could create a unique index on that field in that table, and when a
dup is inserted into the table, do error handling on the VB side to capture
the app, log it or whatever you want to do with that error, then move on in
your looping process as normal.
Or two, if your running SQL 2000 you could create a instead of trigger and
do the unique check ahead of time and log dups to a different table or just
throw them out if you wanted.
Warning though, option 1 is by far the fastest!

--
Ross Nornes
DBA Services Manager
James Tower
http://www.JamesTower.Com


Quote:> As a relatively new SQL Server 7 user, I'm not sure the following is
> even possible. But, if it is, can somebody let me know how I'd go
> about it:

> I'm using a Visual Basic app I'm writing to insert values into a SQL
> Server table that happens to have only one column.  Values get
> inserted into this table during a Visual Basic Loop which loops
> through various INSERT INTO statements.

> The challenge is this:  I don't want to enter the same value twice
> into the table, but I want to continue entering 'new' values into it
> using the various INSERT INTO statements that loop through my VB
> proceedure.

> So, I *guess* my question is this:  I know you can create a table that
> will not allow the same value to be entered twice into a single
> column...

> ...but is there a way to create a table that will allow *new* values
> to be inserted and *skip* redundant values; without screwing up the
> looping structure I've described above.

> I don't know whether I'm explaining this well... or even asking the
> right question.  Any feedback you'd care to provide would be very much
> appreciated.  Thanks in advance.


 
 
 

How To: INSERT only UNIQUE values into a Table???

Post by Andy Macki » Wed, 12 Sep 2001 17:03:40


Sounds like an index with IGNORE_DUP_KEY might be the answer. For example

CREATE TABLE Test(SomeInt int NOT NULL)

CREATE UNIQUE INDEX IDX_Test
ON Test(SomeInt)
WITH IGNORE_DUP_KEY
go

INSERT INTO Test(SomeInt) Values(1)
INSERT INTO Test(SomeInt) Values(2)
INSERT INTO Test(SomeInt) Values(2)
INSERT INTO Test(SomeInt) Values(3)
INSERT INTO Test(SomeInt) Values(3)
INSERT INTO Test(SomeInt) Values(4)

SELECT * FROM TEST

gives this answer

SomeInt
-----------
          1
          2
          3
          4

Regards,
Andy Mackie.


Quote:> As a relatively new SQL Server 7 user, I'm not sure the following is
> even possible. But, if it is, can somebody let me know how I'd go
> about it:

> I'm using a Visual Basic app I'm writing to insert values into a SQL
> Server table that happens to have only one column.  Values get
> inserted into this table during a Visual Basic Loop which loops
> through various INSERT INTO statements.

> The challenge is this:  I don't want to enter the same value twice
> into the table, but I want to continue entering 'new' values into it
> using the various INSERT INTO statements that loop through my VB
> proceedure.

> So, I *guess* my question is this:  I know you can create a table that
> will not allow the same value to be entered twice into a single
> column...

> ...but is there a way to create a table that will allow *new* values
> to be inserted and *skip* redundant values; without screwing up the
> looping structure I've described above.

> I don't know whether I'm explaining this well... or even asking the
> right question.  Any feedback you'd care to provide would be very much
> appreciated.  Thanks in advance.

 
 
 

1. Generating and inserting a unique value into sybase tables

I have an application where I need to generate a unique key for each
row that I'm inserting.  I also need to know the key that's been
generated for later use.

In other words, for each row that I insert into a table, I need Sybase
to generate a unique id number for that row, store it in a column in the
row, and return to me that id number.

This is what I've been doing (note that I send all of the below to
Sybase via the Sybase DBI in perl):

$insert = "begin transaction  \
              INSERT into sell_trips  \
              select max(tripno)+1, $id, \"$pnr_loc\", 0 FROM sell_trips  \
              select max(tripno) FROM sell_trips  \
           commit";

I then do a sth->execute and read the result set that comes back for
the identifier.

It strikes me that the above is highly inefficient, especially as the
table starts to get large.

What's the better way to do this?

Thanks,

greg

--
gregory travis     |"If you're going to kill someone there isn't much reason

                   |with Novell when we pull the trigger."  MSFT's Jim Allchin

2. CreateDatabase method question

3. How to detect an insert error of a non-unique value to a unique field

4. pg_dump - getTables() problem

5. ANSI SQL

6. Table of unique values fed by a fact table

7. OLAP, IIS, and cell-level security

8. Insert record -> getting unique id value

9. Get a Unique value to each row using INSERT

10. ADO Column value on insert (unique Field)

11. hown to insert a unique identity value

12. Insert Trigger - Unique Value