Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to wrote procedure for these? Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 1:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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?
Post #1470335
Posted Thursday, July 4, 2013 2:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 2,225, Visits: 2,661
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’! **
Post #1470345
Posted Friday, July 5, 2013 12:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hai Hanshi,

if its one value means your code is correct.
but i ve many items there so how to do that?
Post #1470590
Posted Friday, July 5, 2013 1:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 2,225, Visits: 2,661
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’! **
Post #1470605
Posted Friday, July 5, 2013 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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?
Post #1470635
Posted Friday, July 5, 2013 5:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 2,225, Visits: 2,661
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’! **
Post #1470681
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse