Guest Posted May 7, 2021 Share Posted May 7, 2021 Can you try the INSERT INTO SELECT command: image.png951571 35.8 KB image.png1226375 29.3 KB Link to comment Share on other sites More sharing options...
Joe Beydoun Posted May 7, 2021 Author Share Posted May 7, 2021 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. Link to comment Share on other sites More sharing options...
Patrick Huebgen Posted May 8, 2021 Share Posted May 8, 2021 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 Link to comment Share on other sites More sharing options...
Warren Hinchliffe Posted May 9, 2021 Share Posted May 9, 2021 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. Link to comment Share on other sites More sharing options...
Brian Suter Posted May 10, 2021 Share Posted May 10, 2021 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. Link to comment Share on other sites More sharing options...
Joe Beydoun Posted May 10, 2021 Author Share Posted May 10, 2021 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now