copy a record?

Hi,
I need to copy a record in a table that has an identity column. Plus,
I want to copy this record without using a column list in the SQL
statement.
So I searched the net, came up with an elegant solution:
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
however, when tried that I received:
Error 8101 An explicit value for the identity column in table can only
be specified when a column list is used and IDENTITY_INSERT is ON
So searched again and had the following answers:
"...Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
" and someone added:
"...
You need a column list for your INSERT statement:
INSERT t2 (
[id], [first], [org], [rest], [aux] ) SELECT
[ie], [first], [org], [rest], [aux] FROM t1
..."
BUT - I have to ask:
what if I want to insert without having to use a column list? if
i use a column list, then every time a column is added, modified or
deleted I need to change this store procedure too, which results in
doubling maintenance costs.
any ideas? how can I copy a record without using a named column
list?
Thanks very much for ANY idea,
Lior<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
Generate the SQL (including the column list) yourself at runtime
Liz
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?|||FWIW, anyone who submits SQL, or SPL that has INSERT/UPDATE statements that
do not have column lists, gets their code back, I won't even allow it on QA.
The reason is maintenance. If I add a column to a table, the code will
break, and then I have to dig it out of the system to do a hot-fix.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||WHY do you not want to do a column list' I can think if several reasons
why you SHOULD do one.
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||Thanks to all answers. the reason I DONT want a coloumn list is to
avoid having the code breaks when I add/modify/delete a column. I want
to minimize the explicit names of columns and to have something like
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
See? Here I have only one column name. therefore, less maintainence.
does it make sense? thanks for any ideas
thanks,
Lior
Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> WHY do you not want to do a column list' I can think if several reasons
> why you SHOULD do one.
w
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <liormessin...@.gmail.com> wrote in message
> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > Hi,
> > I need tocopy a recordin a table that has an identity column. Plus,
> > I want to copy this record without using a column list in the SQL
> > statement.
> > So I searched the net, came up with an elegant solution:
> > INSERT INTO <table>
> > SELECT *
> > FROM table WHERE (table.ID = @.objID)
> > however, when tried that I received:
> > Error 8101 An explicit value for the identity column in table can only
> > be specified when a column list is used and IDENTITY_INSERT is ON
> > So searched again and had the following answers:
> > "...Before your SQL Statement:
> > SET IDENTITY_INSERT <tablename> ON
> > " and someone added:
> > "...
> > You need a column list for your INSERT statement:
> > INSERT t2 (
> > [id], [first], [org], [rest], [aux] ) SELECT
> > [ie], [first], [org], [rest], [aux] FROM t1
> > ..."
> > BUT - I have to ask:
> > what if I want to insert without having to use a column list? if
> > i use a column list, then every time a column is added, modified or
> > deleted I need to change this store procedure too, which results in
> > doubling maintenance costs.
> > any ideas? how can Icopy a recordwithout using a named column
> > list?
> > Thanks very much for ANY idea,
> > Lior|||Actually, very interesting - here is a great thread I found about the
subject. My problem is exactly as Chris's
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/e59a1ef42bafeff9/23480e2a7fc6dc7a?lnk=gst&q=copy+a+record&rnum=2#23480e2a7fc6dc7a|||The real root problem here seems to be your developement process!! If you
aren't doing an impact analysis when you alter a table and making necessary
modifications to your code then that needs to be rectified, IMHO. BTW, you
can STILL make things work by simply using NULLable columns and/or defining
defaults for your tables, right?
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1189040443.208198.139120@.o80g2000hse.googlegroups.com...
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
>
>
>
>
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> WHY do you not want to do a column list' I can think if several reasons
>> why you SHOULD do one.
> w
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <liormessin...@.gmail.com> wrote in message
>> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
>> > Hi,
>> > I need tocopy a recordin a table that has an identity column. Plus,
>> > I want to copy this record without using a column list in the SQL
>> > statement.
>> > So I searched the net, came up with an elegant solution:
>> > INSERT INTO <table>
>> > SELECT *
>> > FROM table WHERE (table.ID = @.objID)
>> > however, when tried that I received:
>> > Error 8101 An explicit value for the identity column in table can only
>> > be specified when a column list is used and IDENTITY_INSERT is ON
>> > So searched again and had the following answers:
>> > "...Before your SQL Statement:
>> > SET IDENTITY_INSERT <tablename> ON
>> > " and someone added:
>> > "...
>> > You need a column list for your INSERT statement:
>> > INSERT t2 (
>> > [id], [first], [org], [rest], [aux] ) SELECT
>> > [ie], [first], [org], [rest], [aux] FROM t1
>> > ..."
>> > BUT - I have to ask:
>> > what if I want to insert without having to use a column list? if
>> > i use a column list, then every time a column is added, modified or
>> > deleted I need to change this store procedure too, which results in
>> > doubling maintenance costs.
>> > any ideas? how can Icopy a recordwithout using a named column
>> > list?
>> > Thanks very much for ANY idea,
>> > Lior
>|||On 4 Sep, 23:19, liormessin...@.gmail.com wrote:
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
Please do NOT multi-post. You have several replies in the
microsoft.public.sqlserver.programming newsgroup.
Posting independently to multiple groups wastes everyones time and
makes it harder for people to help you.
--
David Portas|||On Sep 6, 7:00 am, liormessin...@.gmail.com wrote:
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > WHY do you not want to do a column list' I can think if several reasons
> > why you SHOULD do one.
> w
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <liormessin...@.gmail.com> wrote in message
> >news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > > Hi,
> > > I need tocopy a recordin a table that has an identity column. Plus,
> > > I want to copy this record without using a column list in the SQL
> > > statement.
> > > So I searched the net, came up with an elegant solution:
> > > INSERT INTO <table>
> > > SELECT *
> > > FROM table WHERE (table.ID = @.objID)
> > > however, when tried that I received:
> > > Error 8101 An explicit value for the identity column in table can only
> > > be specified when a column list is used and IDENTITY_INSERT is ON
> > > So searched again and had the following answers:
> > > "...Before your SQL Statement:
> > > SET IDENTITY_INSERT <tablename> ON
> > > " and someone added:
> > > "...
> > > You need a column list for your INSERT statement:
> > > INSERT t2 (
> > > [id], [first], [org], [rest], [aux] ) SELECT
> > > [ie], [first], [org], [rest], [aux] FROM t1
> > > ..."
> > > BUT - I have to ask:
> > > what if I want to insert without having to use a column list? if
> > > i use a column list, then every time a column is added, modified or
> > > deleted I need to change this store procedure too, which results in
> > > doubling maintenance costs.
> > > any ideas? how can Icopy a recordwithout using a named column
> > > list?
> > > Thanks very much for ANY idea,
> > > Lior- Hide quoted text -
> - Show quoted text -
When you do not explicitly mention the column it can cause very hard
to find bugs where wrong columns get populated! Tust me on this
because it happened to me. Also, if you add/delete/modify a column,
your code will break when you do not have a column list.