Professional Web Applications Themes

Help! What is the correct way to insert without dups...(see msg) - Microsoft SQL / MS SQL Server

Assuming you have two identical structure tables 1. A and B that have field1 and field2 (field1 is the primary key) 2. A gets some new records that you want to add to B (no dups) 3. I have tried all these below : Insert into B (field1,field2) select field1,field2 from A where field1 not in (select field1 from B) Insert into B (field1,field2) select field1,field2 from A where field1 not in (select field1 from B where A=B) Insert into B (field1,field2) select distinct field1,field2 from A where field1 not in (select field1 from B) Insert into B (field1,field2) select ...

  1. #1

    Default Help! What is the correct way to insert without dups...(see msg)

    Assuming you have two identical structure tables

    1. A and B that have field1 and field2 (field1 is the primary key)
    2. A gets some new records that you want to add to B (no dups)

    3. I have tried all these below :

    Insert into B (field1,field2) select field1,field2 from A where field1 not
    in (select field1 from B)
    Insert into B (field1,field2) select field1,field2 from A where field1 not
    in (select field1 from B where A=B)
    Insert into B (field1,field2) select distinct field1,field2 from A where
    field1 not in (select field1 from B)
    Insert into B (field1,field2) select distinct field1,field2 from A where
    field1 not in (select field1 from B where A=B)

    These have to be incorrect because sometimes it works and sometimes T-SQL
    says it cant insert a duplicate record (field1) which makes no sense to
    me...
    Im sure somebody can see what im TRYING to do... :-)

    What is the correct way to do this?
    -Craig



    Craig Guest

  2. #2

    Default Re: Help! What is the correct way to insert without dups...(see msg)

    Hi Craig,

    Try the following:

    INSERT INTO B (field1,field2)
    select field1,field2
    from A
    where NOT EXISTS(SELECT NULL FROM B WHERE B.field1 = A.field1)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Craig Stadler" <com> wrote in message
    news:phx.gbl... 


    Jacco Guest

  3. #3

    Default Re: Help! What is the correct way to insert without dups...(see msg)

    Hi Ray,

    Please post follow ups to the newsgroups only and don't send mails, so that
    other people can answer your question as well, you are more likely to get a
    quick answer then. Count yourself lucky, because I usually don't reply to
    mails send directly to my account at all (unless accompanied by a cheque
    ;-))

    To answer your question, you have to use a table alias:

    INSERT INTO [machine_remote].database.dbo.B (field1,field2)
    select field1,field2
    from A
    where NOT EXISTS(SELECT NULL FROM [machine_remote].database.dbo.B BB WHERE
    BB.field1 = A.field1)


    ----------------------
    Jacco,
    Thanks for the suggestion. However, what if one table is on another server?

    INSERT INTO [machine_remote].database.dbo.B (field1,field2)
    select field1,field2
    from A
    where NOT EXISTS(SELECT NULL FROM [machine_remote].database.dbo.B WHERE
    [machine_remote].database.dbo.B.field1 = A.field1)

    This wont work because there are too many prefixes... :-(
    Ideas?
    -Craig
    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    not [/ref]
    not [/ref]
    T-SQL 
    >
    >[/ref]


    Jacco Guest

  4. #4

    Default Re: Help! What is the correct way to insert without dups...(see msg)


    How is this possible...? It STILL fails. heeeelllllp is there a bug in
    SQLServer2000 ?

    Heres a detailed layout...

    insert into [remote_machine].Source.dbo.tblB (filespec,name,heading) select
    distinct filespec,name,-1 from files.dbo.tblA where not EXISTS(select
    filespec from [remote_machine].Source.dbo.tblB BB where
    BB.filespec=files.dbo.tblA.filespec) and (file_type ='xyz')

    CREATE TABLE [dbo].[tblA] (
    [file_type] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [filespec] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [name] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    CREATE UNIQUE CLUSTERED INDEX [PK_tblA] ON [dbo].[tblA] ([filespec])

    CREATE TABLE [dbo].[tblB] (
    [Filespec] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Name] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Heading] [int] NOT NULL ,
    ) ON [PRIMARY]

    CREATE UNIQUE CLUSTERED INDEX [IDX_F] ON [dbo].[tblB] ([Filespec])

    --------
    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    that 

    server? [/ref]
    > not [/ref]
    > not [/ref][/ref]
    where [/ref][/ref]
    where [/ref]
    > T-SQL [/ref][/ref]
    to 
    > >
    > >[/ref]
    >
    >[/ref]


    Craig Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Multiple Insert or Looping Insert
    By payado in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 25th, 09:33 PM
  4. Replies: 3
    Last Post: September 30th, 09:24 PM
  5. INSERT did not insert correct # of rows.
    By Polaris in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 02:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139