duplicate the 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

SQL Server 2000 Forums

SQL Server Development (2000)

duplicate the rows

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2004-06-06 : 07:42:37

hi,

I have a table with ID int (unique column) but is not identity column.
I want to duplicate all the rows, as:

insert into T
select *
from T

but the id is unique !

Noam Graizer

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-06 : 08:51:55

[code]create table t (id int, val char(2))
create unique index ui_id on t (id)

insert into t
select 0,'T0' union all
select 1,'T1' union all
select 2,'T2'

declare
@max_id int,
@sql varchar(100)

select @max_id = max(id)+1
from t

set @sql = 'select identity(int,' + convert(varchar(5),@max_id) + ',1) id,val into #t from t insert t (id,val) select * from #t drop table #t'
exec (@sql)

select * from t
drop table t[/code]

Ophidian
Starting Member

3 Posts

Posted - 2004-06-07 : 03:01:03

DECLARE @max_val int
SELECT @max_val = MAX(ID) FROM table1
INSERT INTO table1 (ID, description)
SELECT @max_val + ID, description FROM table1

PS: Simpler, but I myself think it is not very effective, if you do not have records for each int number. If you are using storedproc, maybe temp table could be a better solution.

========================================

CREATE TABLE #temp (
id INT IDENTITY NOT NULL,
description VARCHAR(50)
)

SET IDENTITY_INSERT #temp ON

INSERT INTO #temp (id, description) SELECT id, description FROM table1

SET IDENTITY_INSERT #temp OFF

INSERT INTO #temp (description) SELECT description FROM table1

DELETE FROM table1
INSERT INTO table1 (id, description) SELECT id, description FROM #temp

DROP TABLE #temp

-- you can always place additional transaction block to be sure

Regards,
John.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources