I would like to write the output of a query into a Sequel Da...

[Guest]

Can you try the INSERT INTO SELECT command:

 

image.png951571 35.8 KB

 

 

image.png1226375 29.3 KB

[Joe Beydoun]

I would like to write the output of a query into a Sequel Database.

Below, statement 1 works, I write specific values into SQL with no problem.

Statement 2 , I get an invalid file error.

Im not familiar with the syntax to do this, please help.

--------------------------------statement 1 (works)---------------------------------

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL SQLMSS

INSERT INTO [staging].[dbo].[DBCAR]

VALUES

(ENGLAND, 2,JAGUAR,V12XKE AUTO,CONVERTIBLE)

END

-RUN

--------------------------------statement 2 (does not work)---------------------------------

SQL

SELECT COUNTRY,SEATS,CAR,MODEL,BODYTYPE FROM CAR;

TABLE ON TABLE HOLD AS HOLDCAR

END

-RUN

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL SQLMSS

INSERT INTO [staging].[dbo].[DBCAR]

SELECT * FROM HOLDCAR

END

-RUN

-EXIT

ERROR:

[42S02] Invalid object name

(FOC1500) : HOLDCAR.

[Patrick Huebgen]

Not sure if you ever tried to use HOLD

ON TABLE HOLD AS DBCAR FORMAT SQLMSS

hold is able to write back to many databases

You can even use database bulk load capabilities if you need to write a larger junk of data.

PATRICK

[Warren Hinchliffe]

And if your data is all coming from the same db, you can use ON TABLE HOLD AS WHATEVER FORMAT SAME_DB PERSISTENCE VOLATILE.

The PERSISTENCE VOLATILE is to create temp tables that are dropped at the end of the session.

[Brian Suter]

No one said it explicitly so I thought I would explain why your case did not work

 

 

 

joe.beydoun:

 

TABLE ON TABLE HOLD AS HOLDCAR

 

 

Is creating a temporary HOLD file on the filesystem. It does have a master file, but its NOT a SQL table.

You then did a SQL SQLMSS INSERT statement. And by specifying SQLMSS after SQL you are effectively saying I am talking to MS SQL directly - just replace &Vars and pass it on. But MS-SQL does not know about the file system hold file.

[Joe Beydoun]

Thanks for the help, I used the FORMAT SQLMSS to make it work. Im sure there is a simpler way of doing this, but Im sharing the code that works if someone else lands here:

In summary,

 

Using a normal FEX I hold my data into a FOCUS table.

I created two files on the sequel server, one to hold daily values and the other historic.

Using an SQL command, I write the FOCUS table into the daily SQL table (it drops and creates the table automatically so its fresh every time).

Write the data from the Daily table to another SQL table. (Primary key includes date, so it does not over write the daily data).

 

TABLE FILE DAILY_ACTIVITY

SUM VALUE

BY KPI

BY REPORT_DATE

ON TABLE HOLD AS KPI_WAREHOUSE_DAILY1 FORMAT FOCUS

END

-*************************************************************************

-WRITE FOCUS TABLE INTO DAILY KPI_WAREHOUSE_DAILY FILE IN SEQUEL TABLE

-************************************************************************

ENGINE SQLMSS SET DEFAULT_CONNECTION DWSQL

SQL

SELECT KPI,REPORT_DATE,VALUE FROM KPI_WAREHOUSE_DAILY1;

TABLE ON TABLE HOLD AS [staging].[dbo].[kpi_warehouse_daily] FORMAT SQLMSS

END

-RUN

-*************************************************************************

-WRITE DAILY TABLE INTO MAIN TABLE KPI_WAREHOUSE TO STORE THE DATA

-************************************************************************

SQL SQLMSS

INSERT INTO [staging].[dbo].[kpi_warehouse]

SELECT E01,E02,E03 FROM [staging].[dbo].[kpi_warehouse_daily]

END

-RUN