Re: How to duplicate a row in SQL Table using a single query
Yes, this is a an activity that I end up doing regularly. Typically
when I am writing a stored procedure and I want to get a working set
of data into a temporary table and have the temporary table use its
own identity key.
There is no straightforward way that I know of doing this but I use a
technique that Dan Guzman MVP gave me some time back which I think is
very neat.
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM MyBigTable
....or...
select * (where all columns are listed and have column names)
into #MetaData
from [my_bespoke_query_joining_several_tables]
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either your tempdb or your current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
-- More specific case where I want to prepare a create table statement
for a
-- temp table that will hold the result set from a stored procedure
because the
-- temporary table must exist before you can use a statement like...
-- INSERT #my_tbl
-- EXECmy_proc
SELECT case ordinal_position
when 1 then ' '
else ' ,'
end
+ quotename(column_name) collate SQL_Latin1_General_CP1_CI_AS +
' '
+ case data_type
when 'int' then data_type
when 'char' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'varchar' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'nvarchar' then 'varchar(' + convert(varchar(5),
character_maximum_length) + ')'
when 'decimal' then data_type + '(' + convert(varchar(5),
numeric_precision) + ',' + convert(varchar(5), numeric_scale) + ')'
when 'smallint' then 'int'
when 'datetime' then data_type
when 'smalldatetime' then 'datetime'
else 'datatype not handled'
end
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either the tempdb or the current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
In fact, if I am writing a stored proc that will later almost
certainly become an input to a further aggregated proc, I always put
in an additional parameter that allows the meta-data of the proc to be
returned as an additional result set.
I think this should give you a nice workaround that at worst means a
bit of cutting and pasting.
Regards
Liam
TheSqlGuy <TheSqlGuy.1dqkdq@mail.mcse.ms> wrote in message news:<TheSqlGuy.1dqkdq@mail.mcse.ms>...
> Guys, Guys, Guys,
>
> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
>
> I have the same question, but I think what the original author is
> trying to do here is duplicate the row while allowing the identity/key
> to grow on its own... without having to list every other column in the
> table. (Hence the * he is wanting to use). Furthermore, the author
> would like to accomplish this in a single SQL statement.
>
> Afterall, a primary key is a primary key, and a unique identifier is a
> unique identifer.
>
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer. And thus, I would expect such functionality
> from a seasoned language (aka, sql)
>
> With the previous assumption having been made, does anyone have the
> answer? Is there a way to duplicate everything in a row except for any
> autonumbering identifiers while allowing those autonumbers to
> autonumber as they were designed to (without listing every single other
> column in the table)?
Cell Phones reviews
10-01-04, 08:13 PM
Linear Mode
