How do I create a copy of a record in a table? ( identity is used on PK )
Hi.
I have a problem. I am trying to copy a record in a stored procedure. There is a primary key ( id, indentity ) and the copy should be an exact copy of the row except for the PK, a field called SerialNo and a field called createtime having a default time of (getdate())
I would like to avoid copying the entire row manually since the table contains a lot of fields ( test parameters and limits of these ) and is not completely defined ( design phase ).
Regards
/Landrover ( old one )
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.
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.
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
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
