none
Hardcode values in Bulk Insert not working RRS feed

  • Question

  • Hi Team,

    I am using Azure SQL DB, I want to extract values from CSV file into SQL Table so I am using BULKINSERT function.

    The Datetime column is not present in CSV so I want to hard code it in BULKINSERT hence I have used a View to insert Datetime and then I am inserting other data from csv to table.

    But it is not working.

    Below is my code:

    CREATE VIEW vw_Stg_FC_Supply
     AS
     SELECT FC_TIMESTAMP FROM Stg_FC_Supply

     BULK INSERT vw_Stg_FC_Supply
           FROM 'Upload_Template.csv'
           WITH
           (
             DATA_SOURCE = 'DemoAzureBlobCSVs',
    DATAFILETYPE = 'char',
             FORMAT = 'CSV',
    FIRSTROW = 2,
             FIELDTERMINATOR = ',',  
             ROWTERMINATOR = '0x0a',
    --FIELDTERMINATOR = '|',
    --ROWTERMINATOR = '\r\n',
             TABLOCK

           );

    please suggest

    Thanks-Sarath

    Thursday, January 23, 2020 5:19 AM

All replies

  • I am using view:

    CREATE VIEW vw_Stg_FC_Supply
     AS
     SELECT GETDATE() AS FC_TIMESTAMP FROM Stg_FC_Supply

    getting the below error

    "Update or insert of view or function 'vw_Stg_FC_Supply' failed because it contains a derived or constant field."

    Please suggest

    Thanks-Sarath

    Thursday, January 23, 2020 6:03 AM
  • Hi sarathkousi, 

    As the error message shows 'it contains a derived or constant field', I tried reproduce your issue . Please check following script . The view 'testview' has a default value . For more information , please refer to CREATE VIEW (Transact-SQL)

    IF OBJECT_ID('test') IS NOT NULL drop table  test
    go 
    create table test (A date)
    insert into test values ('20130202')
    go
    CREATE VIEW testview
    AS
    SELECT GETDATE() AS FC_TIMESTAMP FROM test
    go
    insert into testview
    select * from test 
    /*
    Msg 4406, Level 16, State 1, Line 10
    Update or insert of view or function 'testview' failed because it contains a derived or constant field.
    */
    select * from testview
    /*
    FC_TIMESTAMP
    -----------------------
    2020-01-23 15:39:48.857
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 23, 2020 7:47 AM
  • For that to work, you would have to set up a format file, so that you can map which fields in the file that goes to which columns in the table. Rather than using a new, you could just have a default on the column.

    To use format file, see my article about bulk-load: http://www.sommarskog.se/bulkload.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 23, 2020 10:55 PM