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 insert foreign key value in my table? Expand / Collapse
Author
Message
Posted Monday, July 01, 2013 11:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
Hai friends,

i ve two tables

create table users
(
users_id int primary key,
username varchar(20),
password varchar(30),
department varchar(10),
designation varchar(20)
)

insert into users values('0001','Ram','ram@123','IT','programmer')
insert into users values('0002','Ra','ra@123','IT','Designer')
insert into users values('0003','Raju','raju@123','Sales','Area Sales Manager')


create table travel_request
(
request_id int primary key,
user_id int foreign key references users(user_id),
credit_amount float(20),
created_by varchar(10)
)


here the problem is how to insert the user id in travel request table?
Post #1469296
Posted Tuesday, July 02, 2013 12:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,596, Visits: 5,112
Perhaps using an AFTER INSERT TRIGGER?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1469308
Posted Tuesday, July 02, 2013 12:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,078, Visits: 2,411
No trigger needed!!

Just use an INSERT command. The thing you need to take care of is that the value of "users_id" you enter in the [travel_request] table must allready exists in table [users]. The code below give you two possible solutions. The first will insert all values hard-coded. The second will insert the values and searches the "users_id" that belongs to the given "username".

-- insert values hard-coded
insert into travel_request values(1, 3, 200, 'me')
-- insert values and search the "users_id" from the [users] table
insert into travel_request
select 2, users_id, 500, 'me'
from users
where username = 'Ram'
select * from travel_request



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469311
Posted Tuesday, July 02, 2013 12:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
You really have to give more details about your problem. The way the question is asked, I have no idea what problem you have. Please explain what you are trying to do and what problem you encounter.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1469316
Posted Tuesday, July 02, 2013 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,078, Visits: 2,411
raghuldrag (7/1/2013)

create table users
(
users_id int primary key,
[...etc...]
)

insert into users values('0001','Ram','ram@123','IT','programmer')
insert into users values('0002','Ra','ra@123','IT','Designer')
insert into users values('0003','Raju','raju@123','Sales','Area Sales Manager')


Because your table is created with the column "users_id" with datatype INT, you should insert the values as a datatype INT. This prevents an implicit conversion of the VARCHAR datatype to the INT datatype. So the correct INSERT commands should be:
insert into users values(1,'Ram','ram@123','IT','programmer')
insert into users values(2,'Ra','ra@123','IT','Designer')
insert into users values(3,'Raju','raju@123','Sales','Area Sales Manager')


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse