can anyone give me an example of using current_timestamp / current_user in bcp format file?

  • zxmgh

    SSC Eights!

    Points: 911


    I tried to use current_timestamp / current_user in bcp format file to populate columns in a table, I failed to find an example in books online. (add default to columns is not an option here)

    Here is my table:

    create table t1 ( a int, b datetime, c varchar(10))

    Here is my test.dat:




    Here is my testfm.xml that needs to be modified:

  • SanjayAttray


    Points: 13157

    From the post what I understand is you are loading the data of txt file to a table using bcp.

    for datatime you can put getdate() as default value and for username column put (suser_sname()) as default value in the table. It would give you both current username and also date.

    CREATE TABLE [dbo].[Table1] (

    [col1] [int] NOT NULL ,

    [col2] [varchar] (50)NOT NULL ,

    [col3] [datetime] NOT NULL

    ) ON [PRIMARY]



    CONSTRAINT [DF_Table1_col2] DEFAULT (suser_sname()) FOR [col2],

    CONSTRAINT [DF_Table1_col3] DEFAULT (getdate()) FOR [col3]


    SQL DBA.

  • zxmgh

    SSC Eights!

    Points: 911

    unfortunately using defaults on table is not an option here. company policy.

  • Jeff Moden

    SSC Guru

    Points: 997168

    Never import directly to the final destination table... import into a staging table... should be able to use a default on a staging table but, if not, just update the column which you should be able to define as non-null.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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