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


T-SQL


T-SQL

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97986 Visits: 38995
Question stated "Now I need a copy of this table with an identity column for some temporary work." This is the phrase that pointed me to select "Use an identity function with a SELECT INTO" as the answer. No need for the constraints etc from the source table, just a quick and dirty copy for temporary work.

The part of the question that Steve updated didn't even play a part in my decision making process.

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)
James Horsley
James Horsley
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 451
SELECT INTO loses all your original indexes, constraints etc - much better to copy the structure of original table by scripting it, add the Identity column, rename old table, build new one and copy rows across




James Horsley
Workflow Consulting Limited
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97986 Visits: 38995
And if you don't need those constraints and indexes for the temporary work, why go through the process of creating them when the SELECT INTO works just fine.

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)
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5241 Visits: 3648
Ninja's_RGR'us (2/10/2009)
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.


That is very useful information. Actually I went researching my own post to see if this was still valid and was wondering if the locking of system tables was still a problem. I guess I am behind the times on this point. Thanks for the clarification. Was worth the price of admission.
James Horsley
James Horsley
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 451
Actually original question (that I was answering) was

I have a table XYZ with 3 INT columns. There are 3000 records already in this table. Now I need a copy of this table with an identity column. What is the best way to do this?


No mention of "temporary".

And even then it depends what "temporary" means - if it is to "temporarily" have an IDENTITY on a column while I insert the true 200,000,000 records from a live database then you may well want to still have all data domain checks in place




James Horsley
Workflow Consulting Limited
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3682 Visits: 3059
Since there's no such thing as an "identity fuction", I went with the create/select-into answer.... Wink

Seriously, though, I appreciate both the reminder of the identity fuNction and Hugo's immediate and thorough discussion of the topic. (Steve, give that guy some more points!!)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97986 Visits: 38995
James Horsley (2/10/2009)
Actually original question (that I was answering) was

I have a table XYZ with 3 INT columns. There are 3000 records already in this table. Now I need a copy of this table with an identity column. What is the best way to do this?


No mention of "temporary".

And even then it depends what "temporary" means - if it is to "temporarily" have an IDENTITY on a column while I insert the true 200,000,000 records from a live database then you may well want to still have all data domain checks in place


Guess I got to the question after Steve changed it.

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)
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13300 Visits: 6903
I started out in Hugo's camp. I'm always glad to have learned a new function, but one thing I can't get away from is that, in the real world, if I'm creating a unique row identifier, then I'm probably going to come back and look for it later and for that I will almost always want an index. So that makes it a choice between the two alternatives below. It looks like six of one and a half dozen of the other.

Before stopping to think about which was "better", I would have called it a draw. (And being superstitious, I still am in the habit of avoiding SELECT... INTO....)

However, populating a table before building the index means that using SELECT...INTO with the IDENTITY function (followed by a CREATE INDEX) is going to run FASTER than creating a table with a primary key predefined and then doing an INSERT INTO ... SELECT. (A couple of scripts follow at the bottom if anyone wants to test the difference at a million rows.)

This isn't going to make me go back and change all of our existing stored procedures, but it is something to be remembered going forward.

---------------------------------------------
-- first thought: who cares?
---------------------------------------------
create table #temp(ID int identity(1,1) PRIMARY KEY, foo1 varchar (20), foo2 int)
insert into #temp
select foo1, foo2
from SomeOtherTable

-- vs --

SELECT IDENTITY(int, 1,1) AS ID_Num, foo1, foo2
into #temp
from SomeOtherTable

PLUS

ALTER TABLE .........
or
CREATE INDEX


-----------------------------------------
-- test scripts
-----------------------------------------


-- using IDENTITY function and creating index afterwards
SELECT IDENTITY(int, 1,1) as ID , getdate() + N as Futureday
into #future
from tally

create unique clustered index PK_Future on #future (ID)

select count(*), max(Futureday) from #future
drop table #future
go


-- predefining table with an IDENTITY column
create table #future (ID int identity(1,1) primary key, Futureday datetime)
insert into #future
select getdate() + N as Futureday
from tally

select count(*), max(Futureday) from #future
drop table #future
go

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
leslaw
leslaw
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: 2713 Visits: 36
All tables should have primary key, so it should also be created.

Leslaw
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13300 Visits: 6903
leslaw (2/10/2009)
All tables should have primary key, so it should also be created.

Leslaw



Cardinal Moden will call me a heretic, yet I beg to differ with respect to the word "ALL".

I agree that any non-temporary table should have a unique primary key, but oftentimes a temporary table is built within a stored procedure and then returned with a final select statement. These tables do not survive the ending of the stored procedure. By the time you see the results, the table itself no longer exists.

Sometimes the logic of the stored procedure involves a need to go back and retrieve or update values of particular rows within that table, and in such cases an index is definitely a good idea. But, in the absence of any need to go back and retrieve or update the results from a particular row, what purpose is served by imposing an arbitrary primary key?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
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