Create Table As Select

Hi, I need to build some tables in the application database. I am using a extensibility rule with a StringBuilder and the BRAPi.Database.ExecuteActionQuery command to do that.

The "Create table" command is working fine. Eg:
sqlScript.AppendLine("CREATE TABLE [PayrollMapping](")
sqlScript.AppendLine("[Payroll] varchar(255),")
sqlScript.AppendLine("[Pay_Basis] varchar(255),")
sqlScript.AppendLine("[Salary_Annual_Factor] Int")
sqlScript.AppendLine(") On [PRIMARY]")

But I cannot make the "Create table as select" command work. Eg this does not work (Error: Incorrect syntax near the keyword 'Select')
sqlScript.AppendLine("CREATE TABLE [EBSRegister] As")
sqlScript.AppendLine("Select [RegisterID], [FirstName], [LastName]")
sqlScript.AppendLine("From XFW_PLP_Register")

Is the "Create table as select" not a valid command for the OneStream BRAPi.Database or is there simply something wrong with the syntax above?

Thank you

Data Integration

AndreaF
3 years ago
After testing many suggestions I found that the following line of code does what I was looking for, i.e. it creates a table which is a copy of the old table, without copying any data from the old table (because of the Where 1 = 2 condition):
sqlScript.AppendLine("Select * Into EBSRegister From XFW_PLP_Register Where 1 = 2")
Sai_Maganti
3 years ago
Yes you're on the right track though the new table won't have the indexes/constraints copied over.
The "Create Table As Select (CTAS)" only works in Azure Synapse Analytics Platform System aka PDW. Also works in oracle but not in SQL Server.

NidhiMangtani
Contributor III
3 years ago
Hello,
The "create table as select" looks correct but seems SQL system inside OneStream is not able to comprehend this.
Not all SQL queries, vb.net functions would work as is inside OneStream.
HRunyon
New Contributor III
3 years ago
You can try and create the table first and then do the insert like...
Using dbConn As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
'Create the table, only need this once
Dim sqlDM As String = "Create table EBSRegister ([RegisterID] varchar(255),[FirstName] varchar(255),[LastName] varchar(255))"
BRApi.Database.ExecuteActionQuery(dbConn, sqlDM,False, True)
'Insert the records into the new table
Dim sql As String = "insert into EBSRegister (RegisterID,FirstName,LastName) select RegisterID,FirstName,LastName from XFW_PLP_Register"
BRApi.Database.ExecuteActionQuery(dbConn, sql,False, True)
End Using
- AndreaF
  Contributor III
  3 years ago
  Hi and thank you for your replies. Yes your solution would work, but the reason why I was trying to avoid using the "Create table <table name> (<column1>, <column2>, etc.)" command is that the table I want to recreate has around 50 columns, so I was looking for a way to copy the structure from an existing table
AndreaF
Contributor III
3 years ago
After testing many suggestions I found that the following line of code does what I was looking for, i.e. it creates a table which is a copy of the old table, without copying any data from the old table (because of the Where 1 = 2 condition):
sqlScript.AppendLine("Select * Into EBSRegister From XFW_PLP_Register Where 1 = 2")
- Sai_Maganti
  Contributor II
  3 years ago
  Yes you're on the right track though the new table won't have the indexes/constraints copied over.
  The "Create Table As Select (CTAS)" only works in Azure Synapse Analytics Platform System aka PDW. Also works in oracle but not in SQL Server.
ckattookaran
VIP
2 years ago
Insert into should work fine, did you try that?
- AndreaF
  Contributor III
  2 years ago
  Hi, yes the "insert into" command works in OneStream.
  The command I've used to create the table is: "Select * Into NewTableName From OldTableName Where 1 = 2"

Forum Discussion

Create Table As Select

Related Content

Create Table in SIC

Create Custom Table in Cloud

Create a dependent dropdown list in SQL Table Editor

SQL Query to External Table

Unable to create an application