duplicating queried rows - SQL Server Forums

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

Other Forums

MS Access

duplicating queried rows

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2007-10-27 : 12:41:01

I'm trying to write a query which accepts two parameters:

1) primary_key_value
2) quantity_of_stickers

I want a query to use as the datasource for a report that generates a specific amount of stickers.

Everyone usually wants to rid themselves of duplicate rows but I want to duplicate them a spcific amount of times.

Can I do this with SQL? I've got it working by creating a tblTemp table definition and writing records to it the specified amount of times but it would be much easier with SQL.

Thanks for considering.

DM

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-27 : 15:05:01

You could do a procedural build, although similar to your existing method which inserts values X number of times based on the parameter.

While @Counter <= @Quantity
Begin
Insert into LabelTable (Col1,Col2,etc)
Select col1,col2,etc
From SourceTable
Where PK = @PKID

Select @Counter = @Counter + 1
End

Not much different than what you have. I don't believe Access has functionality that would simply repeat a row x number of times on a basic query.

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 15:24:58

Use a Tally table (a table with one column, containing ascending numbers - ours goes up to 8,000) and CROSS JOIN to that.


SELECT *
FROM MyTable
     JOIN MyTallyTable
         WHERE TallyID BETWEEN 1 AND 10
WHERE MyTableID = 1234

will give me one row fom MyTable, repeated 10 times for each row from MyTallyTable

Kristen

lcsgeek
Starting Member

38 Posts

Posted - 2007-10-29 : 11:56:25

Kristen, you are a sick puppy...and I'm envious. I’m off site right now but what you’ve explained seems like it should work. This is an excellent example of a benevolent use of the Internet, I can get information from people a lot smarter than myself. I appreciate your response and I’ll be sure to post the outcome of my attempt.

Thanks dataguru1971 for your consideration as well.

Darin

lcsgeek
Starting Member

38 Posts

Posted - 2007-10-29 : 12:03:06

One question just occurred to me; if my query already contains joins (inner, outer, left, right, ect.) what would be the syntax for tacking on the Cross Join to an existing Join? Is it simply adding an AND statement on to the end of the FROM clause?

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 13:47:56

Just use the syntax above. The JOIN isn't required to actually join to columns any other table


     JOIN MyTallyTable
         ON TallyID BETWEEN 1 AND 10 -- Note typo correction please
or
     JOIN 
     (
         SELECT 1 AS TallyID UNION ALL SELECT 2 UNION ALL SELECT 3 ...
     ) AS X
         ON TallyID BETWEEN 1 AND 10

However, I see this is the MS Access forum, about which I know nothing!, so dunno if this will work outside of SQL Server itself

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources