Click here to Skip to main content
13,044,467 members (59,627 online)
Rate this:
Please Sign up or sign in to vote.
Actually i am having three tables (registration,access,user_access) here what i want is i am inserting a record in registration table,at the same time i want to insert all the records(records are in static) in access table to user_access table with registration_id.........
//i inserted one record in registration table
registration_id | username | loginid | password ------------------------------------------------------------------------------
1 | nitish | | 12345

at the same time i already having 3 records(static) in access table as shown bellow
accessid | description | active
1 | reports | 1
2 | leads | 1
3 | import | 1

i want to insert access records in user_access table with registration_id looks like below table
registration_id | access_id | active
1 | reports | 1
1 | leads | 1
1 | import | 1

actually i am unable to explain my problem clearly......

this was my stored procedure code
if @option='InsertRegistrationDetails'
insert into School_Details(School_Name,Address,Email_ID,Mobile_No,Fax_No,City,State,Country,Zip_Code,Created_On)values(@School_Name,@Address,@Email_ID,@Mobile_No,@Fax_No,@City,@State,@Country,@Zip_Code,getdate())
insert into Login_Details(School_ID,First_Name,Login_id,Password,Status,Designation,Created_on)values(@School_ID,@First_Name,@Login_id,@Password,@Status,@Designation,getdate())
update login_details set school_id=(select School_ID from school_details where school_name=@School_Name) where Login_id=@Login_id
--insert into User_Access(user_identity,Access_id,Active)select Access_id,active from access and user_identity=(select id from login_details where Login_id=@Login_id)
Posted 7-Feb-13 0:11am
Updated 7-Feb-13 0:16am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

An often overlooked aspect of an insert statement is that you don't have to use the VALUES statement. What you can do, instead, is use another select statement in this place. What you could do here is do an insert/select query. Now, before I show you the syntax, I'd like to point out that your user_access table example looks wront to me. I assume that access_id in this table should actually hold the key, instead of the description.

Right, the query:
INSERT INTO user_access
SELECT registration.registration_id, access.access_id, 1
FROM registration JOIN access
You may notice that I haven't actually put any join criteria in - this is because what you have here is a cross join, so all the rows from both tables match.
ntitish 7-Feb-13 6:39am
it is showing syntax error near access.
and with out primary key and foreign key relationship in the tables and with out using where clause can we join the tables
Pete O'Hanlon 7-Feb-13 6:54am
The code that you updated after I answered this shows that you have a syntax error. "insert into User_Access(user_identity,Access_id,Active)select Access_id,active from access and user_identity=(select id from login_details where Login_id=@Login_id)" The error is because you have an and clause in there. It should be a where clause.
ntitish 7-Feb-13 6:54am
thanks a lot sir......bec understanding the problem is greater then solving problem once again thanks sir.....its working....
Pete O'Hanlon 7-Feb-13 6:55am
Good job. Well done.
ntitish 7-Feb-13 8:39am
sir now i want to insert the records into user_access from access as same as above but here i want to save active field into 0 instead of 1 while it possible sir,.
Pete O'Hanlon 7-Feb-13 8:50am
Replace with 0 in your SELECT statement.
ntitish 8-Feb-13 3:35am
thanks once again sir....i got it
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I did not understand your question completely. Take a look at Using SELECT to INSERT records[^], it might help you. Let me know if you have any questions.
ntitish 7-Feb-13 7:09am
thanks for your involvement sir.....
__TR__ 7-Feb-13 7:25am
You are welcome.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100