Duplicate records and set field to constant

[ishimon]

I have a table on SQL 2000.

I need to duplicate a group of records into the same table but duplicates need to have one field set to constant.

Is it possible to modify Column value in the subselect?

Thanks

[ck9663]

I have a table on SQL 2000.

I need to duplicate a group of records into the same table but duplicates need to have one field set to constant.

Is it possible to modify Column value in the subselect?

Thanks

Yes. Try this:

insert into yourtable (col1, col2, col3, col4)
(select 'ThisIsTheCopy' as col1, col2, col3, col4 from yourtable where thisfield = 'thisvalue')
 

-- CK

[ishimon]

Yes. Try this:

insert into yourtable (col1, col2, col3, col4)
(select 'ThisIsTheCopy' as col1, col2, col3, col4 from yourtable where thisfield = 'thisvalue')
 

-- CK

I am sorry - was in a rush earlier. Here is better explanation of the problem:
Table has: col1, col2, col3 - for example could be State, County, City plus other columns.
I need to insert duplicates of some group of records but to change column to a different value.

I know this will insert duplicates for col1 = somevalue

INSERT INTO Table VALUES ( SELECT * FROM Table WHERE col1 = somevalue)

How can I change the col1 into "othervalue" just for duplicate records not the originals?
Is it possible to do it on the fly?

Also, prefer not to use column names in SELECT because column names will vary in different tables but I do know column name to use in WHERE clause and that the column that needs to be changed.

Thanks.

[Delerna]

unless I am missunderstanding you then you just need to modify
CK's example to something like this

INSERT INTO Table VALUES ( SELECT othervalue as col1,col2,col3 FROM Table WHERE col1 = somevalue)
 

and you will have to specify the field names in the select because you want to change the value of one of them

[ishimon]

unless I am missunderstanding you then you just need to modify
CK's example to something like this

INSERT INTO Table VALUES ( SELECT othervalue as col1,col2,col3 FROM Table WHERE col1 = somevalue)
 

and you will have to specify the field names in the select because you want to change the value of one of them

Thanks for clarifying. I was hoping to avoid naming fields but looks like there is no other way.

[ck9663]

If you really want to grab the field names dynamically, you can still do it. It will take a little bit of coding, but you can.

Try running this on your sql analyzer:

select * from syscolumns where id (select id from sysobjects where name = 'YourTableNameHere')

It will return the column names of your table. You now need to create a cursor to fetch all those records to build a dynamic INSERT query and execute it.

Happy coding.

-- CK