Solved

How to copy row into same table - MS SQL

Posted on 2009-07-08
17
786 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:
alphaau 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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:alphaau
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now