Insert rows based on distinct values in another tb

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

SQL Server 2012 Forums

Transact-SQL (2012)

Insert rows based on distinct values in another tb

Author

Topic

moroformat
Starting Member

1 Post

Posted - 2014-05-20 : 22:49:05

Hello,

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40

Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

Any ideas as to how can I update Table 1 to see desired results?

Regards,

moroformat

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-21 : 01:15:17


;with DayWH
AS
	(select 20 as Days_in_warehouse  union all
	select 30 union all
	select 40)
,Table1 
AS
	(select 1 as [PO#] ,10 Days_to_travel,20 Days_in_warehouse union all
	select 2,5,30 union all
	select 3, 7, 40)


SELECT
	[PO#] 
	,Days_to_travel
	,B.Days_in_warehouse
	,A.Days_in_warehouse
FROM 
	DayWH A
	CROSS JOIN Table1 B
ORDER BY [PO#]

output


PO#	Days_to_travel	Days_in_warehouse	Days_in_warehouse
1	10	20	20
1	10	20	30
1	10	20	40
2	5	30	20
2	5	30	30
2	5	30	40
3	7	40	20
3	7	40	30
3	7	40	40

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources