рдЦреБрд▓реА рдХреНрд╡реЗрд░реА рдкреНрд░рд╛рдкреНрдд рдХрд░реЗрдВ - рдкреНрд░рдЧрддрд┐
рдпрд╣ рдЧрддрд┐рд╢реАрд▓ рд╕рдВрдЧреНрд░рд╣реАрдд рдкреНрд░рдХреНрд░рд┐рдпрд╛ OpenQuery рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдХреЗ SQL рд╕рд░реНрд╡рд░ рд╕реЗ рдЬреБрдбрд╝реЗ рдПрдХ рдкреНрд░рдЧрддрд┐ рдбреЗрдЯрд╛рдмреЗрд╕ рд╕реЗ рдбреЗрдЯрд╛ рдЖрдпрд╛рдд рдХрд░реЗрдЧреА
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.
