Get Open Query - Progress
This dynamic Stored Procedure will import data from an Progress Database linked into SQL Server using OpenQuery
About
The Oracle module will enable you to import from data from an OpenEdge Database linked into SQL Server, either as a one off or as part of the module created in the main document by using dynamic SQL to generate the relevant scripts.
Create Stored Procedure
CREATE PROC [dbo].[GetOpenQuery_Progress](@LinkServ NVARCHAR(100),@Database NVARCHAR(100),@Schema NVARCHAR(100),@Table NVARCHAR(100),@WHERE NVARCHAR(1000),@TOP INT,@SrcScheme NVARCHAR(100)='PUB',@SkipCols NVARCHAR(MAX)='',@INNER NVARCHAR(MAX)='',@SkipTruncate BIT=0) AS BEGINSET NOCOUNT OFF;SET @SkipCols=ISNULL(@SkipCols,'')SET @SrcScheme=ISNULL(@SrcScheme,'PUB')DECLARE @OUPUT NVARCHAR(MAX)='@CREATE NVARCHAR(MAX) OUTPUT,@PROGRE NVARCHAR(MAX) OUTPUT'DECLARE @CREATE NVARCHAR(MAX),@PROGRE NVARCHAR(MAX),@SQL NVARCHAR(MAX)='SELECT @CREATE=COALESCE(@CREATE+'','','''')+QUOTENAME(COL)+'' ''+SQLType,@PROGRE=COALESCE(@PROGRE+'','','''')+(CASEWHEN COLTYPE=''character'' OR (COLTYPE=''varchar'' AND WIDTH>4000)THEN ''CAST(SUBSTRING("''+COL+''",1,4000) AS varchar(4000)) "''+COL+''" ''WHEN COLTYPE=''varchar''THEN ''CAST(SUBSTRING("''+COL+''",1,''+CONVERT(NVARCHAR(10),WIDTH)+'') AS varchar(''+CONVERT(NVARCHAR(10),WIDTH)+'')) "''+COL+''" ''WHEN COLTYPE=''date'' THEN ''CAST("''+COL+''" AS varchar(20)) "''+COL+''" ''--WHEN COLTYPE=''numeric'' AND WIDTH>''38'' THEN ''CAST("''+COL+''" AS varchar(60)) "''+COL+''" ''WHEN COLTYPE=''numeric'' THEN ''CAST("''+COL+''" AS FLOAT) "''+COL+''" ''ELSE ''"''+COL+''"'' END)FROM (SELECT ID,COL,COLTYPE,WIDTH,SCALE,(CASEWHEN COLTYPE=''varchar'' THEN ''NVARCHAR(MAX)''--WHEN COLTYPE=''numeric'' THEN ''DECIMAL(''+CONVERT(NVARCHAR(10),WIDTH)+'',''+CONVERT(NVARCHAR(10),SCALE)+'')''WHEN COLTYPE=''numeric'' THEN ''FLOAT''WHEN COLTYPE=''integer'' THEN ''INT''WHEN COLTYPE=''bigint'' THEN ''bigint''WHEN COLTYPE=''float'' THEN ''float''WHEN COLTYPE=''REAL'' THEN ''REAL''WHEN COLTYPE=''smallint'' THEN ''smallint''WHEN COLTYPE=''bit'' THEN ''bit''WHEN COLTYPE=''varbinary'' THEN ''varbinary(MAX)''WHEN COLTYPE=''character'' THEN ''NVARCHAR(MAX)''WHEN COLTYPE=''date'' THEN ''DATETIME2''WHEN COLTYPE=''time'' THEN ''time''WHEN COLTYPE=''timestamp'' THEN ''DATETIME2''WHEN COLTYPE=''timestamp_timezone'' THEN ''timestamp_timezone''ELSE NULL END) SQLTypeFROM OpenQuery('+@LinkServ+', ''select ID,COL,COLTYPE,WIDTH,SCALE from sysprogress.SYSCOLUMNS_FULLwhere OWNER = '''''+@SrcScheme+''''' AND TBL = '''''+@Table+'''''AND LEFT(COL,5)<>''''SPARE''''ORDER BY ID'')) xWHERE '','+@SkipCols+','' NOT LIKE ''%,''+COL+'',%'''--PRINT @SQLEXEC sp_executesql @SQL,@OUPUT,@CREATE OUTPUT,@PROGRE OUTPUT--PRINT @CREATE--PRINT @PROGREDECLARE @DROPTABLE NVARCHAR(MAX)='IF NOT OBJECT_ID(''['+@Database+'].['+@Schema+'].['+@Table+']'') IS NULL DROP TABLE ['+@Database+'].['+@Schema+'].['+@Table+']'DECLARE @CREATETABLE NVARCHAR(MAX)='CREATE TABLE ['+@Database+'].['+@Schema+'].['+@Table+']('+@CREATE+')'DECLARE @SELECTTOP NVARCHAR(100)=''IF @WHERE IS NOT NULL SET @WHERE=' '+@WHERE+' 'IF @WHERE IS NULL SET @WHERE=''IF @INNER IS NOT NULL SET @INNER=' '+@INNER+' 'IF @INNER IS NULL SET @INNER=''IF ISNULL(@TOP,0)<>0 SET @SELECTTOP=' TOP '+CONVERT(NVARCHAR(10),@TOP)DECLARE @SELECTRowCount NVARCHAR(MAX)='@SQLRowCount BIGINT OUTPUT'DECLARE @SELECT NVARCHAR(MAX)='INSERT INTO ['+@Database+'].['+@Schema+'].['+@Table+']SELECT *FROM OpenQuery('+@LinkServ+', ''SELECT '+@SELECTTOP+' '+@PROGRE+'FROM '+@SrcScheme+'."'+@Table+'"'+@INNER+''+@WHERE+' WITH (NOLOCK) '')'PRINT @SELECTIF @SkipTruncate=0 EXEC sp_executesql @DROPTABLEIF @SkipTruncate=0 EXEC sp_executesql @CREATETABLEEXEC sp_executesql @SELECTENDYou may need to customise this slightly, but it should give you a basis for running code easily.
