question

fred.schmid avatar image
fred.schmid asked

How do I insert records of quantity of one instead of greater?

I have a table with columns: Part#, Qty with records like: a001, 3; b001, 5; and I want records like: a001, 1; a001, 1; a001, 1; b001, 1; b001, 1; b001, 1; b001, 1; b001, 1; Is there a way to do this in TSQL?
tsqlinsert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
A great use of a Numbers or Tally table: declare @YourTable table (PartNo varchar(10), Qty int) insert into @YourTable select 'a001',3; insert into @YourTable select 'b001',5; with cte_tally as (select top 10000 ROW_NUMBER() over(order by sc1.object_id) N from sys.columns sc1, sys.columns sc2 ) select PartNo, 1 from @YourTable join cte_tally on N
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image @SQLShark commented ·
I love tally tables! They answer so many questions.
0 Likes 0 ·
fred.schmid avatar image fred.schmid commented ·
Kev, Thank you for your answer. How do I add more columns? My "real" table has 27 columns with one being the quantity column.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.