Save Every User's Data From 1

Question

Save Every User's Data From 1

Messi

Old Hand

Points: 378
More actions
December 25, 2016 at 1:18 am

#338972

hello guys.
i've a problem. i've a table in which i save data user enters. now i've one requirement and i'm not able to figure out how to do that. the problem is this if 1st user enter some records that are saved in table e.g.
i want if second user or any other user enters record in table the primary key column should start from 1 or every user e.g.
i want every user's record to start from 1 in identity column in same table. how can i achieve this ?
December 25, 2016 at 9:12 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You won't be able to do this with default values, however, would be simple to do with a stored procedure (or you could simply change your insert statement to do what the SP does, what ever your preference).

I've included an extra column, EntryID, as if you want to put a primary key on your table, you're going to need it (as nothing will be guarenteed to be unique otherwise). You don't specifically need the extra column though, but I would suggest it anyway.

All you need do, is then execute the stored procedure, which see further down.

USE TestDB;

GO

CREATE TABLE UserEntry_tbl (EntryID INT IDENTITY(1,1),

ID INT,

FName VARCHAR(50),

LName VARCHAR(50),

UserID INT);

GO

--Now for your procedure.

CREATE PROC InsertUserEntry_sp @user-id INT,

@FName VARCHAR(50),

@LName VARCHAR(50) AS

INSERT INTO UserEntry_tbl (ID, FName, LName, UserID)

SELECT (SELECT ISNULL(MAX(sq.ID),0) + 1

FROM UserEntry_tbl sq

WHERE sq.UserID = @user-id),

@FName,

@LName,

@user-id;

GO

--Then you can insert as follows

EXEC InsertUserEntry_sp @user-id = 1, @Fname = 'Ali', @Lname = 'Ali';

EXEC InsertUserEntry_sp @user-id = 1, @Fname = 'Tesla', @Lname = 'Tesla';

EXEC InsertUserEntry_sp @user-id = 2, @Fname = 'Atif', @Lname = 'Atif';

EXEC InsertUserEntry_sp @user-id = 1, @Fname = 'Saba', @Lname = 'Saba';

EXEC InsertUserEntry_sp @user-id = 3, @Fname = 'Jedu', @Lname = 'Jedu';

EXEC InsertUserEntry_sp @user-id = 3, @Fname = 'Salu', @Lname = 'Salu';

EXEC InsertUserEntry_sp @user-id = 2, @Fname = 'Sam', @Lname = 'Sam';

SELECT *

FROM UserEntry_tbl;

GO

--Clean up

DROP PROC InsertUserEntry_sp;

DROP TABLE UserEntry_tbl;

GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Note that the primary key is unique to all records, so you can't say that the ID is the primary key.

You can say that the ID + UserID (or name) is the PK.

As noted, I would use the advice above to calculate a new ID for entries for each user.