Union All command - create new table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union All command - create new table

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 05:36:58

----query 1 - stock in
select xtxdetail.sku, sum (xtxdetail.qty) as sum_Wareh_stock_in_qty from xtxheader (nolock)
inner join xtxdetail (nolock)
on xtxheader.txno=xtxdetail.txno
where xtxheader.txdate > '20140326' and xtxheader.status = 'c' and (xtxheader.toloc = 'wareh' or xtxheader.fromloc = 'wareh')
and xtxheader.txtype not in ('spo', 'crt', 'cso', 'pd', 'g-', 'cdn', 'dn', 'srt', 'wo', 'tf') and
xtxdetail.sku in ('020862', '020701', '106216', '023031', '022222')

union all
-----------------------
---query 2 - stock out

select xtxdetail.sku, sum (xtxdetail.qty) as sum_Wareh__Stock_out_qty from xtxheader (nolock)
inner join xtxdetail (nolock)
on xtxheader.txno=xtxdetail.txno
where xtxheader.txdate > '20140326' and xtxheader.status = 'c' and (xtxheader.toloc = 'wareh' or xtxheader.fromloc = 'wareh')
and xtxheader.txtype not in ('spo', 'crt', 'cso', 'pd', 'g+', 'sgr', 'wr', 'tf') and xtxdetail.sku in ('020862', '020701', '106216', '023031', '022222')

union all
------------------------------
---query 3 - transfer in
select xtxdetail.sku, sum (xtxdetail.qty) as sum_Wareh_Transfer_in_qty from xtxheader (nolock)
inner join xtxdetail (nolock)
on xtxheader.txno=xtxdetail.txno
where xtxheader.txdate > '20140326' and xtxheader.status = 'c' and xtxheader.toloc = 'wareh' and xtxheader.txtype = 'tf'
and xtxdetail.sku in ('020862', '020701', '106216', '023031', '022222')

union all
---------------------------------
--- query 4 - transfer out
select xtxdetail.sku, sum (xtxdetail.qty) as sum_Wareh_Transfer_out_qty from xtxheader (nolock)
inner join xtxdetail (nolock)
on xtxheader.txno=xtxdetail.txno
where xtxheader.txdate > '20140326' and xtxheader.status = 'c' and xtxheader.fromloc = 'wareh' and xtxheader.txtype = 'tf' and
xtxdetail.sku in ('020862', '020701', '106216', '023031', '022222')

insert into table5(sku, sum_Wareh_stock_in_qty, sum_Wareh__Stock_out_qty,sum_Wareh_Transfer_in_qty,sum_Wareh_Transfer_out_qty)
--------------------------------
Anyone can give idea how to get 4 query into one table.
Please help check my coding it is wrong , I am try to run it but error !

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 07:30:03

P please pray your error messages

aniruddhaa
Starting Member

3 Posts

Posted - 2014-10-16 : 07:30:41

-----Insert 4 query into one table

SELECT '1' as A, '2' as B
UNION ALL
SELECT '2' as A, '3' as B
UNION ALL
SELECT '3' as A, '4' as B
UNION ALL
SELECT '5' as A, '6' as B
UNION ALL
SELECT '7' as A, '8' as B

---Just add INTO <tablename> in the start of first query
SELECT '1' as A, '2' as B INTO UnionAllDS
UNION ALL
SELECT '2' as A, '3' as B
UNION ALL
SELECT '3' as A, '4' as B
UNION ALL
SELECT '5' as A, '6' as B
UNION ALL
SELECT '7' as A, '8' as B

---Inserting data into one table
INSERT INTO UnionAllDS (A,B)
SELECT A,B FROM
(SELECT '1' as A, '2' as B
UNION ALL
SELECT '2' as A, '3' as B
UNION ALL
SELECT '3' as A, '4' as B
UNION ALL
SELECT '5' as A, '6' as B
UNION ALL
SELECT '7' as A, '8' as B) D

Best,
Aniruddha

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 09:12:31

Using your queries, just put the selects after the insert like this:


INSERT INTO table5 (
	sku
	,sum_Wareh_stock_in_qty
	,sum_Wareh__Stock_out_qty
	,sum_Wareh_Transfer_in_qty
	,sum_Wareh_Transfer_out_qty
	)
----query 1 - stock in 
SELECT xtxdetail.sku
	,sum(xtxdetail.qty) AS sum_Wareh_stock_in_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
	AND xtxheader.STATUS = 'c'
	AND (
		xtxheader.toloc = 'wareh'
		OR xtxheader.fromloc = 'wareh'
		)
	AND xtxheader.txtype NOT IN (
		'spo'
		,'crt'
		,'cso'
		,'pd'
		,'g-'
		,'cdn'
		,'dn'
		,'srt'
		,'wo'
		,'tf'
		)
	AND xtxdetail.sku IN (
		'020862'
		,'020701'
		,'106216'
		,'023031'
		,'022222'
		)

UNION ALL

