Printable View
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
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
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
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
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