Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 7:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:15 AM
Points: 32,779, Visits: 14,938
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
Post #653726
Posted Tuesday, February 10, 2009 8:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 3,766, Visits: 3,584

Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.
Post #653788
Posted Tuesday, February 10, 2009 8:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #653799
Posted Tuesday, February 10, 2009 8:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 3,089, Visits: 7,745
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.
Post #653810
Posted Tuesday, February 10, 2009 8:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 22,492, Visits: 30,187
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.




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)
Post #653813
Posted Tuesday, February 10, 2009 8:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #653820
Posted Tuesday, February 10, 2009 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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! :D



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #653833
Posted Tuesday, February 10, 2009 8:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 3,766, Visits: 3,584
I agree Lynn. Many times the discussion is as or more informative than the question. The point is to learn from the exchange.
Post #653840
Posted Tuesday, February 10, 2009 9:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:15 AM
Points: 32,779, Visits: 14,938
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
Post #653855
Posted Tuesday, February 10, 2009 9:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #653860
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse