Hi
I want to create a copy of an existing record in the same table, except with
a different primary key value of course.
This is pretty easy if you specify each in turn like:
INSERT INTO TableA
(
Col1
Col2
...
)
SELECT Col1, Col2, ...
FROM TableA
WHERE PK = @.RecordtoCopy
However, in my situation the database structure is evolving. Therefore, if
the fields in the TableA, above, are changed then the associated SQL script
is no longer valid.
Is there some way of doing the equivalent of:
INSERT INTO TableA
SELECT * FROM TableA
WHERE PK = @.RecordtoCopy
but leaving out the primary key field, which is an identity field.
That is, I don't want to have to specify the column names each time. I
guess I could use 'sycolumns' to construct a query or is there an easier way
?
What I've shown here is a very simplistic query. Mine are much more
complex, and I want to copy all fields from several records in tables formin
g
an hierarchical structure.
Cheers
TimHi Tim
I dont imagine a situation where you need to keep duplicate copy in the same
table.
Can you explain why you want to do that so to as provide some thought on tha
t.
Regards
R.D
"Tim M" wrote:
> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||This is for a cost estimating system where you want to create a new version
of an existing estimate. This would happen when you want to be on a new job
,
which is very similar to one that was previously costed and you want to use
the old one as a template for a new one.
I think I've solved it anyway. The code below is a function that creates an
insert clause as a string.
CREATE FUNCTION dbo.fn_CreateInsert (@.@.Table varchar(50), @.@.PKey
varchar(20)) RETURNS varchar(1000) AS
BEGIN
DECLARE @.InsertClause varchar(1000),
@.@.TableName varchar(255),
@.ColName varchar(255),
@.ColsCursor CURSOR
SET @.ColsCursor = CURSOR FAST_FORWARD
FOR
SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
INNER JOIN (sysobjects INNER JOIN sysusers ON sysobjects.uid =
sysusers.uid)
ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = @.@.Table
OPEN @.ColsCursor
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName, @.ColName
SET @.InsertClause = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.ColName NOT IN (@.@.PKey, 'DateCreated', 'DateModified')
BEGIN
IF @.InsertClause = ''
SET @.InsertClause = '(' + @.ColName
ELSE
SET @.InsertClause = @.InsertClause + ', ' + @.ColName
END
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName,@.ColName
END
CLOSE @.ColsCursor
DEALLOCATE @.ColsCursor
SET @.InsertClause = 'INSERT INTO ' + @.@.Table + ' ' + @.InsertClause + ')'
RETURN @.InsertClause
END
GO
"R.D" wrote:
> Hi Tim
> I dont imagine a situation where you need to keep duplicate copy in the sa
me
> table.
> Can you explain why you want to do that so to as provide some thought on t
hat.
> Regards
> R.D
> "Tim M" wrote:
>|||Tim, try,
-- DDL & sample data
create table dbo.t1
(
keycol int not null identity primary key,
col1 int,
col2 int,
col3 int
);
insert into dbo.t1 values(1, 2, 3);
go
-- usp_copyt1row proc
create proc usp_copyt1row
@.keyval as int
as
declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
set @.col_list = N'';
select @.col_list = @.col_list + ',[' + column_name + N']'
from information_schema.columns
where table_schema = N'dbo'
and table_name = N't1'
and column_name <> N'keycol';
set @.col_list = right(@.col_list, len(@.col_list) - 1);
set @.sql =
N'insert into dbo.t1('
+ @.col_list + N') select '
+ @.col_list + N' from dbo.t1 where keycol = @.keyval'
exec sp_executesql
@.sql, N'@.keyval as int', @.keyval;
go
-- test
exec usp_copyt1row 1;
exec usp_copyt1row 2;
select * from dbo.t1;
-- Output
keycol col1 col2 col3
-- -- -- --
1 1 2 3
2 1 2 3
3 1 2 3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Tim M" wrote:
> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||Thanks for that.
"Itzik Ben-Gan" wrote:
> Tim, try,
> -- DDL & sample data
> create table dbo.t1
> (
> keycol int not null identity primary key,
> col1 int,
> col2 int,
> col3 int
> );
> insert into dbo.t1 values(1, 2, 3);
> go
> -- usp_copyt1row proc
> create proc usp_copyt1row
> @.keyval as int
> as
> declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
> set @.col_list = N'';
> select @.col_list = @.col_list + ',[' + column_name + N']'
> from information_schema.columns
> where table_schema = N'dbo'
> and table_name = N't1'
> and column_name <> N'keycol';
> set @.col_list = right(@.col_list, len(@.col_list) - 1);
> set @.sql =
> N'insert into dbo.t1('
> + @.col_list + N') select '
> + @.col_list + N' from dbo.t1 where keycol = @.keyval'
> exec sp_executesql
> @.sql, N'@.keyval as int', @.keyval;
> go
> -- test
> exec usp_copyt1row 1;
> exec usp_copyt1row 2;
> select * from dbo.t1;
> -- Output
> keycol col1 col2 col3
> -- -- -- --
> 1 1 2 3
> 2 1 2 3
> 3 1 2 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim M" wrote:
>
