Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to copy row into same table - MS SQL

Posted on 2009-07-08
17
Medium Priority
?
805 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 7

Accepted Solution

by:
Alpha Au earned 1000 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 1000 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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