October 14, 2002 at 12:34 pm
Hello
I'm looking for a way to create a recordset that can be used for mailmerge.
I have the following table:
* ID int, identity
* Description nvarchar(25)
* Count int
The content of the table is:
1 DescriptionA 3
2 DescriptionB 4
The resultset should be:
1 DescriptionA 3
1 DescriptionA 3
1 DescriptionA 3
2 DescriptionB 4
2 DescriptionB 4
2 DescriptionB 4
2 DescriptionB 4
The count column value is the number of duplicate records that must be returned.
Possible with T-SQL (without SP)??
Regards,
Marco
Regards,
Marco
October 14, 2002 at 2:16 pm
Normally I would say use a WHILE loop however this should work better.
1) Create a permanent table for duplicating objects.
CREATE TABLE tblDuplicate (
[uid] [int] PRIMARY KEY
)
And fille it with numbers 1 -your maximum value or something you don't think you will cross. Do not leave gaps.
2) Join the value of Count to uid in the new table like so.
SELECT tM.[ID], tM.[Description], tM.[Count] FROM tblMail tM
INNER JOIN
tblDuplicate tD
ON
tD.uid <= tM.[Count]
this should cause the rows to duplicate since it will have multiple matches for the ON portion.
Also if you know for sure your mail list table does not have gaps for the numbers begining at 1 thru your max value or a set value you would never go over then you could forget the whole extra table and use tblMail tD ON tD.[ID] <= tM.[Count] to do the same thing but depending on size and index structures you may get better performance on having another control table.
Never tried this but the concept should be right and I don't think there is any base I missed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
January 11, 2012 at 1:41 am
I found a solution with dynamic SQL (I know, YUK!), using the built-in REPLICATE string function to multiply an INSERT SQL-string...
DECLARE @strSQL VARCHAR(MAX);
SET @strSQL = '';
SELECT @strSQL = @strSQL + REPLICATE('INSERT INTO #tbl_Duplicates (fld_Value1, fld_Value2) SELECT ' + CAST(fld_Value1 AS VARCHAR(512)) + ', ' + CAST(fld_Value2 AS VARCHAR(512)) + ';', fld_Quantity)
FROM tbl_Originals;
EXEC (@strSQL);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply