Post reply

Inserting Data into a Table

  • July 1, 2004 at 9:34 am

    #162906

    Hey All,

    New to this SQL Server stuff and still trying to find my way.  Need help on an issue I have.  I am trying to insert data from one table into another table several times.  I have a table that contains user ids and I want to insert the ids into another table and at the same time set the data of the other columns of the table.

    The table in question is called UserCommunite and it has 4 columns (UserID, CommuniteID, Authorite, StylesheetCommunite).  What I want to is to insert the UserID from another table:-

    Insert into tbl_UserCommunite (UserId)

    Select ID from tbl_users

    Where ID > 800

    However the CommuniteID column requires a value too and users can belong to more than one Communite.  So the statement I trying to create would look something like:-

    Insert into tbl_UserCommunite (UserID, CommuniteID, Authorite, StylesheetCommunite)

    Select ID from tbl_Users, Set CommuniteID = 1, 3, 7,  StylesheetCommunite = 1

    Where ID > 800

    What I would like to get is where a user id would be inserted 3 times because they have been set to 3 communities.  Not sure if it possible.

    I don't know how to create a SQL statment that combines inserting data into a table and at the same time set the value of the other columns of the table.

    Thanks alot.

    Kutty


    TheKutts

  • July 1, 2004 at 9:54 am

    #512978

    Try this as an example:

    CREATE TABLE #ALLCommuniteID (CommID int)

    INSERT INTO #ALLCommuniteID VALUES (1)

    INSERT INTO #ALLCommuniteID VALUES (2)

    INSERT INTO #ALLCommuniteID VALUES (3)

    Change

    'Select ID from tbl_Users, Set CommuniteID = 1, 3, 7,  StylesheetCommunite = 1 Where ID > 800'

    TO:

    Select ID, CommID, 1 AS 'Authorite', 1 AS 'StylesheetCommunite'

    from tbl_Users, #ALLCommuniteID

    Where ID > 800

     

    This will insert a ID 3 times, assuming each ID belongs to 3 Communities.  If you have another table that contains which communities the ID belongs to then join tbl_Users to that table (join on ID) instead of using this temp table #ALLCommuniteID

     

     

    When in doubt - test, test, test!

    Wayne

  • July 1, 2004 at 10:23 am

    #513001

    Thanks mate, you're a star.

    Worked a treat!

     

    TheKutts


    TheKutts

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply