Solved

How to copy row into same table - MS SQL

Posted on 2009-07-08
17
789 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
0
Comment
Question by:Contestoas
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 7

Accepted Solution

by:
Alpha Au earned 250 total points
ID: 24801740
the easiest way i can think of is :
select * into #temp from Profile where system_id = 52
 
update #temp 
set system_id = 'user_value'
, field1 = value 1
, ...
, ...
 
insert into Profile
    select * from #temp
 
drop table #temp

Open in new window

0
 

Author Comment

by:Contestoas
ID: 24801810
Perfect, just what I was looking for, thanks :)

Marius
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 total points
ID: 24801827
what about this?

 declare @system_id  int
 declare @name varchar(50)
 
 set @system_id=60
 set @name='Ritesh'
 

insert into table1 (system_id,name,50+ columns)
select @system_id,@name, 50+ columns from table1 where system_id=52
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24801832
>>Perfect, just what I was looking for, thanks :)<<

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

0
 

Author Comment

by:Contestoas
ID: 24801988
I'm pretty newbie regading DB's so I dont know what overhead means.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802065
It will take so much resources and slow down performance but in my way, it is easy, readable and more efficient.
0
 

Author Comment

by:Contestoas
ID: 24802106
Okay, thanks for you answer aswell then :)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802115
I expected split points, anyway, that's ok.
0
 

Author Comment

by:Contestoas
ID: 24802146
Sorry but the points were given away before I saw your posting.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802205
if you wish than you can change it by clicking on "Request Attention" button, moderator will open question and you can re-assign points.
0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24802316
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 .
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802325
>>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.
0
 

Author Comment

by:Contestoas
ID: 24802439
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

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802456
:) 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.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24810592
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.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24810949
good points Acperkins, I wanted to convey the same but you know that I am not as good as you in words. :)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question