July 4, 2013 at 1:58 am
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?
July 4, 2013 at 2:44 am
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
July 5, 2013 at 12:59 am
Hai Hanshi,
if its one value means your code is correct.
but i ve many items there so how to do that?
July 5, 2013 at 1:22 am
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?
July 5, 2013 at 3:24 am
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?
July 5, 2013 at 5:49 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply