Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Using INSERT...SELECT and INSERT....VALUES

Author  Topic  huge Starting Member 31 Posts Posted - 2001-07-25 : 12:17:17 Ok well firsly here is my code: CREATE PROCEDURE sproc_Registration2 ( @uid char(15), --Data for Profile_build @ProvinceID int, @StateID int, @CityName char, @Attentionheader char(75), @AboutYourself char(2000), --Data for Profile_lookingfor @GenderID tinyint, @rel_friendship bit, @rel_email bit, @rel_activity bit, @rel_dating bit, @rel_commitment bit, @rel_marriage bit, @rel_other bit, @AgeRangeLow int, @AgeRangeHigh int, @AboutMatch char(2000) ) AS --Declare variables DECLARE @reg2Done bit --Check if all needed Header are submitted IF (@Attentionheader IS NULL or @AboutYourself IS NULL or @GenderID IS NULL or @AboutMatch IS NULL) SET @reg2Done = 0 ELSE SET @reg2Done = 1 --Put Data into the profile_build table INSERT INTO Pofile_build (Username) SELECT Username FROM UserInfo WHERE UserID = @uid INSERT INTO Pofile_Build (ProvinceID, StateID, CityName, AttentionHeader, AboutYourself, reg2done) VALUES (@ProvinceID, @StateID, @CityName, @AttentionHeader, @AboutYourself, @reg2done) --Put Data into Profile_lookingFor table INSERT INTO Profile_lookingFor (Username) SELECT Username FROM UserInfo WHERE UserID = @uid INSERT INTO Profile_lookingFor (GenderID, rel_friendship, rel_email, rel_activity, rel_dating, rel_commitment, rel_marriage, rel_other, AgeRange_low, AgeRange_high, AboutMatch) VALUES (@GenderID, @rel_friendship, @rel_email, @rel_activity, @rel_dating, @rel_commitment, @rel_marriage, @rel_other, @AgeRangeLow, @AgeRangeHigh, @AboutMatch) GO I think I know why I am getting an error. Becaause it is inserting 2 seperate rows in 2 different tables of data, and since in the second insert (of each table) there is no username (pk) then it generates an error. So my question is how can I do this all in one step. I am aware I can put a where clause in the INSERT...VALUES but I would have to define a variable that knows the username. The problem is I don't know how to do this. But the theory is there ;). Another way to do this if it's possible is to include the INSERT...SELECT and ...Values clause in the same statement, but again I tried many ways of this but always got errors. Can someone help. I am pretty sure method 1 will work except I dont know how to do it. Thanks Mike M