Post reply

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

  • January 4, 2008 at 9:41 am

    #120069

    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:

  • January 4, 2008 at 10:54 am

    #764921

    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.

  • January 4, 2008 at 11:08 am

    #764926

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

  • January 4, 2008 at 7:57 pm

    #765018

    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.

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

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

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