Hi,
I have a table with about 50 fields including intCompanyId as the
table identity.
I want to be able to use one query (or a few in a stored proc to
duplicate a specific row in this table.
I do not want to list the fields in case some are added later.
Ex : insert into T_Company select * from T_Company where intCompanyId
= 17
This query gives me an error :
An explicit value for the identity column in table 'T_Company' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Even if I turn it OFF it will not let it insert it since it will have
the same intCompanyId as the other one.
Can some one help me?
Thanks
Alain> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
Why on earth would you want to _duplicate_ a row in a table? It makes no
sense to do that.
> I do not want to list the fields in case some are added later.
Good practice is to *always* list the columns, never use SELECT * in
production code. This should actually make the code easier to maintain and
debug if the DDL changes because your code will fail safe. INSERT ... SELECT
* is dangerous because it assumes a fixed sequential order to the columns
and that is something you shouldn't need to worry about and isn't always
easy to have complete control over.
--
David Portas
SQL Server MVP
--|||I am really curious to know why you want to do something like that. It's like
asking us how to mess up your table...
"Alain Filiatrault" wrote:
> Hi,
> I have a table with about 50 fields including intCompanyId as the
> table identity.
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
> I do not want to list the fields in case some are added later.
> Ex : insert into T_Company select * from T_Company where intCompanyId
> = 17
> This query gives me an error :
> An explicit value for the identity column in table 'T_Company' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Even if I turn it OFF it will not let it insert it since it will have
> the same intCompanyId as the other one.
> Can some one help me?
> Thanks
> Alain
>|||Alain,
Like Susan, I wonder why you want to do this. But there are two
things going on here. First, because there is an identity column, you
can't insert values into every column of the table, as insert into
T_Company select * ... would do. Because you say that you can't insert
even with IDENTITY_INSERT set to ON (I assume you meant "Even if I turn
it ON", not OFF), there must be a unique or primary key constraint on
the identity column. I would hope that constraint is in place for a
reason, and that constraint is preventing you from entering duplicate
information (in the identity column, at least) into the table. Your
desire to add a duplicate row to the table is at odds with the desire of
the database designer, who designed the database to prevent anyone from
doing what you want to do.
Steve Kass
Drew University
Alain Filiatrault wrote:
>Hi,
>I have a table with about 50 fields including intCompanyId as the
>table identity.
>I want to be able to use one query (or a few in a stored proc to
>duplicate a specific row in this table.
>I do not want to list the fields in case some are added later.
>Ex : insert into T_Company select * from T_Company where intCompanyId
>= 17
>This query gives me an error :
>An explicit value for the identity column in table 'T_Company' can
>only be specified when a column list is used and IDENTITY_INSERT is
>ON.
>Even if I turn it OFF it will not let it insert it since it will have
>the same intCompanyId as the other one.
>Can some one help me?
>Thanks
>Alain
>|||Guys, Guys, Guys,
It never ceases to amaze me how so many questions get answered wit
another question... instead of an answer.
I have the same question, but I think what the original author i
trying to do here is duplicate the row while allowing the identity/ke
to grow on its own... without having to list every other column in th
table. (Hence the * he is wanting to use). Furthermore, the autho
would like to accomplish this in a single SQL statement.
Afterall, a primary key is a primary key, and a unique identifier is
unique identifer.
It seems to me this would be a capability often persued by any seasone
and active SQL programmer. And thus, I would expect such functionalit
from a seasoned language (aka, sql)
With the previous assumption having been made, does anyone have th
answer? Is there a way to duplicate everything in a row except for an
autonumbering identifiers while allowing those autonumbers t
autonumber as they were designed to (without listing every single othe
column in the table)
-
TheSqlGu
----
Posted via http://www.mcse.m
----
View this thread: http://www.mcse.ms/message1119654.htm|||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)?|||> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
Answering a question directly isn't always the appropriate and professional
way to help someone who needs it. If someone asked you "Where can I get a
gun so I can shoot myself?" would you just answer the direct question or
would you offer some more constructive suggestions? The OP was asking how to
do something that will destroy the integrity of his data (if indeed it had
any to start with). My response was to ask for more information about his
actual business requirement so I could advise him better. Unfortunately
there are a lot of people posting to this group who are desperately trying
to shoot themselves in the foot. Not all of us want to help them to do it!
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer.
Not by a *good* SQL programmer!
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> If someone asked you "Where can I get a
> gun so I can shoot myself?" would you just answer the direct question or
> would you offer some more constructive suggestions?
Depends on the person.|||Also depends on what the original poster meant by "duplicate". I have
often had users ask me for funtionality to "duplicate" an order and
what they really mean is that they want the original order used as a
template to produce a new order - which is fair enough. Of course, it
would suggest that such functionality was an afterthought (which it
often is) and should have been factored into the original design.
An ability to appreciate the difference between the syntactic
precision of gun mechanics(parsed code) and the semantic ambiguity of
target shooting(meeting users requirements and expectations) is a very
useful skill.
Regards
Liam
"Mark Wilden" <mark@.mwilden.com> wrote in message news:<TfSdnZTmCJhXW_jcRVn-rw@.sti.net>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> > If someone asked you "Where can I get a
> > gun so I can shoot myself?" would you just answer the direct question or
> > would you offer some more constructive suggestions?
> Depends on the person.
