Wednesday, March 21, 2012

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

No comments:

Post a Comment