Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Insert From Select

    I dont know why this code is not working (for MS SQL 2000)

    DECLARE @Name nVarChar

    @Name = SELECT DISTINCT Users.Name FROM Users ORDER BY Users.Name

    INSERT INTO Local
    (Numb, Name, Level) VALUES (0, @Name , 1)


    I want to insert into the table Local all the Distinct Names from Users

    the datatype of name :
    [Name] [nvarchar] (100) NOT NULL UNIQUE NONCLUSTERED

    if the name allready exists in the table Localisation I must jump over the error

    how can i do it ?


    thank you
    Last edited by anselme; 12-07-06 at 06:47.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Use insert into to insert bulk data in your table,but the code that you are using will not work .first you can't assign @SOMETHING without using SET. Secondly you can't keep set of data in nvarchar variables.ok?
    Code:
    --SELECT  name FROM BB group by name
    --having count(name)>=1
    Last edited by rudra; 12-07-06 at 06:56.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    thank you rudra but how can i do it ???

    how can insert all the distinct value from a table in another one with a NOT NULL UNIQUE NONCLUSTERED index ?

    INSERT INTO Local
    (Numb, Name, Level) VALUES (0, (select DISTINCT name from users) , 1)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     
    INSERT INTO Local (Numb, Name, Level) 
    SELECT DISTINCT 
     0
     , Name
     , 1
    FROM  Users
    WHERE Name IS NOT NULL
    HTH
    Last edited by pootle flump; 12-07-06 at 07:18. Reason: Added where name is not null after noticing the index definition
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    but what is @Name in that case ?

    it must be a normal, daily, way to do that

    and if the name is allreary In Local i shall get an error

    thank you for helping

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    but what is @Name in that case ?
    Copy & Paste error - I changed it a few minutes ago - you must have been fast off the mark.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    ok i try at once

    thank you

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    it works but i get

    Violation of UNIQUE KEY constraint 'UQ__Localisation__73852659'. Cannot insert duplicate key in object 'dbo.Localisation'.

    Name must be unique

    dont understand that in MS SQL there is not a TRY CATCH block to avoid that

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah - you don't want a try catch. So - there are already names in Local that exists in Users? Easy to fix:
    Code:
    INSERT INTO Local (Numb, Name, Level) 
    SELECT DISTINCT 
               0
               , Name
               , 1
    FROM   Users
    WHERE  Name IS NOT NULL
               AND NOT EXISTS (SELECT NULL
                                       FROM     Local
                                       WHERE   Local.Name = Users.Name)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anselme
    dont understand that in MS SQL there is not a TRY CATCH block to avoid that
    there is, in SQL 2005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2005
    Posts
    266
    and any idea how to avoid it for MS SQL 2000 ?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by anselme
    and any idea how to avoid it for MS SQL 2000 ?
    Sure. By following the advice that you have already been given.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2005
    Posts
    266
    what do you think of :

    INSERT INTO Local (Numb, Name, Level)
    SELECT DISTINCT 0, us.[Name], 1
    FROM Users us
    LEFT JOIN Local Loc ON us.[Name] = Loc.[Name]
    WHERE us.Name IS NOT NULL AND us.Name <> Loc.Name


    and shall i use INNER JOIN or LEFT JOIN ?

    thank you for helping

  14. #14
    Join Date
    Dec 2005
    Posts
    266
    Quote Originally Posted by blindman
    Sure. By following the advice that you have already been given.
    Sorry but i dont know which one

    thank you

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    what do you think of :

    INSERT INTO Local (Numb, Name, Level)
    SELECT DISTINCT 0, us.[Name], 1
    FROM Users us
    LEFT JOIN Local Loc ON us.[Name] = Loc.[Name]
    WHERE us.Name IS NOT NULL AND us.Name <> Loc.Name
    I'm afraid it won't work (as I imagine you know by now) however it is along the lines of a query that would work.

    I think blindman meant my last post (before this one).

    Your query could be (this is the equivalent of what I posted):
    Code:
    INSERT INTO Local (Numb, Name, Level)
    SELECT DISTINCT 0, us.[Name], 1
    FROM Users us
    LEFT JOIN Local Loc ON us.[Name] = Loc.[Name]
    WHERE Loc.Name IS NULL
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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