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


T-SQL


T-SQL

Author
Message
Danny Ocean
Danny Ocean
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 1549
Comments posted to this topic are about the item T-SQL

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8870 Visits: 11739
I don't think the given "correct" answer is in fact "the best way", as was asked. I even doubt if it takes the least effort, as the answer key says.

If table XYZ is in fact a real table from a real database, and not just a throwaway table created for the purpose of doing a quick test, then there will be constraints on the table. There will be at least one PRIMARY KEY or UNIQUE constraint, probably one or two CHECK constraints, maybe a FOREIGN KEY constraint, possible a DEFAULT constraint or two, and of course NOT NULL constraints for most, if not all, columns. Of these constraints, only the NOT NULL constraints will be copied when you use SELECT INTO with an IDENTITY() function. There also will not be a UNIQUE or PRIMARY KEY constraint on the added identity column.

So if you use a SELECT INTO with an IDENTITY() column, you'll next have to run a series of ALTER TABLE statements to add back all these constraints. Each of these (except the DEFAULT constraints) requires a table scan to check if the existing doesn't violate the constraint. And unless you have the system in single user mode, other users might be able to sneak in some bad data before you have the last constraint added. Oops. With an explicit CREATE TABLE, you can add all the constraints before copying the data; the constraints will be checked while the data is copied (with no extra scans required), and other users have no chance of bypassing them. So this is definitely the better way.

It is also more efficient. I can use SSMS to generate a CREATE TABLE script for the existing table with all the constraints, use find and replace to change the table name, and then manually add the IDENTITY column, change the current PRIMARY KEY to UNIQUE (if required) and add a PRIMARYY KEY or UNIQUE constraint on the new column, and that's all I need to do. With the INSERT INTO method, I have to manually key in all the ALTER TABLE statements for the constraints, or generate them one by one (if that is even possible). So the CREATE TABLE / INSERT SELECT method required less typing, not more, making it more efficient for me. Since less scans of the data are needed (see above), it is also more efficient for SQL Server.

Bottom line - INSERT INTO is a nice quick method to get a copy of the data only in a temp table. For making a real copy of the table (presumably as a first step that will be followed by dropping the original and renaming the copy - for why else would I want a copy of the table instead of a view?), INSERT INTO falls terribly short.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Rachel Byford
Rachel Byford
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: 1630 Visits: 958
I completely agree with Hugo. You said the "best" way, not the way with the least effort (and potentially not the best result). Besides which if you use Query Analyzer (or whatever you people who've moved off sql2000 have) to script the table creation from the original table - which will also script all of the constraints - then the first way is very little effort whatsoever.
Tao Klerks
Tao Klerks
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1435 Visits: 1249
I agree with the comments above, but on the other hand I understood why the question's "Right" answer had to be the Identity function - I had never heard of it and it is a nice simple solution to a problem that I have encountered many times, needing to create a temp table with an Identity column explicitly instead of simply using Select Into like I usually do when I (really) need a temp table.

The question could do with a little rewording / putting into context, but I learned something new and very interesting today Smile

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Philipp Post
Philipp Post
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 634
I go in line with Hugo's oppinion.

I would assume if anyone choose to use IDENTITY he/she wishes to use this for new rows as well after copying the table, but the question did not give sufficient information on the requirements.

brgds

Philipp Post

brgds

Philipp Post
Danny Ocean
Danny Ocean
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 1549
Hi all,


First thank to All for your great response. i Just want to share sometime that i knew. Actually lots of time, i need a copy of data with identity column. And that time i use two statements (SELECT INTO, ALTER TABLE). Then i got IDENTITY Function.

So i post it to help other.

Hugo, You are right, I agree with you. But i think you got my point, why i was saying that "Best way".


Thanks for all
Vinay K Smile

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
HBhagat
HBhagat
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 935
I Agree with Vinay Kumar..

There are sometimes a scenario comes where we just need the data and there are lots other things to complete in a row.. in that case where there are limited resources these small thintgs really help.. I had gone into this situation 2 years back when I have to insert all the values only into another new table with an identity column..

And this was a knowledge what he shared.. and the Quiz is whether we know it or not.. thats all..
Thanks, Smile
Harsha Bhagat
Rachel Byford
Rachel Byford
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: 1630 Visits: 958
Maybe we should start having questions phrased - "what is the most interesting answer..."?

Wink
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 11595
In my humble opinion, this was a poor choice of question and 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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22525 Visits: 9671
Alvin Ramard (2/10/2009)
In my humble opinion, this was a poor choice of question and answer.



Cool, when would now be the time to most "correct" questions to ask?
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