How do I create a copy of a record in a table? ( identity is used on PK )

How do I create a copy of a record in a table? ( identity is used on PK )

• Manena

  An insert select is correct, but I was wondering if there were another way.
  I really hate to do insert into ( aa,bb,cc,dd.. ) values (select aa,bb,cc,dd,.. FROM x WHERE pk=zz)
  
  What is was hoping for was something like a for "loop from column 2 to end" for both records ( the new one initially with default values), or something entirely different.
  
  It might sound a bit wierd, it is also more out of a curiousity. I might as well do it in the dataset and use the insert procedure.
  
  Regards
  Old LR.

  
  

• Scottaroberts99

  Hi.

  To copy record with IDENTITY field:

  Use STORED PROC SP_COPY(TABLENAME). This proc will automaticly distinguish IDENTITY FIELD and return @@IDENTITY of new inserted record

  USE [YOUR_DATABASE]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  

  CREATE Function [dbo].[udf_GetColumnsInTable] (@var_tablename varchar(50))
  returns @results3 TABLE
  (
  ColumnName varchar(50),
  DataType varchar(128),
  DataLen varchar(10),
  VarLen bit,
  IsIdentity bit
  )
  AS

  begin
  INSERT INTO @results3
  SELECT syscolumns.name, datatype.name, syscolumns.length, datatype.variable, syscolumns.colstat
  FROM syscolumns JOIN master.dbo.systypes datatype
  ON syscolumns.xtype = datatype.xtype
  WHERE id in(select id FROM sysobjects where name = @var_tablename) AND NOT datatype.name = 'sysname'
  RETURN
  end

  USE [YOUR_DB]
  GO
  

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO

  CREATE PROCEDURE [dbo].[sp_copy]
  @tablename varchar(254),
  @id int
  AS
  begin
  SET NOCOUNT ON;

  DECLARE @sqlstr nvarchar(2048)
  DECLARE @var_Columnstr nvarchar(2048)

  DECLARE @ColumnName varchar(30)
  DECLARE @temp varchar(30)
  DECLARE @DataType varchar(128)
  DECLARE @DataLen varchar(10)
  DECLARE @VarLen bit
  DECLARE @IsIdentity bit
  DECLARE @IdField varchar(50)
  
  set @var_columnStr = '';
  set @idField = '';
  DECLARE F_Cur CURSOR FOR
  SELECT *
  FROM udf_GetColumnsInTable(@tableName)
  OPEN F_Cur;
  FETCH NEXT FROM F_Cur
  INTO @ColumnName, @DataType, @DataLen, @VarLen, @IsIdentity
  set @temp = @ColumnName;
  if @isIdentity = 0
  begin
  set @var_columnStr = @temp + ','
  end else
  begin
  set @idField = @temp;
  end;
  WHILE @@FETCH_STATUS = 0
  BEGIN
  FETCH NEXT FROM F_Cur
  INTO @ColumnName, @DataType, @DataLen, @VarLen, @IsIdentity;
  set @temp = @ColumnName;
  if (@@FETCH_STATUS = 0)
  begin
  if @isIdentity = 0
  begin
  set @var_columnStr = @var_columnStr + @temp
  set @var_columnStr = @var_columnStr + ','
  end else
  begin
  set @idField = @ColumnName;
  end;
  end;
  end;
  CLOSE F_Cur;
  DEALLOCATE F_Cur;
  set @var_columnStr = @var_columnStr
  set @var_columnStr =
  stuff(@var_columnStr,len(@var_columnSTr),1,' ')
  SET @sqlstr = 'insert INTO ' + @TableName + '(' +
  @var_ColumnStr + ') select ' +
  @var_ColumnStr + ' from ' + @tableName + ' where ' + @idField + '=' + Str(@id);
  exec sp_executesql @sqlstr;
  return @@IDENTITY

  end
  

  
  
  

• JasonReis

  I don't quite understand your question. But can't you do this with insert...select statement. You need to however reference the required columns in the insert column list and the select list. You could do this dynamically but there are permissions and security issues with that approach that you should be aware of.
  
  

• Tom_In_Dallas

  Hi Konstantin,
  Thanks for your posting. In the meantime, I found that this SP fails when the number of column grows, since 2048 characters is not enough to hold the string. It also didn't work with SQL-reserved column names and names with spaces, which has been fixed by adding brackets to the name. Also the sp now doesn't return a null in case of a failed copy. See below.
  
  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[sp_copy]
  @tablename varchar(254),
  @id int
  AS
  begin
  SET NOCOUNT ON;
  --- Modified by to allow problematic column names, e.g. w./spaces
  --- Modified to allow long SQL strings
  --- Modified to allow maximum-length column names
  --- Modified to avoid returning a NULL value in case of failed copy
  
  DECLARE @sqlstr nvarchar(max) -- Should never run out of string space
  DECLARE @var_Columnstr nvarchar(max)
  
  DECLARE @ColumnName varchar(128) -- Max. according to BOL
  DECLARE @temp varchar(30)
  DECLARE @DataType varchar(128)
  DECLARE @DataLen varchar(10)
  DECLARE @VarLen bit
  DECLARE @IsIdentity bit
  DECLARE @IdField varchar(50)
  
  set @var_columnStr = '';
  set @idField = '';
  DECLARE F_Cur CURSOR FOR
  SELECT *
  FROM udf_GetColumnsInTable(@tableName)
  OPEN F_Cur;
  FETCH NEXT FROM F_Cur
  INTO @ColumnName, @DataType, @DataLen, @VarLen, @IsIdentity
  set @temp = @ColumnName;
  if @isIdentity = 0
  begin
  set @var_columnStr = '[' + @temp + ']' + ','
  end else
  begin
  set @idField = @temp;
  end;
  WHILE @@FETCH_STATUS = 0
  BEGIN
  FETCH NEXT FROM F_Cur
  INTO @ColumnName, @DataType, @DataLen, @VarLen, @IsIdentity;
  set @temp = @ColumnName;
  if (@@FETCH_STATUS = 0)
  begin
  if @isIdentity = 0
  begin
  set @var_columnStr = @var_columnStr + '[' + @temp + ']'
  set @var_columnStr = @var_columnStr + ','
  end
  else
  begin
  set @idField = @ColumnName;
  end;
  end;
  end;
  CLOSE F_Cur;
  DEALLOCATE F_Cur;
  set @var_columnStr = @var_columnStr
  set @var_columnStr =
  stuff(@var_columnStr,len(@var_columnSTr),1,' ')
  SET @sqlstr = 'insert INTO ' + @TableName + '(' +
  @var_ColumnStr + ') select ' +
  @var_ColumnStr + ' from ' + @tableName + ' where ' + @idField + '=' + Str(@id);
  EXEC sp_executesql @sqlstr;
  IF @@identity IS NULL
  BEGIN
  RETURN 0
  END
  ELSE
  BEGIN
  RETURN @@IDENTITY
  END
  end
  
  
  
• How do I create a copy of a record in a table? ( identity is used on PK )