SQL Server Unpivot large SQL table and update same table at the same time? (limited by space)

s4n0splo  于 2023-05-05  发布在  其他
关注(0)|答案(1)|浏览(154)

My server at work is very low on space, which we are currently working to resolve. However, we also have some quick deadlines coming up where we need to get this data in there asap.

Origin table: 1.5 billion rows Unpivotted table: will have 11x 1.5billion rows......~17 billion rows

Is there a way to unpivot the origin table and store that over the origin table at the same time? Normally, I would just store it as a new table and then drop the old table. However, in this case it is not an option. I already tried that and errored due to space limitations.

rhfm7lfc

rhfm7lfc1#

Given the limited space I'm not sure that doing it it chunks will meet "quick deadlines" and I would pursue adding a new filegroup and a new file to it using ALTER DATABASE ADD FILE. Then, create the unpivoted table using SELECT INTO and specify the new filegroup using the ON clause.

-- Create a new filegroup and add a file to it
ALTER DATABASE [database_name]
ADD FILEGROUP [filegroup_name];

ALTER DATABASE [database_name]
ADD FILE (
  NAME = [file_name],
  FILENAME = 'C:\path\to\filegroup\file.ndf',
  SIZE = 100GB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10GB
)
TO FILEGROUP [filegroup_name];

-- Create the temporary table on the new filegroup
SELECT *
INTO [filegroup_name].[unpivoted_table]
FROM (
  SELECT id, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11
  FROM origin_table
) AS t
UNPIVOT (
  value FOR col IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11)
) AS unpvt
ON [filegroup_name];

-- Optional: Rename the origin table
EXEC sp_rename 'origin_table', 'origin_table_backup';

-- Copy the unpivoted table to where the origin table is normally stored
SELECT *
INTO origin_table
FROM unpivoted_table;

-- Drop the backup table, once the oigin_table is verified
-- DROP TABLE origin_table_backup;

Obviously this can be broken into separate steps to suit, and handling both a backup table and the new table in the currently limited space may not be possible so you may want to delay overwriting "origin_table" until you have resolved the current lack of space. Until you have the origin_table rebuilt perhaps use the unpivoted_table in lieu.

相关问题