Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

SQL SERVER - COPY ONE ROW WITH PRIMARY KEY

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
javilmer
Asked:
javilmer
1 Solution
 
macarrillo1Commented:
Are you wanting any record or a specific record?

Something like:

insert into myTable
select  Top 1 * from myTable
0
 
AnujSQL Server DBACommented:
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
 
javilmerAuthor Commented:
anujnb : that's sounds interesting, how can i do the insert though ?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
AnujSQL Server DBACommented:
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
 
javilmerAuthor Commented:
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
 
jogosCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now