Replicating a record - 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

Transact-SQL (2000)

Replicating a record

Author

Topic

ciggs
Starting Member

8 Posts

Posted - 2002-03-26 : 06:57:39

Hi guys,
Sorry - I'm a bit of a beginner so this is probably quite easy.
I have a large table (50 fields) with an identity field as the primary key. Is there a an easy way to replicate the data in a record (minus the identity field) and insert that as a new record.
Cheers in advance for the help.
ciggs

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-26 : 07:15:12

insert into tableabc (col2, col3, col4, col....col50)
select col2, col3, col4, .... col50 from tableabc where col1 = xyzvalue

col1 is the name of your identity column.

ciggs
Starting Member

8 Posts

Posted - 2002-03-26 : 08:15:46

Cheers for that.. faster than the method I had.
However, I was trying to avoid having to list all 49 fields.
Any alternatives?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 08:33:05

Sure, redesign your table so that it doesn't have 50 columns!

Sorry, no shortcut. Turn the SELECT operation into a stored procedure that accepts an identity value:

CREATE PROCEDURE makeNewRow @id int AS
SELECT col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14,
col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27,
col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
col41, col42, col43, col44, col45, col46, col47, col48, col49, col50
FROM myTable
WHERE col1=@id

Then you can do this:

INSERT INTO myTable EXECUTE makeNewRow @id=10

Oooooops, the last line is not 100% correct, but I just had a better idea:

CREATE PROCEDURE makeNewRow @id int AS
INSERT INTO myTable
(col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14,
col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27,
col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
col41, col42, col43, col44, col45, col46, col47, col48, col49, col50)

SELECT col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14,
col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27,
col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
col41, col42, col43, col44, col45, col46, col47, col48, col49, col50
FROM myTable
WHERE col1=@id

Then all you need to do is EXECUTE makeNewRow @id=10.

Edited by - robvolk on 03/26/2002 08:44:14

ciggs
Starting Member

8 Posts

Posted - 2002-03-26 : 08:45:35

Hi,
I wish I could cut down the fields but its too late now (already in use), but the Stored Procedure appears to be a good way to go. I suppose I was just looking for the lazy way out of typing out 49 fields.
Thanks anyway,
Ciggs

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-26 : 09:07:47

quote:

Cheers for that.. faster than the method I had.
However, I was trying to avoid having to list all 49 fields.
Any alternatives?

Can you use Query Analyzer in SQL 2000 to script out an INSERT and a SELECT? That's what I always do.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

ciggs
Starting Member

8 Posts

Posted - 2002-03-26 : 09:32:27

Good idea - gets around all that typing.
Anyway, I think thats me sorted - thanks for all the help guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources