Newbie Question on SQL DB

Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:

> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com

newbie needs help with @@identity

I have a form that submits to multiple tables. After insertion into the first table I need to access the identity key from the record and use is to associate a record in another table. The form element I'm inserting into the second table however, is not a required field so I think I need to check IS NOT NULL first. In my code below I have copied the insert statement for the first table and the conditional and subsequent insert into the 2nd table. I am uncertain where and how I get and use @.@.identity. The error I'm getting when I run the Check Syntax button is: 'incorrect syntax near @.@.identity.'

I appreciate someone telling me how to correct my syntax.

INSERT INTO GPRA_Activities
(
SubmitDate,
StaffId,
GPRAId,
FreedomID,
DocumentDesc,
ActivityTitle,
ActivityDesc

)
VALUES
(
getDate(),
@.StaffId,
@.GPRAId,
@.FreedomID,
@.DocumentDesc,
@.ActivityTitle,
@.ActivityDesc

SELECT @.@.identity
)

if @.KeywordId1 IS NOT NULL

@.@.identity smallint,

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.@.identity,
@.KeywordId1
)

GO

You need to get the value of @.@.IDENTITY Into a local variable and use it. You cannot use the @.@.IDENTITY by itself.

Declare @.valintINSERT INTO GPRA_Activities(SubmitDate,StaffId,GPRAId,FreedomID,DocumentDesc,ActivityTitle,ActivityDesc)VALUES (getDate(),@.StaffId,@.GPRAId,@.FreedomID,@.DocumentDesc,@.ActivityTitle,@.ActivityDesc)SELECT @.val = SCOPE_IDENTITY()if @.KeywordId1ISNOT NULL-- @.@.identity smallint, I dont know what you are trying to do hereINSERT INTO GPRA_KeywordsUsed ( ActivityId, KeywordId )VALUES ( @.val, @.KeywordId1 )GO

|||

I finally got the SQL code below not to error (though I haven't been able to submit my form yet. Keep getting error message about expected number of parameters. That one will be my nemesis.

What is the difference between @.@.identy and SCOPE_IDENTITY?

DECLARE
@.ActivityId smallint
SELECT @.ActivityId = @.@.Identity

if @.KeywordId1 IS NOT NULL

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.ActivityId,
@.KeywordId1
)

|||

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

I'm new at this so please forgive my ignorance.

So, if I use @.@.identity and there are multiple users of the application at once, could the wrong identity get "grabbed"?

|||

Possible. HEre's some info from Books on line:

For example, there are two tables,T1 andT2, and an INSERT trigger is defined onT1. When a row is inserted toT1, the trigger fires and inserts a row inT2. This scenario illustrates two scopes: the insert onT1, and the insert onT2 by the trigger.

Assuming that bothT1 andT2 have identity columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement onT1. @.@.IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted inT2. SCOPE_IDENTITY() will return the IDENTITY value inserted inT1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

|||thank you. I'll change it.