Insert From Select

[anselme]

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

[mikr0s]

INSERT INTO Local (Numb, Name, Level)
SELECT DISTINCT 0, [Name], 1
FROM Users
Order BY [Name]


--if you want to avoid inserting dublicate records, try the following one:

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
Order BY us.[Name]


--HTH

[anselme]

I dont understand WHERE Loc.Name IS NULL

but I cant get it in that way, i get :

Cannot insert the value NULL into column 'Name', table 'Localisation'; column does not allow nulls. INSERT fails.

if i try WHERE loc.Name IS NOT NULL I get :

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


us.[Name] must be NOT NULL
and loc.Name must be unique


thank you

[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


and shall i use INNER JOIN or LEFT JOIN ?

thank you for helping

[anselme]

maybe it is my way to ask it ... certainly

1) users table is allready full
2) i want to get all the distinct users.Name to insert them in a new table Localisation
3) maybe a few names are allready in the table Localisation and they must be NOT NULL and UNIQUE