Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

[SOLVED] Insert rows from reporting server into db2/sql tables.


Justin Thomas 2

Recommended Posts

We generally use syntax similar to that (along with DROP) to write hold files to DB2 when we need to. I would like to be insert records from the hold file into the DB2 without modifying existing rows. As a workaround, I have written new records to another DB2 temp table and then used insert in an sql passthru. However, I would like to eliminate that step, and just insert directly from the hold file.

Link to comment
Share on other sites

I created a table in SQL Server using SQL passthru:

-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022';  ENGINE &Engine SET DEFAULT_CONNECTION &Connection SQL &Engine CREATE TABLE ggsql ( SEQ_NO INTEGER , REGION CHAR(11) , ST CHAR(02) , CITY CHAR(20) , CATEGORY CHAR(11) , UNITS INTEGER , DOLLARS INTEGER ); END

Then I used the ggsales FOCUS data table to insert rows into the table I just created:

-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022';  ENGINE &Engine SET DEFAULT_CONNECTION &Connection -REPEAT ENDREPEAT 20 TIMES-READFILE GGSALES SQL &Engine INSERT INTO ggsql ( SEQ_NO, REGION, ST, CITY, CATEGORY, UNITS, DOLLARS) VALUES (&SEQ_NO,'&REGION','&ST','&CITY','&CATEGORY',&UNITS,&DOLLARS); END-ENDREPEAT

Since it uses SQL Passthru there is no need to have metadata for the ggsql table. If you have metadata for the table you are inserting rows into, then you could use MODIFY FILE as an alternative.

Link to comment
Share on other sites

I was able to create a synonym for the SQL Server table and then create a hold file and use MODIFY FILE to load it into the SQL Server table.

CREATE SYNONYM ggsql FOR dbo.ggsql DBMS MSODBC END  TABLE FILE ggsales PRINT SEQ_NO REGION ST CITY CATEGORY UNITS DOLLARS ON TABLE HOLD AS ggdata FORMAT ALPHA WHERE RECORDLIMIT IS 20 END  MODIFY FILE ggsql SQL SET LOADONLY FIXFORM FROM ggdata MATCH SEQ_NO ON MATCH REJECT ON NOMATCH INCLUDE DATA ON ggdata END

 Hopefully this gives you another option.

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...