1

I am using SQL Server 2008.

I need to clone a record from a master table and all related child tables, there are about 10.

Currently I believe the approach would be to write all the SELECT and INSERT T-SQL statements in a Stored Procedure. Obviously this will take a little time as there are 10 tables, and some of the tables have quite a few columns. Is there another approach which would be quicker to implement ie some form of "cascade copy".

Thanks.

EDIT:

It seems I cannot do:

insert into Table1
select *
from Table1 
where Id = 1082

I get:

An explicit value for the identity column in table 'Table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
SamJolly
  • 6,347
  • 13
  • 59
  • 125
  • 4
    select * into from – knkarthick24 Nov 06 '14 at 12:41
  • ok, yes :). Also I am now researching http://stackoverflow.com/questions/6727850/cascade-copy-of-rows-in-sql – SamJolly Nov 06 '14 at 12:42
  • Actually I am inserting another record into the same . But my question is really whether I need to then explicitly write "insert" statements to do all the cloning for all of the child records from the child tables. I appreciate I can use Select * in the Insert statements, but I still need to write an Insert statement for each child table, or am I missing something? Thanks. – SamJolly Nov 06 '14 at 13:08
  • 2
    Your current belief would be the correct method. Don't try and cut corners, it's only a bit of typing ;) Remember that SSMS can generate certain scripts for you (right-click, script table as, insert to) which might help! – gvee Nov 06 '14 at 13:28
  • 1
    @gvee, Appreciate the comment :) Just checking my belief..... – SamJolly Nov 06 '14 at 13:34

1 Answers1

2

use SET IDENTITY_INSERT X ON & just keep this in mind * It is must to mention the column list after the table name

try your insert statement like this (mention column name) --

insert into Table1 (id,a,b,b)
select id,a,b,c
from Table1
Naveen Kumar
  • 1,541
  • 10
  • 12
  • Thanks for this. Actually found that "SET IDENTITY_INSERT X OFF" is needed in my case where I would need to create a fresh Id which is the Identity column – SamJolly Nov 06 '14 at 13:35