Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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.........
example:
//i inserted one record in registration table
------------------------------------------------------------------------------
registration_id | username | loginid | password ------------------------------------------------------------------------------
1 | nitish | ab@gmail.com | 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'
begin
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)
end
Posted 7-Feb-13 1:11am
ntitish1.5K
Edited 7-Feb-13 1:16am
v2
Rate this: bad
good
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.
  Permalink  
Comments
ntitish at 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 at 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 at 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 at 7-Feb-13 6:55am
   
Good job. Well done.
ntitish at 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 saving.is it possible sir,.
Pete O'Hanlon at 7-Feb-13 8:50am
   
Replace access.active with 0 in your SELECT statement.
ntitish at 8-Feb-13 3:35am
   
thanks once again sir....i got it
Rate this: bad
good
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.
  Permalink  
Comments
ntitish at 7-Feb-13 7:09am
   
thanks for your involvement sir.....
__TR__ at 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
0 Kornfeld Eliyahu Peter 169
1 George Jonsson 145
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,382


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2014
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