Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2002-09-24 : 14:50:33
|
| Hi-What is the best way to go about copying a specific row in a table? I need all of the fields to be the same with the exception of the identity field which SQL Server should handle on its own.Thanks! |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-09-24 : 17:21:25
|
| insert mytable (col2, col3, col4, col5)select col2, col3, col4, col5 from mytable where col1 = 123 -- col1 is your identity fieldhth,JustinHave you hugged your SQL Server today? |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-25 : 07:34:54
|
quote:
What is the best way to go about copying a specific row in a table? I need all of the fields to be the same with the exception of the identity field which SQL Server should handle on its own.
This request suggests some serious design issues. The surrogate key (i.e. IDENTITY column) vs. natural key debate has been played, but one thing is certain. If a surrogate key is used on the physical layer, there should exist a naturally occuring candidate key on the table as well. Duplicating a row like this suggests the lack of a natural key ...Jay White{0} |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-09-25 : 10:48:13
|
Well, I would aregue that IDENTITY column does act as a natural key. The problem arose in a table that stores customer orders. I use the IDENTITY column as the Order Number that they customer refers to. The need to copy a row arose when certain items got placed on back order. For billing purposes any items shipped outside the original shipment needed to be given a new order number. All of the existing order data (name, address, shipping method) was the same, but a new order number needed to be generated. Thus the need to copy the row and not the IDENTITY column.quote:
quote:
What is the best way to go about copying a specific row in a table? I need all of the fields to be the same with the exception of the identity field which SQL Server should handle on its own.
This request suggests some serious design issues. The surrogate key (i.e. IDENTITY column) vs. natural key debate has been played, but one thing is certain. If a surrogate key is used on the physical layer, there should exist a naturally occuring candidate key on the table as well. Duplicating a row like this suggests the lack of a natural key ...Jay White{0}
|
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-25 : 13:03:02
|
quote:
A surrogate key is an artificially produced value, most often a system-managed, incrementing counter whose values can range from 1 to n, where n represents a table's maximum number of rows. In SQL Server, you create a surrogate key by assigning an identity property to a column that has a number data type. A natural key is a naturally occurring descriptor of the data and one of a table's attributes that has no duplicate values.-SQL Server Mag: Surrogate Key vs. Natural Key Feb 2002, Poolet
By definition, a system generated code/number is not naturally occuring. Your [Order Number] is not a Natural Key...quote:
In general, if you cannot find an obvious natural key, reconsider your design -- chances are there's something wrong with it.-Fabian Pascal
Jay White{0} |
|
|
|
|
|
|
|
|
Copy Row