SELECT col1 FROM table1
UNION ALL
SELECT col1 FROM table2
SELECT * FROM TableExcel
UNION ALL
SELECT * FROM TableJSON
SELECT * FROM TableExcel
UNION ALL
SELECT * FROM TableJSON
ORDER BY [Dates], [Type], [Code]
ALTER TABLE TableExcel ADD Flag VARCHAR(10);
GO
ALTER TABLE TableJSON ADD Flag VARCHAR(10);
GO
UPDATE TableExcel SET Flag = 'Excel';
UPDATE TableJSON SET Flag = 'JSON';
SELECT * FROM TableExcel
UNION ALL
SELECT * FROM TableJSON
ORDER BY [Dates], [Type], [Code], [Flag]
SELECT * FROM TableExcel
UNION ALL
SELECT *, NULL ExtraCol FROM TableJSON
ORDER BY [Dates], [Type], [Code], [Flag]
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
ORDER BY [Dates], [Type], [Code], [Flag]
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableOther
ORDER BY [Dates], [Type], [Code], [Flag]
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableInvalid
ORDER BY [Dates], [Type], [Code], [Flag]
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
UNION ALL
SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code], [Amount], [Flag] FROM TableInvalid
ORDER BY [Dates], [Type], [Code], [Flag]
;WITH cte AS
(
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
UNION ALL
SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code], [Amount], [Flag] FROM TableInvalid
)
SELECT [Dates], [Type], SUM([Amount]) Amount
FROM cte
GROUP BY [Dates], [Type]
ORDER BY [Dates], [Type]
;WITH cte AS
(
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON
UNION ALL
SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code], Amount, [Flag] FROM TableInvalid
UNION ALL
SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableDeleted
)
SELECT [Dates], [Type], SUM([Amount]) Amount
FROM cte
GROUP BY [Dates], [Type]
ORDER BY [Dates], [Type]
DECLARE @sql VARCHAR(MAX)
DROP TABLE IF EXISTS #TableOutput
CREATE TABLE #TableOutput
(
[Dates] [datetime] NULL,
[Type] [varchar](1) NULL,
[Code] [varchar](20) NULL,
[Price] [money] NULL,
[Unit] [int] NULL,
[SubTotal] [money] NULL,
[Brokeage Rate] [money] NULL,
[Tax] [money] NULL,
[Stamp Duty] [money] NULL,
[Clearing Fee] [money] NULL,
[Amount] [money] NULL,
[Svc Cost] [money] NULL,
[Flag] [varchar](10) NULL
)
IF OBJECT_ID('TableExcel', 'U') IS NOT NULL
BEGIN
SET @sql = 'INSERT INTO #TableOutput
SELECT * FROM TableExcel'
EXEC(@sql)
END
IF OBJECT_ID('TableJSON', 'U') IS NOT NULL
BEGIN
SET @sql = 'INSERT INTO #TableOutput
SELECT * FROM TableJSON'
EXEC(@sql)
END
IF OBJECT_ID('TableInvalid', 'U') IS NOT NULL
BEGIN
SET @sql = 'INSERT INTO #TableOutput
SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code],
[Price], [Unit], [SubTotal], [Brokeage Rate], [Tax],
[Stamp Duty], [Clearing Fee], [Amount], [Svc Cost], [Flag]
FROM TableInvalid'
EXEC(@sql)
END
IF OBJECT_ID('TableDeleted', 'U') IS NOT NULL
BEGIN
SET @sql = 'INSERT INTO #TableOutput
SELECT * FROM TableDeleted'
EXEC(@sql)
END
SELECT [Dates], [Type], SUM([Amount]) Amount
FROM #TableOutput
GROUP BY [Dates], [Type]
ORDER BY [Dates], [Type]
DECLARE @sql VARCHAR(MAX) = ''
IF OBJECT_ID('TableExcel', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel'
IF OBJECT_ID('TableJSON', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON'
IF OBJECT_ID('TableInvalid', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code], [Amount], [Flag] FROM TableInvalid'
IF OBJECT_ID('TableDeleted', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableDeleted'
SET @sql = '
;WITH cte AS
(
' + @sql + '
)
SELECT [Dates], [Type], SUM([Amount]) Amount
FROM cte
GROUP BY [Dates], [Type]
ORDER BY [Dates], [Type]
'
EXEC(@sql)
IF OBJECT_ID('ViewExcel', 'V') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM ViewExcel'
DECLARE @sql VARCHAR(MAX) = ''
DECLARE @msg VARCHAR(MAX) = ''
IF OBJECT_ID('TableExcel', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableExcel'
ELSE
SET @msg = @msg + CASE WHEN @msg <> '' THEN CHAR(13) + '' ELSE '' END + 'Invalid object name: TableExcel'
IF OBJECT_ID('TableJSON', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableJSON'
ELSE
SET @msg = @msg + CASE WHEN @msg <> '' THEN CHAR(13) + '' ELSE '' END + 'Invalid object name: TableJSON'
IF OBJECT_ID('TableInvalid', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT TRY_CAST([Dates] AS DATETIME), [Type], [Code], [Amount], [Flag] FROM TableInvalid'
ELSE
SET @msg = @msg + CASE WHEN @msg <> '' THEN CHAR(13) + '' ELSE '' END + 'Invalid object name: TableInvalid'
IF OBJECT_ID('TableDeleted', 'U') IS NOT NULL
SET @sql = @sql + CASE WHEN @sql <> '' THEN CHAR(13) + ' UNION ALL ' + CHAR(13) ELSE '' END + 'SELECT [Dates], [Type], [Code], [Amount], [Flag] FROM TableDeleted'
ELSE
SET @msg = @msg + CASE WHEN @msg <> '' THEN CHAR(13) + '' ELSE '' END + 'Invalid object name: TableDeleted'
IF @msg = ''
SET @sql = '
;WITH cte AS
(
' + @sql + '
)
SELECT [Dates], [Type], SUM([Amount]) Amount
FROM cte
GROUP BY [Dates], [Type]
ORDER BY [Dates], [Type]
'
ELSE
SET @sql = 'SELECT ''' + @msg + ''' Error '
EXEC(@sql)
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)