what is more cerrect to use - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

what is more cerrect to use

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-07 : 05:24:30

im meaning ofresources needed?the influence on tempDB and so on :
select x from y into......
or
insert into x (a,b) select (a,b) from z
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 05:37:02

Either of them doesn't make much difference when no of rows are small like 50-100 but when populating large no. of records, I always prefer second version because first version places lock on system tables in TempDB.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 05:38:35

The first query should be
select a, b into x from z

And the second would be
insert into x (a,b) select a, b from z

There is little difference since the second query needs a CREATE TABLE statement first.
I think second statement together with CREATE TABLE will be a little faster because with first statement, SQL Server needs to lookup the two column datatypes and create the table for you.
With your own CREATE TABLE you do not need to do a lookup, since you create the columns directly.

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-07 : 07:02:28

ok and does any of them take more space on thwe tempDB form th other or that all depends just o nthe query results?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-07 : 07:09:36

Since you are not using either Table Variable(@Tablename) or Temporary table (#Table) it will not have impact on Tempdb much. and also you are not using ORDER BY Hint. so i don't think this will have any impact on TempDB as such

madhu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 09:05:38

1 Table created using Select * into wont have indices, constraints that source table has
2 The source table will be locked till new table is created

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources