sql server programming: Bill,

It's perhaps easier than you thought.

However, instead of using [SELECT *], you will have to itemize each of the
columns -that's because a duplicate UserID isn't allowed (I hope!)

So:

INSERT INTO Items
SELECT
Column2
, Column3
, etc
FROM Items i
JOIN Users u
ON i.UserID = u.UserID
WHERE u.LocalID = 0

However, it would be so much easier to know how to help you if you provided
the table DDL. The above is just a blind guess based upon my fantasy about
what the tables might look like.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


[quoted text, click to view]

[quoted text, click to view]

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;

[quoted text, click to view]

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

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

Bill

That's it my brain was just stuck in 1st gear.

Point about DDL well taken - sorry about that - I was trying to make it
easier but instead made it harder for you guys.

Thanks

Bill


[quoted text, click to view]