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

  • zxmgh

    SSC Eights!

    Points: 911

    Hi,

    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:

    1

    2

    3

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

  • SanjayAttray

    SSChampion

    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]

    GO

    ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD

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

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

    GO

    SQL DBA.

  • zxmgh

    SSC Eights!

    Points: 911

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

  • Jeff Moden

    SSC Guru

    Points: 994661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code 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