Inserting NON existing rows - 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 2000 Forums

Transact-SQL (2000)

Inserting NON existing rows

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-13 : 08:51:53

Salute..
I want to be able to determine if the row I am inserting in the table exists or not!..if not I want to be able to insert it, otherwise I will ignore it.
The example table that I have consists of the following columns :

TransId(identity)
EmployeeId
TransDateTime
TransFunction
LastUser
LastUpdate

Any ideas??....
Thanks In Advance

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 08:57:49


insert into <dest_table> (<col_list>)
select
    <col_list>
from
    <sourcedata> s
where
    not exists (
        select 1
        from
            <dest_table>
        where
            s.<cols> = <cols>

Insert into the table where a record does not exists already....

Jay White
{0}

Edited by - Page47 on 08/13/2002 08:58:12

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-13 : 09:19:03

First..Thanks..but what is select 1??..and what do u mean by s.<cols> = <cols>..I didnt unserstand ur pseudo code!..could u clarify please?..Second I am filling the table using ADO.NET SQLDataAdapter Fill method. It invokes the corresponding Insert Command that NEEDS paramters to work!. I dont know if U have worked with .NET u can know what I mean..

quote:


insert into <dest_table> (<col_list>)
select
    <col_list>
from
    <sourcedata> s
where
    not exists (
        select 1
        from
            <dest_table>
        where
            s.<cols> = <cols>

Insert into the table where a record does not exists already....

Jay White
{0}

Edited by - Page47 on 08/13/2002 08:58:12

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 09:31:16

quote:
Second I am filling the table using ADO.NET SQLDataAdapter Fill method. It invokes the corresponding Insert Command that NEEDS paramters to work!.

It would have been helpful if you had posted this to begin with.

I am not familiar with the fill method, so I don't know what is available to it.

However, with a straight "insert into table (...) values (...) DML command you cannot check for existing records. You will probably either need to write an INSTEAD OF trigger to disregard rows already in the table. Better yet, you should pass your data to a stored proc, in the old-fashioned way, and write the DML (derived from my psuedo-code) to do what you want.

I'm a caveman. Your modern ways frighten and confuse me.

quote:
but what is select 1??..and what do u mean by s.<cols> = <cols>..I didnt unserstand ur pseudo code!..could u clarify please?..

SELECT 1 is just what it looks like. You could replace it with SELECT 'Whatever you want', so long as it returns a row. s.<cols> = <cols> means the columns in the outer table <sourcedata> equal the columns in your inner table <dest_table>. This is a correlated subquery. It will return something if there is a match between your source and destination. The condition in the where clause only evaluates to true if the subquery DOES NOT return something.

Jay White
{0}

r937
Posting Yak Master

112 Posts

Posted - 2002-08-13 : 10:29:22

i'm not a caveman, i'm a lot older than that

here's another idea -- declare a unique constraint

if the columns are

TransId(identity)
EmployeeId
TransDateTime
TransFunction
LastUser
LastUpdate

then the unique constraint wouldn't have to cover all of them, perhaps just EmployeeID and TransDateTime

just go ahead and attempt the insert and ignore any "unique constraint violated" errors that arise

rudy
http://rudy.ca/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources