SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61635 Visits: 19099
I have altered the question to say "easiest way" and I tend to agree with Hugo that a real table would have other constraints on it. However making a copy of a table isn't necessarily something that you worry about people adding data to. It would not have permissions and not likely be known to other people either.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4273 Visits: 3648
Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23283 Visits: 9730
I think the question was clear enough. But Hugo is right about the variations on the definition of "best".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 11639
GSquared (2/10/2009)
I think the question was clear enough. But Hugo is right about the variations on the definition of "best".


That is why I said this was a poor choice of question and answer. What is "best" is too suggestive and may not have a clear cut answer.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38951 Visits: 38508
I find if you don't read too much into these questions that they are usually fairly easy to answer. Unfortunately it seems people add a lot of contraints and requirements that aren't even stated.

It was a good question as it brings to the front identity function that some people may not even know about. The QotD is a learning experience, not about best practices or what should or shouldn't be done in a production environment.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 1992
OK, I learned about the IDENTITY function used with SELECT INTO.

But if I wanted to copy a table, except as a temporary hack, I'd still use 'Create Script..', edit, etc.

Derek
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10815 Visits: 11967
Lynn Pettis (2/10/2009)
I find if you don't read too much into these questions that they are usually fairly easy to answer.


Hi Lynnn,

I would usually agree. Not in this case, though. When I read the question and saw the answers, I immediately knew that two of the answers could have been correct.

Either the author of the question wrote this question to educate people about the existance of the IDENTITY() function, in which case SELECT INTO would be the expected answer. Or the author expected the readers to know about this and wrote the question to warn about some less published and less well-known side effects, in which case SELECT INTO would obviously have been wrong and CREATE TABLE ... INSERT SELECT would have been right.

Since there is no way to read the mind of the author of the question, I was faced with a 50/50 chance of getting my answer right. I took my chance ... and lost.

And then I took the opportunity to evangelize about those side effects anyway! BigGrin


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4273 Visits: 3648
I agree Lynn. Many times the discussion is as or more informative than the question. The point is to learn from the exchange.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61635 Visits: 19099
It's not that they add extra constraints, it's often authors are thinking of one thing. One small area they are trying to show and they don't have the same frame of reference that others do.

If you need a quick copy of a table, do you worry about constraints? Some people do, some always would script that, some would never think about it since there wouldn't be a security issue.

There's also a language issue. How do you try to get someone to think in a particular way. I could see why "best" was chosen, since for this person, it might eb the "best way" with the least amount of effort to do this. Might never have caused an issue.

It's good for people like Hugo to raise the debate. It's also good that the questions are here to get you to think about or learn about some new feature in SQL Server.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28721 Visits: 9671
Cliff Jones (2/10/2009)

Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.



Not since 6.5. One of those myths that keep hanging around.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search