Adding new user info to current old user accounts

All we need is an easy explanation of the problem, so here it is.

Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization.

This was easy enough to do for NEW users:

"INSERT INTO " +
    "aspnet_UsersAddition " +
        "(UserId, firstName, LastName, OrganizationID) " +
"VALUES " +
    "(" +
        "'" + genGUID + "'," +
        "'" + Params.firstName + "'," +
        "'" + Params.lastName + "'," +
        "" + Params.Params["Org"] +
    ")";

However, and this is the problem, is that OLD users who do not have that First/Last and organization associated with their account need to have those added to them.

I have tried the following:

INSERT INTO 
    aspnet_UsersAddition   
        (UserId, firstName, LastName, OrganizationID) 
VALUES 
    ('','','',1) 
WHERE 
    UserId = (SELECT UserId FROM aspnet_Membership WHERE Email = '[email protected]');

But of course the above would not work since the UserId in the aspnet_UsersAddition was non-excitant when comparing it to the aspnet_Membership UserId. The only info I have access to on the page where I am calling this function is their email address and user name.

So I guess my question is – How do I add a new record using the users GUID [that needs to be looked up via email address or user name] so that I can add that info [UserId] to the new table [first/last and organization]?

Note: The UserId is a Uniqueindentifier.

Do I have to make separate query’s

Select old users GUID using their email address.

Insert new record into new table with blanks other than the GUID.

in order to do this? Or is there a way to do this in one query?

The only query I can come up with is a 2 parter:

DECLARE @userGUID VARCHAR(50)
SET @userGUID = (SELECT UserId FROM aspnet_Membership WHERE Email = '[email protected]')

INSERT INTO 
    aspnet_UsersAddition 
        (UserId, firstName, LastName, OrganizationID) 
VALUES 
    (Convert(UNIQUEIDENTIFIER, @userGUID), '', '', 1);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Sorry, I don’t fully follow your question, if you could clarify it a little bit that would be helpful, regardless it sounds like you’re looking for a query like this:

INSERT INTO aspnet_UsersAddition
     (UserId, firstName, LastName, OrganizationID) 
SELECT 
     UserId, '', '', 1
FROM aspnet_Membership 
WHERE Email = '[email protected]'

This will INSERT the UserId with blanks for the FirstName and LastName fields, and 1 for the OrganizationID field, into the new table from the old table, for the supplied email address.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply