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.
| Author |
Topic |
|
Nedra
Starting Member
21 Posts |
 Posted - 2004-11-02 : 13:14:26
|
| A coworker of mine wants to do schema changes to our database using the following method:select ...intofrom ...drop original tablerename select into table to original table nameIf the column is nullable, he will be converting the column to the correct datatype and applicable length, so to not loose that information.Does anyone know the difference between using a select into vs. insert into...select * from in this circumstance? Which is better performing? Enterprise manager uses the insert into...select * from method. Are there inherent risks with using select into instead? Also, what affect does the recovery model have on each of these methods? We have had situations in the past where the tran log grows until it fills up the disk space when doing schema changes like this on very large tables.Thanks!Nedra |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-02 : 13:25:24
|
| If the recovery model allows bulk updates then this will have less effect on the tr log.I would do it by creating gthe new table from a script then copying the data via inserts in blocks.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-11-02 : 13:48:47
|
| How much data are we talking about?If you're going to alter the table AFTER(?) you move the data, you'll be just as hosed. The minimally logged SELECT * INTO FROM...is good for keeping the transactions that are logged to a minimum, but the ALTER would then be fully logged, so what's the point.How much Data?Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-02 : 13:51:26
|
| And an alter table can end up wasting a lot of space (and give errors).http://www.nigelrivett.net/AlterTableProblems.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Nedra
Starting Member
21 Posts |
Posted - 2004-11-02 : 13:52:46
|
| The tables we have had problems with are generally 1 million rows or greater.Also, we wouldn't do an alter after the select into. We would add the new column with a defalut value and a convert as part of the select statement. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-02 : 13:55:11
|
| I would create the table - add a view to exclude the new column, bcp the data out then bcp it back in again.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-11-02 : 14:23:10
|
| Nigel,I forgot I read that....did you do that originally on 7? Does the same happen in 2k?Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-02 : 14:49:08
|
| You mean the link? It's v7 and 2k.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
select into vs. insert into