InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:30:43 GMT20http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]homebrew01 (12/21/2009)[/b][hr][quote][b]CirquedeSQLeil (12/21/2009)[/b][hr]Then a simple select * into tablebackup12212009 from table would suffice for the backup.[/quote]I seem to recall that you can get blocking when creating a table this way if the source table is big because system locks are held until the insert is complete. You could create the empty table tablebackup12212009 first , then insert the data.[/quote]It's an absolute myth that was once true prior to SQL Server 6.5 sp1.[url]http://support.microsoft.com/kb/153441/EN-US/[/url]Mon, 21 Dec 2009 19:41:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxIMHO, SSIS will be slower for this kind of process.As homebrew said, you could use the GUI to script the changes. And then run them from the server itself.Also, you could use the alter column to change the column to a bigint.Mon, 21 Dec 2009 15:45:47 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Joe-420121 (12/21/2009)[/b][hr][b]3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min[/b].[/quote]Instead of using the GUI locally on your desktop, use the GUI to generate the script, then log on to the server remotely and run the the script there in a query window.Mon, 21 Dec 2009 13:43:22 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Joe-420121 (12/21/2009)[/b][hr]1. The table has only 4 coulmmns, no FK, constrains etc.2. The table is used 24 hours, and about 2000 users use it all the time.3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min.4. Copy data to tmp table and when all done, and drop the old table and just rename it quicky.( also I am creating the table in a new file group).5. The size of table is about 60GB, so I feel SELECT INTO might take more time than SSIS. am I wrong?Thanks for all your helps...[/quote]SSIS probably would not be any faster.Mon, 21 Dec 2009 13:25:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx1. The table has only 4 coulmmns, no FK, constrains etc.2. The table is used 24 hours, and about 2000 users use it all the time.3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min.4. Copy data to tmp table and when all done, and drop the old table and just rename it quicky.( also I am creating the table in a new file group).5. The size of table is about 60GB, so I feel SELECT INTO might take more time than SSIS. am I wrong?Thanks for all your helps...Mon, 21 Dec 2009 13:22:27 GMTJoe-420121http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Carlos Pereyra (12/21/2009)[/b][hr]Any reason why you're not using alter table alter column?as in alter table mytable alter column my column bigint?Carlos.[/quote]The best answer is - as a safeguard - but the OP will have to respond.Mon, 21 Dec 2009 13:15:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxAny reason why you're not using alter table alter column?as in alter table mytable alter column my column bigint?Carlos.Mon, 21 Dec 2009 13:09:45 GMTCarlos Pereyrahttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxIf you're going to use INSERT INTO, you'll need to name all the columns. One easy way to get the list of columns (if the table has many) is "select name + ',' from syscolumns where object_name(id) = 'mytablename' order by colorder (Replace mytablename with the name of your table) This will give you the columns and the commas in-between.Carlos.Mon, 21 Dec 2009 13:05:29 GMTCarlos Pereyrahttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Lynn Pettis (12/21/2009)[/b][hr][quote][b]Joe-420121 (12/21/2009)[/b][hr]The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity2. using SSIS export data from old table to [backup12212009]3. Delete old table4. Rename backup12212009Can do as above?[/quote]Why not just use INSERT INTO? Just be sure to enable INDENTITY insert either way, and if using T-SQL, to turn it off when done.[/quote]As Lynn said, I would use the INSERT INTO. It will be faster in most cases.Also, it is very important that when you create the table that it is created to mirror the original table with the exception of the BIGINT.I, would also not Drop the old table for several weeks. I like to keep it around as a failsafe. It is much faster to recover from that table than from backup.Mon, 21 Dec 2009 13:03:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Joe-420121 (12/21/2009)[/b][hr]The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity2. using SSIS export data from old table to [backup12212009]3. Delete old table4. Rename backup12212009Can do as above?[/quote]if there are any dependent foreign keys on the table, or if the table has any constraints or defaults then no, that is [b]NOT [/b]the way to do it.doing a full backup and additionally backing up the table as Jason suggested, and then using the SSMS GUI is the way to do it.Mon, 21 Dec 2009 13:01:56 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]Joe-420121 (12/21/2009)[/b][hr]The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity2. using SSIS export data from old table to [backup12212009]3. Delete old table4. Rename backup12212009Can do as above?[/quote]Why not just use INSERT INTO? Just be sure to enable INDENTITY insert either way, and if using T-SQL, to turn it off when done.Mon, 21 Dec 2009 12:37:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxThe following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity2. using SSIS export data from old table to [backup12212009]3. Delete old table4. Rename backup12212009Can do as above?Mon, 21 Dec 2009 12:33:12 GMTJoe-420121http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxThis is quick and dirty way to create an empty table ... Not one to file under best practice though :cool:[code="sql"]select * into tablebackup12212009 from table where 2=1[/code]Mon, 21 Dec 2009 12:32:13 GMTMark_Pratthttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxGood Point Homebrew - It is a better practice in most cases to create the table first.[i]If all is broken[/i] currently because nothing can be inserted into the table, then it may be fine in this scenario to use the select into.Mon, 21 Dec 2009 12:25:03 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspx[quote][b]CirquedeSQLeil (12/21/2009)[/b][hr]Then a simple select * into tablebackup12212009 from table would suffice for the backup.[/quote]I seem to recall that you can get blocking when creating a table this way if the source table is big because system locks are held until the insert is complete. You could create the empty table tablebackup12212009 first , then insert the data.Mon, 21 Dec 2009 12:14:14 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxNPMon, 21 Dec 2009 11:56:12 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxThank you guys for your helps.Mon, 21 Dec 2009 11:45:37 GMTJoe-420121http://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxIn addition, I recommend you make a backup of the table as a precaution.I would go to the extent of scripting out the table, including indexes and Key relationships.Then a simple select * into tablebackup12212009 from table would suffice for the backup.Doing this provides a bit of protection should something happen during the conversion. This has saved me from catastrophy due to a transaction deadlock on the table rebuild that killed the temp table and the data in it. If not for the backup, I would have been hosed.Other than that, do as Lowell recommends.Mon, 21 Dec 2009 11:27:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxyou'll want to use the GUI in SSMS;and change it to bigINT; the GUI will create a new table witht he proper structure, migrate the data, migrate all the foreign keys, constraints, etc, and drop teh old, then rename the new table to the proper name;it is much easier and faster to use the GUI than to do that by hand, especially on a big busy table.i'm fairly sure that it also changes the [b]child tables [/b]that point to it with foreign keys, since they need to be changed to BIGINT also. one simple operation like changing the data type might affect dozens or hundreds of tables.everyone will be locked out of the table while you are changing it, but they cannot insert right now, i would assume, so you are probably going to have to do this right away.Mon, 21 Dec 2009 11:02:02 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic837371-146-1.aspxMy production environment has a table with indenity column with INT datatype; The column size is maxed out, The table size is 60GB. What is the best way to change the datatype to BIGINT to support more inserts. The table is heavly used by the users.Mon, 21 Dec 2009 10:31:33 GMTJoe-420121