By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,970 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,970 IT Pros & Developers. It's quick & easy.

Duplicate records and set field to constant

P: 3
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
Feb 14 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. insert into yourtable (col1, col2, col3, col4)
  2. (select 'ThisIsTheCopy' as col1, col2, col3, col4 from yourtable where thisfield = 'thisvalue')
  3.  
-- CK
Feb 14 '08 #2

P: 3
Yes. Try this:

Expand|Select|Wrap|Line Numbers
  1. insert into yourtable (col1, col2, col3, col4)
  2. (select 'ThisIsTheCopy' as col1, col2, col3, col4 from yourtable where thisfield = 'thisvalue')
  3.  
-- 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.
Feb 15 '08 #3

Delerna
Expert 100+
P: 1,134
unless I am missunderstanding you then you just need to modify
CK's example to something like this
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Table VALUES ( SELECT othervalue as col1,col2,col3 FROM Table WHERE col1 = somevalue)
  2.  
and you will have to specify the field names in the select because you want to change the value of one of them
Feb 15 '08 #4

P: 3
unless I am missunderstanding you then you just need to modify
CK's example to something like this
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Table VALUES ( SELECT othervalue as col1,col2,col3 FROM Table WHERE col1 = somevalue)
  2.  
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.
Feb 15 '08 #5

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 15 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.