I have a table basetable as given below
id name qty price
2 a 2 20
3 d 3 10
4 b 5 60
i want to insert the record into another table based on quantity,As given in able table id 2 has two quantity so table2 will contain 2 records for it with modified quantity as 1.
I tried it using while loop & insert statement and it worked but I want to achieve it using select into statement.
Sidebar Recommendation Concerning rCTEs that Increment a Count
We'll get to the problem in just a minute but here's a recommendation for future code.
As slick as they may seem, you should avoid Recursive CTEs (rCTEs) that increment a count because they're actually slower than a well formed While loop and use a ton more resources even for small row counts. Apologies for not doing a write up here on that subject but it would take a very long post to explain/demonstrate the reasons why. If you're interested on knowing more about the problems with rCTEs that count, there's an article on the subject that you could have a look at. I believe that site below has now made it so you can view articles without being a member.
Hidden RBAR: Counting with Recursive CTE's
(If you want a StackOverflow link, try this one. It has a test you can actually run along with results from my humble box. SQL, Auxiliary table of numbers ).
Here's one of the performance charts from that article. If you look at the Red, almost vertical skyrocket of a line on the far left, that's the performance curve of an rCTE. It's really bad.
Readily Consumable Test Data for the Posted Problem
Getting back to the problem at hand, here's some readily consumable test data that others can use and play with. It's a little bit more than the original just to demonstrate the capabilities.
--===============================================================================
-- Create a test table for the original problem.
-- This is NOT a part of the solution.
-- We're just creating a larger set of data to test with.
--===============================================================================
--===== If the test table exists, drop it to make reruns in SSMS easier.
-- We'll use a Temp Table just to prevent accidental drops of a real table.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO --Just to make table modifications easier if the table already exists.
--===== Create and populate the test table on-the-fly (kept this 2005 compatible)
WITH cteTestData (SomeID,SomeName,Qty,Price) AS
(
SELECT 4,'D',5,60 UNION ALL --Need to make 5 rows.
SELECT 2,'B',4,20 UNION ALL --Need to make 4 rows.
SELECT 5,'F',0,86 UNION ALL --Need to make 0 rows.
SELECT 3,'C',2,10 UNION ALL --Need to make 2 rows.
SELECT 1,'D',2,22 UNION ALL --Need to make 2 rows. Name duplicated.
SELECT 7,'D',1,11 --Need to make 1 row. Name duplicated.
)
SELECT SomeID,SomeName,Qty,Price
INTO #TestTable
FROM cteTestData
;
--===== Display the contents of the table in order by name and quantity
-- just to make verification easy.
SELECT *
FROM #TestTable
ORDER BY SomeName,Qty
;
GO
Building a Useful Tool - fnTally
We could solve this totally inline but we might as well add a very useful tool to our T-SQL server toolbox. This one is a simplified Tally Table - like function that simply counts from 1 to the parameterized max. It's an iTVF (Inline Table Valued Function) which means that, unlike scalar or multi-statement functions, is nasty fast and produces zero logical reads (thank you Mr. Ben-Gan) in the process. It uses cCTEs (Cascading CTEs) based on CROSS JOINs to make it so fast rather than any form of recursion. That also means that you don't have to set the MAXRCURSION option anywhere. You'll find dozens of uses for a function like this in the future.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from 1up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:01:45 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Basic syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@MaxN) t
;
Notes:
1. Can also be used with APPLY.
2. Note that this does not contain Recursive CTEs (rCTE), which are much slower and resource intensive.
3. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
4. As a bit of a sidebar, one of the definitions of the word "Tally" is "to count", which is what this function does.
It "counts" from 1 to whatever the value of @MaxN is every time it's called and it does so without producing any
logical reans and it's nasty fast.
5. I don't normally use the "Hungarian Notation" method of naming but I also have a table name Tally and so this
function needed to be named differently.
-- Jeff Moden
**********************************************************************************************************************/
(@MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL --Could be converted to VALUES after 2008
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL --but there's no performance gain in doing so
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL --so kept it "2005 compatible".
SELECT 1) --10^1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10^4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10^12 or 1 Trillion rows (yeah, call me when that's done. ;-)
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
GO
Solution is Now Easy, Short, and Nasty Fast
Once you have such a function in place, solutions to this type of problem (relational multiplication) become super easy and nearly trivial. Here's a very high performance solution to the problem given using the test data I provided above.
--===== Solve the original problem
SELECT st.SomeID
,st.SomeName
,Qty = 1
,st.Price --Assuming Price is "for each".
FROM #TestTable st
CROSS APPLY dbo.fnTally(st.Qty)
ORDER BY SomeName,SomeID --Not necessary. Just makes visual verification easy.
;
Here's the result set from that.
SomeID SomeName Qty Price
----------- -------- ----------- -----------
2 B 1 20
2 B 1 20
2 B 1 20
2 B 1 20
3 C 1 10
3 C 1 10
1 D 1 22
1 D 1 22
4 D 1 60
4 D 1 60
4 D 1 60
4 D 1 60
4 D 1 60
7 D 1 11
(14 row(s) affected)
You can use a recursive CTE to generate the data:
with cte as (
select bt.id, bt.name, bt.qty, bt.price, 1 as cnt
from basetable bt
union all
select bt.id, bt.name, bt.qty, bt.price, cnt + 1
from cte
where cnt < bt.qty
)
select id, name, 1 as qty, price
from cte;
If you want to put the data in another table, then either use an insert before the select or an into after the select.
Note: If your quantities get really big, you might have to investigate the MAXRECURSION option.
