Post reply

How do I include a column with the table name it came from specified in another column

  • January 29, 2007 at 12:09 pm

    #171946

    I need to be able to insert records into a table from multiple places and be able to specify where each record came from.  In other words:

     

    Insert into EmailList

    Select Name,date,emailaddress,TableName

    from BVC_user

                              The record in the new table would be as follows

                                  John Doe,1/29/2007,JOHN@DOE.com,Bvc_User

     

    I can't figure out how to get the table name.

    any thoughts? I am sure it is probably simple but I haven't been able to figure it out.

  • January 29, 2007 at 12:11 pm

    #686238

    Just specify it as a string, like this:

    Insert into EmailList

    Select Name,date,emailaddress,'BVC_User' as TableName

    from BVC_user

    UNION ALL

    Select Name,date,emailaddress,'GFD_User' as TableName

    from GFD_user

    UNION ALL

    Select Name,date,emailaddress,'TRE_User' as TableName

    from TRE_user

    Hope this helps...

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • January 30, 2007 at 10:28 am

    #686525

    Are you doing this from within a trigger or another function?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 30, 2007 at 10:56 am

    #686540

    You can also use the master..sysobjects table. Your table should be there also - as name, id etc. But in fact the way given by Mr.Farley is easier 

    Kindest Regards,

    Damian Widera
    SQL Server MVP,
    MCT, MCSE Data Platform, MCSD.NET

  • January 30, 2007 at 11:17 am

    #686552

    I am doing this through a trigger.

     

    Thanks

  • January 30, 2007 at 9:33 pm

    #686706

    Then you have to use Rob's method... triggers are absolutely oblivious as to what the source of information is so far as table name goes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

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