none
Getting issue in running table script

    Question

  • Here is my code

    DROP DATABASE IF EXISTS MyDB; CREATE DATABASE MyDB; DROP TABLE IF EXISTS MyDB.dbo.Customers; CREATE TABLE MyDB.dbo.Customers ( Customer string, Id int, INDEX idx CLUSTERED(Customer ASC) DISTRIBUTED BY HASH(Customer) ); @customers = SELECT * FROM (VALUES ("Contoso", 123 ), ("Woodgrove", 456 ) ) AS D( Customer, Id ); INSERT INTO MyDB.dbo.Customers SELECT * FROM @customers; @rs = SELECT * FROM MyDB.dbo.Customers; OUTPUT @rs TO "/tableOUTPUT.tsv"

    Am running in data lake extension in visual studio and whenever i run i get this error

    Severity Code Description Project File Line Suppression State Error E_CSC_USER_READAFTERDML: Attempt to read from table MyDB.dbo.Customers which has been modified earlier in the same script. Description: Once a DML has occurred in a script on table T, no statement can later read data from T. Resolution: Separate the DML and read accesses into separate scripts. USQLApplication2 C:\Users\Dell\source\repos\USQLApplication2\USQLApplication2\Script1.usql 28

    USING Outputters.Tsv();

    Thursday, August 2, 2018 10:55 AM

All replies

  • As the error message displays, please try to separate the creating and reading in single script or try modifying the script as below

    DROP DATABASE IF EXISTS MyDB;
    CREATE DATABASE MyDB;
    DROP TABLE IF EXISTS MyDB.dbo.Customers;
    
    CREATE TABLE MyDB.dbo.Customers
    ( 
        Customer string, 
        Id int, 
        INDEX idx  
            CLUSTERED(Customer ASC)
            DISTRIBUTED BY HASH(Customer) 
    );
    
    @customers  = 
      SELECT * FROM 
        (VALUES
           ("Contoso",   123 ),
           ("Woodgrove", 456 )
        ) AS D( Customer, Id  );
    
    
    
    INSERT INTO MyDB.dbo.Customers
        SELECT * FROM @customers;
    
    @rs =
        SELECT *
        FROM @customers;
    
    OUTPUT @rs
    TO "/tableOUTPUT.tsv"
    USING Outputters.Tsv();

    it works fine. check out below screenshots:

    tableOUTPUT.tsv

    JOB STATUS:

    Outputs:

    Tuesday, August 7, 2018 8:18 PM
    Moderator