I have a row that I want to duplicate and change one field in the process
Example
DATE Shift Sample C1 C2 C3 C
4/1/04 1 A 0 0 0

I need a statement to copy the above to a new row with the sample being 'B' so I will end up with
DATE Shift Sample C1 C2 C3 C
4/1/04 1 A 0 0 0
4/1/04 1 B 0 0 0

Is there any easy way to do it when I have aprox 50 Columns without having to add all thoses column names

Is it possible to do something lik
INSERT into table_name select 'B' as shift, * from table_nam
where sample='A

Also is there a select statement that can select all except one col? lik

[quoted text, click to view]
having to add all thoses column names? <<

No. Name another programmign language that has such a thing. Then be
glad; this preserves data integrity whenteh table is altered.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Create a Stored procedre to do this operation
if the sample is a predefined one or data from a table open a table variable or cursor and run a while loo
till the end of table variable / cursor. and insert in to your table witrh the value from the table variable or Cursor. if you want me to write the code mail me jeswanthj@netscape.ne
regard
Jeswanth Jaishanke
Bangalor

----- Martin Fletcher wrote: ----

I have a row that I want to duplicate and change one field in the process
Example
DATE Shift Sample C1 C2 C3 C
4/1/04 1 A 0 0 0

I need a statement to copy the above to a new row with the sample being 'B' so I will end up with
DATE Shift Sample C1 C2 C3 C
4/1/04 1 A 0 0 0
4/1/04 1 B 0 0 0

Hi,

INSERT into table_name select date,shift,'B',C1,C2,C3,C4 from table_name
where sample='A'

This will work fine if the Sample column will not duplicate.

Thanks
Hari
MCDBA

[quoted text, click to view]

Hi.

Is it possible to do something like INSERT into table_name select 'B' as
shift, * from table_name where sample='A'

Ans: You can do like the below (Remove the alias name shift)

INSERT into table_name(Shift,date,col1,col2..coln) select 'B'
,Shift,date,col1,col2..coln from table_name where sample='A'

Also is there a select statement that can select all except one col? like
SELECT * EXCEPT shift FROM table_name

Ans: There is no clause like EXCEPT, but you do that using column list

insert into table(date,col1,col2,col3...coln) select
date,col1,col2,col3..coln from table

Ignore shift column from Insert and Select.

Thanks
Hari
MCDBA






[quoted text, click to view]

Yes, you can provide a * in the SELECT list provided the number of columns
do match. For selecting all except one column, there is no direct way to do
it. You might want to experiment with dynamic SQL though.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

[quoted text, click to view]