how to CREATE duplicate rows

this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition.

using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id.

but is there any one to perform this action just using sql?

thanks,

goran.insert your_table
select * from your_table x
inner join (
select field_you_want_to_have_duplicates_on, cnt=count(*)
from your_table group by field_you_want_to_have_duplicates_on
having count(*) > 1) y
on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on|||I think I just heard their head explode...

They want to do it from asp...that's a pure sql solution (which is the fastest btw)

Just loop through you're rs and do an insert for every iteration...|||i will disagree
if they truly wanted to do it from asp, why did they come to the sql forum??

they want sql and they know it!! and there's no denying

isnt that right rdjabarov?
rd? rd... RD!
well he HAD my back on this..|||AAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRHHHHHHHH!!!

I'm on 2 conf calls and typing here|||hmmm ... i think you guys got me wrong, my current solution is implemented in asp using the same method that me and you have described. i was seeking a pure SQL solution.

anyway, thanks for replying, i've tried adapting your sql statement to my tables but am not getting very far. let me explain a little further ...

i have a table called 'tblWinesEnPrimeur' and want to make a duplicate of every row in this table that has a 'SectionID' of 21.

thanks,
goran.|||Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.|||Originally posted by blindman
Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.

Very helpful...

I'm betting you have an IDENTITY Column...ket's say Col1

INSERT INTO tblWinesEnPrimeu (Col2, Col3, Col4, ect)
SELECT Col2, Col3, Col4, ect)
FROM tblWinesEnPrimeu
WHERE SectionId = 21

Without the DDL it's a guess...|||I'd suggest using:INSERT INTO yourTable (collist)
SELECT *
FROM yourTable
WHERE 21 = sectionIdTHis should massively confuse your data, and make the users very happy!

-PatP|||sorry guys, no luck with that either, it's on the client's server and we have no direct access to the database, also the website was orignally designed so that duplicates are not inserted by front-end users of the site.

the sql threw up the same exception as before, simple 'Object required'. like i said it's all workin using the asp/sql combined solution, but the client prefers straight forward sql scripts rather than asp pages which can be run many times and inflate or alter the database.|||I give up...I'm totally lost...

Maybe if you post some (any) code, it might help...|||I'll second that thought! I'd suggest that you use SQL Enterprise Mangler to script everything about that table, the DDL, the indicies, even the permissions. Given that and the nickel description that you posted above, I'll bet that one of us can work out the solution!

-PatP|||insert your_table
select * from your_table x
inner join (
select field_you_want_to_have_duplicates_on, cnt=count(*)
from your_table group by field_you_want_to_have_duplicates_on
having count(*) > 1) y
on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on
where x.SectionID = 21