-----------------------
---query 2 - stock out 
SELECT xtxdetail.sku
	,sum(xtxdetail.qty) AS sum_Wareh__Stock_out_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
	AND xtxheader.STATUS = 'c'
	AND (
		xtxheader.toloc = 'wareh'
		OR xtxheader.fromloc = 'wareh'
		)
	AND xtxheader.txtype NOT IN (
		'spo'
		,'crt'
		,'cso'
		,'pd'
		,'g+'
		,'sgr'
		,'wr'
		,'tf'
		)
	AND xtxdetail.sku IN (
		'020862'
		,'020701'
		,'106216'
		,'023031'
		,'022222'
		)

UNION ALL

------------------------------
---query 3 - transfer in
SELECT xtxdetail.sku
	,sum(xtxdetail.qty) AS sum_Wareh_Transfer_in_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
	AND xtxheader.STATUS = 'c'
	AND xtxheader.toloc = 'wareh'
	AND xtxheader.txtype = 'tf'
	AND xtxdetail.sku IN (
		'020862'
		,'020701'
		,'106216'
		,'023031'
		,'022222'
		)

UNION ALL

---------------------------------
--- query 4 - transfer out
SELECT xtxdetail.sku
	,sum(xtxdetail.qty) AS sum_Wareh_Transfer_out_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
	AND xtxheader.STATUS = 'c'
	AND xtxheader.fromloc = 'wareh'
	AND xtxheader.txtype = 'tf'
	AND xtxdetail.sku IN (
		'020862'
		,'020701'
		,'106216'
		,'023031'
		,'022222'
		)

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 21:07:02

I am try to this query , but show error !!!!!!!! anyone can help ?

error message : column "xtxdetail.sku" is invalid in the select list
because it is not contained in either an aggregated function or the group by clause.
------------------------------------------
INSERT INTO table5 (
sku
,sum_Wareh_stock_in_qty
,sum_Wareh__Stock_out_qty
,sum_Wareh_Transfer_in_qty
,sum_Wareh_Transfer_out_qty
)
----query 1 - stock in
SELECT xtxdetail.sku
,sum(xtxdetail.qty) AS sum_Wareh_stock_in_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
AND xtxheader.STATUS = 'c'
AND (
xtxheader.toloc = 'wareh'
OR xtxheader.fromloc = 'wareh'
)
AND xtxheader.txtype NOT IN (
'spo'
,'crt'
,'cso'
,'pd'
,'g-'
,'cdn'
,'dn'
,'srt'
,'wo'
,'tf'
)
AND xtxdetail.sku IN (
'020862'
,'020701'
,'106216'
,'023031'
,'022222'
)

UNION ALL

-----------------------
---query 2 - stock out
SELECT xtxdetail.sku
,sum(xtxdetail.qty) AS sum_Wareh__Stock_out_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
AND xtxheader.STATUS = 'c'
AND (
xtxheader.toloc = 'wareh'
OR xtxheader.fromloc = 'wareh'
)
AND xtxheader.txtype NOT IN (
'spo'
,'crt'
,'cso'
,'pd'
,'g+'
,'sgr'
,'wr'
,'tf'
)
AND xtxdetail.sku IN (
'020862'
,'020701'
,'106216'
,'023031'
,'022222'
)

UNION ALL

------------------------------
---query 3 - transfer in
SELECT xtxdetail.sku
,sum(xtxdetail.qty) AS sum_Wareh_Transfer_in_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
AND xtxheader.STATUS = 'c'
AND xtxheader.toloc = 'wareh'
AND xtxheader.txtype = 'tf'
AND xtxdetail.sku IN (
'020862'
,'020701'
,'106216'
,'023031'
,'022222'
)

UNION ALL

---------------------------------
--- query 4 - transfer out
SELECT xtxdetail.sku
,sum(xtxdetail.qty) AS sum_Wareh_Transfer_out_qty
FROM xtxheader(NOLOCK)
INNER JOIN xtxdetail(NOLOCK) ON xtxheader.txno = xtxdetail.txno
WHERE xtxheader.txdate > '20140326'
AND xtxheader.STATUS = 'c'
AND xtxheader.fromloc = 'wareh'
AND xtxheader.txtype = 'tf'
AND xtxdetail.sku IN (
'020862'
,'020701'
,'106216'
,'023031'
,'022222'
)

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2014-10-17 : 02:17:45

If you have aggregate and non aggregate columns in the select statement then you should group by the result set based on non aggregate column.

The query 1 should be as below to avoid that error:

select xtxdetail.sku, sum (xtxdetail.qty) as sum_Wareh_stock_in_qty from xtxheader (nolock)
inner join xtxdetail (nolock)
on xtxheader.txno=xtxdetail.txno
where xtxheader.txdate > '20140326' and xtxheader.status = 'c' and (xtxheader.toloc = 'wareh' or xtxheader.fromloc = 'wareh')
and xtxheader.txtype not in ('spo', 'crt', 'cso', 'pd', 'g-', 'cdn', 'dn', 'srt', 'wo', 'tf') and
xtxdetail.sku in ('020862', '020701', '106216', '023031', '022222')

group by xtxdetail.sku

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources