[RESOLVED] Insert a child record for every existing parent ID

[schoemr]

Hi,

I have in the database tblUsers:

UserID
LastName
FirstName
etc.

I now created another table tblUserSettings:

UserSettingID
UserID
Setting1
Setting2
etc.

So every user shall have some settings that I can use all over. e.g. I want to create 3 color themes, etc.

The problem that I now have is that this newly created tblUserSettings is empty and I have a lot of existing UserID's. I want to insert default (child) data for every existing UserID in tblUsers.

Meaning for every UserID in tblUsers insert default values to tblUserSettings.

This is a one time problem because in the future when a new user is created in tblUsers then I shall insert a record with default values for that user in tblUserSettings.

I guess I can do this manually, but it will a pain as it will take long time and maybe i can learn a quick way for future scenarios like this.

Thank you

[TysonLPrice]

Something like:

insert into tblUserSettings(UserID,Setting1,Setting2,....)
select distinct UserID, 'default1','default1',....)
from tblUsers

With the default values hardcoded like 'red','blue' or 1,2,3

[kareninstructor]

A slightly modified version from Tyson where in the event this needed to be ran more than once the following ensures no duplicates. Also note the setting table should have a primary key.

INSERT INTO tblUserSettings ( UserID )
            SELECT UserID
            FROM   dbo.tblUsers t1
            WHERE  t1.UserID NOT IN (   SELECT UserID
                                        FROM   dbo.tblUserSettings );

Defining SQL in code

Dim insertIntoStatement As String =
        <SQL>
        INSERT INTO tblUserSettings ( UserID )
                    SELECT UserID
                    FROM   dbo.tblUsers t1
                    WHERE  t1.UserID NOT IN (   SELECT UserID
                                                FROM   dbo.tblUserSettings );
        </SQL>.Value

[techgnome]

I'd have gone with a left join

INSERT INTO tblUserSettings ( UserID, Setting1, Setting2, Setting3)
            SELECT U.UserID, 'S1', 'S2', 'S3'
            FROM   dbo.tblUsers U
            LEFT JOIN dbo.tblUserSettings US on U.UserID = US.UserID
            WHERE  US.UserID is null

Just another way to skin the cat.

-tg

[schoemr]

How do you all just KNOW these things!!!

Thank you very much

[techgnome]

How do you all just KNOW these things!!!

Thank you very much

The same way one gets to Carnegie Hall... Practice, practice, practice.


-tg