append results of a simple select to table without a cursor?

SQL Apprentice Question
I supect it can be done but I don't see it

scenario:


User has Items and the first user is the Default user


I want all new Users to have the same list of items as the default user


Select * from Users
right join Items
on User.UserID = Items.UserID
where User.LocalID = 0 -- This is the default user


the above gets me the list of x items for the default Customer


Is it possible to append this list to Items for the new User, without using
a cursor?


Thanks



Celko Answers


>> I want all new users to have the same list of items as the default user <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. ISO-11179 prefers that you use collective or plural names for
tables because they are sets, unless they really do model single
entites. Here is a skeleton of what you need:

CREATE TABLE Users
(user_id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY
CHECK (user_id >= 0),
user_name VARCHAR(35) DEFAULT '{{DEFAULT}}' NOT NULL, --usps
standard length
etc.)


--set up the default user with little known trick
INSERT INTO Users(user_id, user_name, ..)
DEFAULT VALUES;


--table of items needed
CREATE TABLE Items
(item_nbr INTEGER NOT NULL PRIMARY KEY, -- needs standard code
item_name VARCHAR(35) NOT NULL,
etc.);


-- ownership is a relation so it has its own table!
CREATE TABLE Ownership
(user_id INTEGER NOT NULL
REFERENCES Users (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
item_nbr INTEGER NOT NULL
REFERENCES Items(item_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (user_id, item_name),
etc.);


-- Now you need to proc to add new users and get them a default package
to start
CREATE PROCEDURE AddNewGuy
(@new_user_id INTEGER, @new_user_name VARCHAR(35), etc.)
AS
BEGIN
INSERT INTO Users (user_id, user_name, etc.)
VALUES (@new_user_id, @new_user_name, etc.);


INSERT INTO Ownership
SELECT @new_user_id, O.item_nbr
FROM Ownership AS O
WHERE O.user_id = 0;
END;



>> Is it possible to append this list to Items for the new User, without using a cursor? <<


Of course. Why did you even think of using a cursor?