Insert data from multiple select statements

webressurs
webressurs used Ask the Experts™
on
I have a stored procedure that shall insert some user data into a table. The stored procedure has only one input; @UserId. All userdata related to this UserId shall be selected from multiple tables in my solution, and inserted into another table.

How can this be done without making one SELECT statement for each value (like I do when getting the @UserName)? As you see  in the attached code I get 7 values from 2 different tables based on @UserId.
ALTER PROCEDURE [dbo].[spLogUser]
(
	@UserId varchar(64)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserName varchar(256);
    SET @UserName = (SELECT UserName FROM aspnet_Users WHERE UserId =@userId);
	
    // I can get Firstname, Lastname, Address, Postalcode, Postalplace from tblUserProfileData like this:
    // SELECT Firstname, Lastname, Address, Postalcode, Postalplace FROM tblUserProfileData WHERE USerID = @UserId;

    // I can get  CreateDate, LastLoginDate from aspnet_Membership like this:
    // SELECT CreateDate, LastLoginDate FROM aspnet_Membership WHERE UserId = @UserId;

    // HOW TO INSERT THE SELECT VALUES ABOVE INTO THIS INSERT STATEMENT BELOW:

    INSERT INTO tblUserLog 
    (UserId, UserName, Firstname, Lastname, [Address], Postalcode, Postalplace, CreateDate, LastLoginDate, LastUpdatedDate) 
    VALUES 
    (@UserId, @UserName, Firstname, Lastname, [Address], Postalcode, Postalplace, CreateDate, LastLoginDate, LastUpdatedDate);


END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
this should do:
ALTER PROCEDURE [dbo].[spLogUser]
(
	@UserId varchar(64)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserName varchar(256);
    SELECT @UserName = UserName FROM aspnet_Users 
     WHERE UserId =@userId ;


    INSERT INTO tblUserLog 
    (UserId, UserName, Firstname, Lastname, [Address], Postalcode, Postalplace, CreateDate, LastLoginDate, LastUpdatedDate) 
    SELECT @UserId, Firstname, Lastname, Address, Postalcode, Postalplace 
      FROM tblUserProfileData 
     WHERE USerID = @UserId

Open in new window

Commented:
Hi,
Im fairly new to this, but see below code, i think this may be more suitable?
ALTER PROCEDURE [dbo].[spLogUser]
(
	@UserId varchar(64)
)
AS
BEGIN
    SET NOCOUNT ON;


    INSERT INTO tblUserLog 
    (UserId, UserName, Firstname, Lastname, [Address], Postalcode, Postalplace, CreateDate, LastLoginDate, LastUpdatedDate) 
    (Select  upd.UserId, u.UserName, upd.Firstname, upd.Lastname, [upd.Address], upd.Postalcode, upd.Postalplace, m.CreateDate, m.LastLoginDate, m.LastUpdatedDate
	From tblUserProfileData upd, aspnet_Membership m, aspnet_Users, aspnet_Users U
	where upd.UserID = m.UserID and 
	      upd.userID = u.UserID and
	      upd.UserID = @UserID);


END

Open in new window

Commented:
Try this code

ALTER PROCEDURE [dbo].[spLogUser]
(
      @UserId varchar(64)
)
AS
BEGIN
    SET NOCOUNT ON;

   INSERT INTO tblUserLog
   (
    UserId, UserName, Firstname, Lastname, [Address],
    Postalcode, Postalplace, CreateDate, LastLoginDate, LastUpdatedDate
   )
   SELECT
        @UserId
      , Users.UserName
      , UserProfile.Firstname
      , UserProfile.Lastname
      , UserProfile.[Address]
      , UserProfile.Postalcode
      , UserProfile.Postalplace
      , Membership.CreateDate
      , Membership.LastLoginDate
   FROM
      aspnet_Users Users
   INNER JOIN
      tblUserProfileData UserProfile
   ON
      Users.UserId = UserProfile.USerID
   INNER JOIN
      aspnet_Membership Membership
   ON
      Users.UserId = Membership.UserId
   WHERE
      Users.UserId = @UserId  
END
GO

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial