Facil strips off additional sql statements from temp table script

[costa100]

Hello,

I have the following:

    scripts:
      - include: "*.sql"
      - for: TempJobTemplate.sql
        tempTables:
          - definition: CreateTable/TempJobTemplate.sql

In the CreateTable/TempJobTemplate.sql script I have the create table statement followed by a create index statement (I did not use a "go" between the statements, I ended the create table statement with semicolon). The create index statement is removed from the script generated code, even though I want it there. Is it normal? What can I do to tell Facil to leave it there?

Thanks

Edited: I added the folder the name to the script name.

[cmeeren]

I need more information. What do you mean by "is removed from"? How and where to you see that this is removed? What is the script?

[costa100]

Yes, my definition script (CreateTable/TempJobTemplate.sql) contained something like this:

create table #TempSomeTable (
      Field1 int NOT NULL PRIMARY KEY
    , Field2 varchar(100) NOT NULL
);
create index ix_TempSomeTable_Field2 on #TempSomeTable(field2);

The generator removed the create index statement. That's what I meant.

    [<EditorBrowsable(EditorBrowsableState.Never)>]
    member this.CreateTempTableData
      (
        ``TempSomeTable``: seq<``TempSomeTable``.``TempSomeTable``>
      ) =
      [
        TempTableData
          (
            "#TempSomeTable",
            """
            CREATE TABLE #TempSomeTable
            (  
              Field1 int NOT NULL PRIMARY KEY
            , Field2 varchar(100) NOT NULL

            );

            """,
            (``TempSomeTable`` |> Seq.map (fun x -> x.Fields)),
            2,
            Action<_> this.userConfigureBulkCopy
          )
      ]

Sorry for not being more explicit.

Thank you

[cmeeren]

Thanks, I'll have a look at it. Can't guarantee I can look at it until after the holidays. As a workaround and experiment, could you try to have the index statement as part of the table creation statement?

[costa100]

Yes, of course, and thank you for the suggestion. I didn't know you can create the index inside the create table statement. I knew about the PK.

[cmeeren]

By the way, please let me know the result. It could shed some light on the issue when I start investigating.

[costa100]

Sorry, what result are you referring to? Do you mean having the index specified inside the create table statement? If this is what you mean, I tried it, and the index statement is removed as well which leads me to believe that in the code you use some kind of script generation that pulls the create table statement without anything else.

[costa100]

Hi,

I tested more, and it seems to work fine. I don't know what to say 😳 I feel embarrassed. I made sure everything is saved, I triggered the generator changing the yaml file and by rebuilding the project. Now it seems to include the everything from the sql script. It's strange. Anyway, you can close the issue - if I discover anything, we can re-open it. Sorry for the false alarm.

[cmeeren]

Ok! Weird, but good to know it works. (I briefly checked the code when you posted the issue and couldn't immediately find anything wrong; the source should be used as-is.)