We help IT Professionals succeed at work.

How to copy row into same table - MS SQL

1,363 Views
Last Modified: 2012-05-07
I have a datatable with around 50 columns, and I need a way to copy a row.
But before the insert is runned I need to change some column values.

I have Tabel: Profile
Which have columns: system_id, name + 50 other.

Lets say I have rows:
system_id  name + 50
      52        test
      57        test2

I want to copy row with system_id 52 into the same table, but change the system_id and name first.
I have variables containing the new values that will replace the old ones, @system_id and @name. But I'm unsure on how to do this.

Do I have to create a temp table containing all the 50 columns, or is there an easier way to do this?

If you need more clarification please ask.

Thanks in advanced:)

Regards
Marius
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Perfect, just what I was looking for, thanks :)

Marius
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
>>Perfect, just what I was looking for, thanks :)<<

creating temp table and dropping it, won't it create overhead on database?

Author

Commented:
I'm pretty newbie regading DB's so I dont know what overhead means.
It will take so much resources and slow down performance but in my way, it is easy, readable and more efficient.

Author

Commented:
Okay, thanks for you answer aswell then :)
I expected split points, anyway, that's ok.

Author

Commented:
Sorry but the points were given away before I saw your posting.
if you wish than you can change it by clicking on "Request Attention" button, moderator will open question and you can re-assign points.

Commented:
it would be much faster (in developer view) to type
select * into #temp
instead of typing the 50+ column name (correctly)

and it reduce the maintenance need for table structure change.
(if table structure change, the select * into #temp from Profile don't need to change, via the 50+ column name need to add the affected column name)

as for the production impact,
i agree that is not the fastest way to run,
but it don't really affect so much, accounting for the ease of program maintenance .
>>but it don't really affect so much, accounting for the ease of program maintenance .<<

programmer needs to make program just once, but user needs to use it on everyday basis, so it is better to keep that in mind and have to create or develop something which is really faster which can save so many resources of system and man hours.

Author

Commented:
A colleague of mine showed me a very good way to do this:
declare @cols nvarchar(max), @sql nvarchar(max)
select @cols=null
select @cols=coalesce(@cols + ',', '') + name
from sys.columns where object_id= (select object_id from sys.tables where name='profile')
AND name not in ('SYSTEM_ID', 'DOCNAME')
set @sql=N'insert into docsadm.profile (SYSTEM_ID, DOCNAME, ' + @cols + ')
                  SELECT ' + STR(@SYSTEM_ID) + ', ''MY DOCNAME'', ' + @COLS + ' FROM PROFILE
                  WHERE SYSTEM_ID=16'
EXEC SP_EXECUTESQL @SQL

:) yes, it is also one of the good way to go for, it is partially based on my concept but didn't use temp table so it will have good performance also. Even if you will compare this with my way, my query will run faster for sure, you can compare execution plan. However this is better approach than temp table.


CERTIFIED EXPERT
Top Expert 2012

Commented:
alphaau,

>>instead of typing the 50+ column name (correctly)<<
Why would you do that?  Surely you know that there is no need to type all the column names.  All you have to do is drag the Columns object for the table in question and you have all your comma delimited names.

>>if table structure change, the select * into #temp from Profile don't need to change<<
Please don't encourage the use of SELECT *.  Except for adhoc queries it is a very bad idea.  And no we will not get in to a debate here about it, just do a search on Google and find out why.

>>but it don't really affect so much, accounting for the ease of program maintenance .<<
I see.  So you believe that applications are written solely for the ease of the developer?  Interesting concept.
good points Acperkins, I wanted to convey the same but you know that I am not as good as you in words. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.