Solved

SQL SERVER - COPY ONE ROW WITH PRIMARY KEY

Posted on 2012-03-13
6
207 Views
Last Modified: 2012-03-13
Hi,
We have a table with hundreds columns, so it would be a bit heavy to name each, and we need a stored procedure that copies one row. We have a primary key autonum, so :
insert into myTable select * from myTable does not work.
Any (simple) idea ?
Thanks for help
0
Comment
Question by:javilmer
6 Comments
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37714454
Are you wanting any record or a specific record?

Something like:

insert into myTable
select  Top 1 * from myTable
0
 
LVL 15

Accepted Solution

by:
Anuj earned 500 total points
ID: 37714462
You cannot use '*' in your insert queries if you have identity columns in the destination table,
a tricky way to get all non identity columns is by using the result of the following query.

SELECT STUFF((SELECT ', '+name
FROM sys.columns
WHERE is_identity = 0
AND object_id = OBJECT_ID('YourTable')
FOR XML PATH('')),1,1,'')
0
 

Author Comment

by:javilmer
ID: 37714650
anujnb : that's sounds interesting, how can i do the insert though ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Anuj
ID: 37714712
Execute the above query, this will gives a list of columns separated by comma, copy the results and paste into your Stored procedure, this is just a tricky way of doing the things you can do the same using dynamic sql but its not recommended.
0
 

Author Comment

by:javilmer
ID: 37714867
I can't do it manually, if not i would copy/paste in visual studio, I need a stored proc because the application needs a "copy function" :)
0
 
LVL 25

Expert Comment

by:jogos
ID: 37714959
With a identity-column you mustalways specify the columnlist

insert into mytable (col1,col2 ...;)
select col1, col2
from ....

If you also want to insert the identity-column itself you must SET IDENTITIY_INSERT
http://msdn.microsoft.com/en-us/library/ms188059.aspx

And I would advise always include the column-list because for future changes in source or destination your sql could become invallid.

Notice that when in SSMS you select your table and click right, you can choose for that table to generate a insert statement for you .... no typing for the columnlist only copy/paste
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
Sql Server group by 10 29
Return 0 on SQL count 24 30
ms sql + get number in list out of total 7 29
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

810 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