insertion by joining 2 tables in sql

Question

0.00/5 (No votes)

See more:

here i have 2 tables

SQL

latlongtransactions
(
devId, 
lat, 
lon
) 

and 

devicemaster
(
devId, 
password
)

Now my task is i have to insert lat and lon value in the latlongtransactions table by matching the devId with devicemaster table where password and devId is being checked inputted by the user.

Say for example, i am giving
12345 as devId,
9999 as password ,
lat as 10.2 and
lon as 10.9 as inputs

Now the task is lat and lon will be inserted into latlongtransactions table with the mentioned devId but firstly it will check the devId and password from devicemaster table.(here in devicemaster table devId is the primary key and a foreign key to the another table.)

Waitng for your reply.

Posted 8-Oct-15 20:00pm

Abhishek Das 90

Updated 8-Oct-15 20:13pm

Magic Wonder

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Answer 1 · 2015-10-08T20:21:00

Hi,

Check this...

SQL

Create Procedure Insertvalues
(
@pa_devId int,--or as per your datatype
@pa_pass varchar(100),--or as per your datatype
@pa_lat decimal(10,2),--or as per your datatype
@pa_lon decimal(10,2),--or as per your datatype
@OutPutMess varchar(20)--or as per your requirement
)
As
Begin

Set @OutPutMess=''

Declare @cnt int

Set @cnt=0

Select @cnt = Count(*) from devicemaster where devId=@pa_devId and password=@pa_pass

if @cnt > 0 
Begin

insert into latlongtransactions (devId, lat, lon)  
values (@pa_devId,@pa_lat,@pa_lon)

Set @OutPutMess='Done'--or as per your requirement

end
else
Begin

Set @OutPutMess='Error'--or as per your requirement

End

Select @OutPutMess

End

Hope this will help you.

Cheers

DamithSL · Answer 2 · 2015-10-08T20:27:00

Solution 2

SQL

INSERT INTO latlongtransactions(devId, lat, lon)
    SELECT 12345 , 10.2, 10.9
        FROM dual
        WHERE EXISTS (SELECT * FROM devicemaster
                             WHERE devId = 12345
                               AND password = 9999 )

Posted 8-Oct-15 20:27pm

DamithSL