@Database NVARCHAR(100) ,
@TableName NVARCHAR(100) = 'XX_Common_Data' ,
@Server NVARCHAR(100) = '' ,
@UserID NVARCHAR(100) = '' ,
@Password NVARCHAR(100) = ''
DECLARE @execSQL NVARCHAR(4000)
SELECT @execSQL = 'INSERT INTO dbo.XX_Common_Data ([Emp_No], '
+ '[A1], [B1], [C1], '
+ '[D1], [E1], [F1], [G1], '
+ '[H1], [I1], [J1], [K1], [L1])'
+ ' SELECT '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(40), ISNULL([Emp_No], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([A2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([B2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([C2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(100), ISNULL([D2], '''')))), '
+ ' [E2], '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([F2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([G2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(40), ISNULL([H2], '''')))), '
+ ' CASE '
+ ' WHEN ISNULL([I2], 0) < 0 '
+ ' THEN 0 '
+ ' ELSE ISNULL([I2], 0) '
+ ' END , '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(10), ISNULL([J2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([K2], '''')))), '
+ ' LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([L2], '''')))) '
IF @Server = ''
BEGIN
SELECT @execSQL = @execSQL + ' FROM ' + @Database + '.dbo.' + @TableName + ' '
END
ELSE
BEGIN
SELECT @execSQL = @execSQL + ' FROM OPENROWSET(''SQLOLEDB'', ''' +
@Server + '''; ''' + @UserID + '''; ''' + @Password +
''', ''SELECT * FROM ' + @Database + '.dbo.' + @TableName + ''') AS a '
END
EXEC (@execSQL)