Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35871 Visits: 18715
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: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Cliff Jones
Cliff Jones
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: 4069 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14363 Visits: 9729
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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2666 Visits: 11584
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8273 Visits: 11536
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.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: 4069 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-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35871 Visits: 18715
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: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20919 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