Multiple row insertion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple row insertion

Author

Topic

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-01 : 08:20:09

i am new to sql server
i want to insert five rows continously in sql server database.
my problem is like this .
i am inserting user datails. i want to get the userid which is an identity column.with the userid i want to insert userid and roleid in another table.Roleid values 1 to 5. ie constant.
an sql procedure is required.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 08:25:52

Can u give ur table structure, some sample data and what needs to be inserted into what ?

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-01 : 09:10:28

quote:
Originally posted by Srinika

Can u give ur table structure, some sample data and what needs to be inserted into what ?

first I need to insert userdetails into the user table
in that userid is an identity column.
with the userid i need to insert into another table called UserRoles.
here I had to take another value Role id. from Roles table
Tables
user columns-Userid(int,identity column),UserName
Roles columns-Roleid(int,identity column),RoleDescription

userroles columns--RoleID(int) , UserID(int).

user Table
User id user name
1- a
2- d
3- d
4- f
5- g

Roles table

role id role desc
1- a
2- a
3- a
4- a
5- a
6- a
role id may increase in future.

finally i want like this for
eg UserId =1
UserRoles tables

role id UserId
1- 1
2- 1
3- 1
4- 1
5- 1
6- 1

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-01 : 09:38:02

hi..

that is bit confusing....can you please explain it in easier way so that we all.. as well as you also understand the problem.......

Papillon

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 09:40:51

For the first 2 tables u can insert the sample data u give as follows

Insert into [user] ([Userid], [UserName])
Select 1, 'a' Union all
Select 2, 'd' Union all
Select 3, 'd' Union all
Select 4, 'f' Union all
Select 5, 'g'

Insert into [Roles] ([Roleid], [RoleDescription])
Select 1, 'a' Union all
Select 2, 'a' Union all
Select 3, 'a' Union all
Select 4, 'a' Union all
Select 5, 'a' Union all
Select 6, 'a'

I can't understand the data of 3rd table
Is it to be filled with the combination of data in above 2 tables ?
eg.

roleid UserId
1- 1
2- 1
3- 1
4- 1
5- 1
6- 1
1- 2
2- 2
3- 2
4- 2
5- 2
6- 2
1- 3
2- 3
3- 3
4- 3
5- 3
6- 3
1- 4
2- 4
....
....

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-01 : 10:02:46

quote:
Originally posted by Srinika

For the first 2 tables u can insert the sample data u give as follows

Insert into [user] ([Userid], [UserName])
Select 1, 'a' Union all
Select 2, 'd' Union all
Select 3, 'd' Union all
Select 4, 'f' Union all
Select 5, 'g'

Insert into [Roles] ([Roleid], [RoleDescription])
Select 1, 'a' Union all
Select 2, 'a' Union all
Select 3, 'a' Union all
Select 4, 'a' Union all
Select 5, 'a' Union all
Select 6, 'a'

I can't understand the data of 3rd table
Is it to be filled with the combination of data in above 2 tables ?
eg.

roleid UserId
1- 1
2- 1
3- 1
4- 1
5- 1
6- 1
1- 2
2- 2
3- 2
4- 2
5- 2
6- 2
1- 3
2- 3
3- 3
4- 3
5- 3
6- 3
1- 4
2- 4
....
....

hi

sorry for the confusion caused
i am little tensed today is last day for project submission.

I need to insert only in One table called UserRoles.

Roles table is already loaded table.
I am able to insert into UserTable.

When i am creating a new user in USER Table it should
insert the ( Roleid from Role table) and (userId from User table) into UserRoles table
for eg new User id=10
UserRoles Table
RoleID UserID
1 10
2 10
3 10
4 10
5 10
6 10

I want an sql procedure for taking UserID from User table and RoleID from Roles table and insert into UserRoles Table

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-01 : 10:03:28

quote:
Originally posted by under2811

hi..

that is bit confusing....can you please explain it in easier way so that we all.. as well as you also understand the problem.......

Papillon

hi

sorry for the confusion caused
i am little tensed today is last day for project submission.

I need to insert only in One table called UserRoles.

Roles table is already loaded table.
I am able to insert into UserTable.

When i am creating a new user in USER Table it should
insert the ( Roleid from Role table) and (userId from User table) into UserRoles table
for eg new User id=10
UserRoles Table
RoleID UserID
1 10
2 10
3 10
4 10
5 10
6 10

I want an sql procedure for taking UserID from User table and RoleID from Roles table and insert into UserRoles Table

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-01 : 10:12:23

step 1: Add your user to you user table:

Insert into Users (....) VALUES (....)

Step 2: get the Identity inserted using the Scope_Identity() function:

declare @id int
set @id = scope_identity()

step 3: Add the roles from your roles table into your UserRoles table:

insert into UserRoles (UserID, RoleID)
select @ID, RoleID
from Roles

If there are only certain default roles that you want to add (not all of them) then be sure to have a column in your Roles table to indicate this. I..e, something like this:

insert into UserRoles (UserID, RoleID)
select @ID, RoleID
from Roles
where Roles.DefaultRole = 1

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 10:17:39

The code should be:

Insert Into UserRoles (RoleID, UserID )
Select Roleid, UrInputValueForuserTbl from [Roles]

UrInputValueForuserTbl has to be used in different ways depending on the situation
eg1. If it is from the Front End, the SQL string needs to be prepared and sent to the DB (Not recommended)
eg2. If it is from a Stored Procedure, the data coming as a passed parameter and that has to be taken in to the query
eg3. If it is from a trigger, the data has to be captured from "Inserted"

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-01 : 12:18:09

quote:
Originally posted by jsmith8858

step 1: Add your user to you user table:

Insert into Users (....) VALUES (....)

Step 2: get the Identity inserted using the Scope_Identity() function:

declare @id int
set @id = scope_identity()

step 3: Add the roles from your roles table into your UserRoles table:

insert into UserRoles (UserID, RoleID)
select @ID, RoleID
from Roles

If there are only certain default roles that you want to add (not all of them) then be sure to have a column in your Roles table to indicate this. I..e, something like this:

insert into UserRoles (UserID, RoleID)
select @ID, RoleID
from Roles
where Roles.DefaultRole = 1

hi
still problem exists
i want to vary the values of RoleiD .
RoleID
userId is constant
RoleiD should be like 1,2,3,4,5,6.
i am confused
shall i have to use cursor

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-01 : 13:13:59

No. the roles are being selected from your Roles table, as I described. Read up on how INSERT works in Books on-line, and also practice doing inserts from SELECT's (as I have shown you) to get a feel for it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources