SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to wrote procedure for these?


How to wrote procedure for these?

Author
Message
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 473
Hai friends,

i ve two tables

create table travel_request
(
request_id int identity primary key,
user_id varchar(100) foreign key references users(user_id)
purpose_travel varchar(100),
total_amount varchar(10)
)


create table onward_journey
(
onward_journey_id int identity,
request_id int foreign key references travel_request(request_id),
departuredate datetime,
from varchar(100),
to varchar(100),
travel mode varchar(100)
)

how to i insert these tables?
and one more think is when i started enter travel purpose request_id ll genereted automatically depends upon that request_id onward_journey ll be there how to do that?
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4482 Visits: 3672
In your previous post http://www.sqlservercentral.com/Forums/Topic1469296-392-1.aspx I gave you allready a sample you can use to insert values into a table that has a foreign key relation to another table. This post is similar to your previous post. So if you adjust the code a bit, you can use it to insert into the tables from this post.

The thing you need to take care of is that the value of the foreign_key fields you enter in the [travel_request] and/or [onward_journey] table, must allready exists in table [users] and/or [travel_request].

-- insert values and search the "users_id" from the [users] table
insert into travel_request
select 2 -- hard coded value
, users_id -- value selected from [users] table
, 500 -- hard coded value
, 'me' -- hard coded value
from users
where username = 'Ram'

-- insert values and search the "request_id" from the [travel_request] table, belonging to a name selected from the [users] table
insert into onward_journey
select
1 -- hard coded value
, request_id -- value selected from [travel_request] table
, '20130703' -- hard coded value
, 'LONDON' -- hard coded value
, 'NEW YORK' -- hard coded value
, 'plane' -- hard coded value
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = 'Ram'



If you want your ID columns in the table to be automaticly increased, you should change these columns to IDENTIY columns. See http://msdn.microsoft.com/en-us/library/ms186775.aspx

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 473
Hai Hanshi,

if its one value means your code is correct.
but i ve many items there so how to do that?
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4482 Visits: 3672
raghuldrag (7/5/2013)
but i ve many items there so how to do that?


Instead of INSERTing a single row where the values are hard-coded, you can also INSERT multiple rows using a SELECT statement. The syntax will be something like: "INSERT INTO {table} ({column1}, {column2}, ...) SELECT {value1}, {value2}, ... FROM {table or joined tables} WHERE {put your filter here}"

But to give you a statement you can use, you have to elaborate your situation a bit more.
- How many items, you want to insert
- What is the source of the items you want to insert (flat file?, another table?, entered by users?, something else?)
- How are the items defined? Please post some samples or sample code!!
- Need the items be inserted in only one of the three tables, or need the items be split over all three tables?
- does all related values allready exists in the tables, or do they need to be inserted?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 473
Hai Hanshi,

i ve 3 tables .
users:
=========


create table users
(
user_id varchar(100) primary key,
username bvarchar(100),
password varchar(100),
designation varchar(10)

)

insert into users values('0002','ram','ramki@123','progrmmer')
insert into users values('0006','ra','rai@123','Accounts')

travel_request:
===========

create table travel_request
(
request_id int identity primary key,
user_id varchar(100) foreign key references users(user_id),
travel_purpose varchar(100),
tota_amount varchar(10)
)

when the users ram is logged and write travel purpose making request means

insert into users values('0002','visit other branch','12000')
now request_id=1

like if some other users logged means ve to captch the user_id.

onward_journey:
============

create table onward_journey
(
onward_journey_id int identity primary key,
request_id int foreign key references travel_request(request_id),
departuredate datetime,
from varchar(10),
to varchar(10),
no.of.days int
)

after enter the purpose here insert ll goes like thes

insert into onward_journey values('01-jul-2013','chennai','banglore','1') onward_joureny=1 but request_id=1
insert into onward_journey values('02-jul-2013','banglore','Hydrebad','1')
onward_joureny=2 but request_id=1



onward_journey may increase depends on insertion dates but request_id is constant depends on travel_request table.

My Requirements is:
===============


if the different may logged means to ve captch user_id insert into travel_request.
i ve to captch the request_id from the praticular user_id pass to onward_journey table

how to do that?
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4482 Visits: 3672
See the code below. I have stated remarks inbetween the code to explain the different actions.
CREATE TABLE users (
users_id VARCHAR(100) PRIMARY KEY
, username VARCHAR(100)
, password VARCHAR(100)
, designation VARCHAR(10)
)

CREATE TABLE travel_request (
request_id INT identity PRIMARY KEY
, users_id VARCHAR(100) FOREIGN KEY REFERENCES users(users_id)
, travel_purpose VARCHAR(100)
, tota_amount VARCHAR(10)
)

CREATE TABLE onward_journey (
onward_journey_id INT identity PRIMARY KEY
, request_id INT FOREIGN KEY REFERENCES travel_request(request_id)
, departuredate DATETIME
, from_place VARCHAR(10)
, to_place VARCHAR(10)
, no_of_days INT
)

-- insert initial data about the different users
insert into users values('0002','ram','ramki@123','progrmmer')
insert into users values('0006','ra','rai@123','Accounts')

--define the user by his/her name
declare @Username varchar(100)
set @Username = 'Ram'
--insert a new row in [travel_request] and use the users_id belonging to the user
insert into travel_request
select
Users_id -- select users_id from table [users] belonging to name given in the where clause
,'visit other branch' as travel_purpose
,'12000' as tota_amount
from users
where username = @Username -- select the name
--insert the travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id) -- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '01-jul-2013' as departuredate
, 'chennai' as from_place
, 'banglore' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username -- select the name
group by travel_request.users_id
--insert a second travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id) -- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '02-jul-2013' as departuredate
, 'banglore' as from_place
, 'Hydrebad' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username -- select the name
group by travel_request.users_id

--define the new user by his/her name
set @Username = 'Ra'
--insert a new row in [travel_request] and use the users_id belonging to the new user
insert into travel_request
select
Users_id -- select users_id from table [users] belonging to name given in the where clause
,'visit seminar' as travel_purpose
,'8000' as tota_amount
from users
where username = @Username -- select the name
--insert the travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id) -- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '03-jul-2013' as departuredate
, 'chennai' as from_place
, 'delhi' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username -- select the name
group by travel_request.users_id

-- show all inserted results by joining the three tables
select *
from users
inner join travel_request
on users.users_id = travel_request.users_id
inner join onward_journey
on travel_request.request_id = onward_journey.request_id



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search