Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sp_rename или как-то ещё, перекинуть 16Гб  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Исходные данные:
ms sql 2008r2
Нужно перекинуть все данные из таблицы 'Table_1' в 'Table_1_arh', это около 16Гб.
А затем вернуть обратно только за последний месяц, 100Мб. Как вернуть обратно - понятно. Как перенести в архивные таблицы быстро?
Это нужно проделать для 25 таблиц(похожая структура, см. скрипт ниже), в каждой из которых 10 полей.
+

CREATE TABLE dbo.Table_1
(
	ID binary(16) NOT NULL PRIMARY KEY CLUSTERED,
	Version1 timestamp NOT NULL, -- это не нужно перекидывать
	date_time datetime NULL,
	d1 int NULL,
	d2 varchar(50) NULL	
)
go
CREATE NONCLUSTERED INDEX inx_01 ON dbo.Table_1 (d1)
CREATE NONCLUSTERED INDEX inx_02 ON dbo.Table_1 (d2)
go

CREATE TABLE dbo.Table_1_arh  -- архивная таблица
(
	ID binary(16) NOT NULL PRIMARY KEY CLUSTERED,
	Version1 timestamp NOT NULL,
	date_time datetime NULL,
	s2 int NULL,
	d5 varchar(50) NULL
)
go
CREATE NONCLUSTERED INDEX inx_03 ON dbo.Table_1_arh (s2)
CREATE NONCLUSTERED INDEX inx_04 ON dbo.Table_1_arh (d5)


Думал ускорить процесс так:
sp_rename Table_1, Table_temp
sp_rename Table_1_arh, Table_1
sp_rename Table_temp, Table_1_arh

-- а потом каждое поле
sp_rename 'Table_1_arh.d1', 's2', 'COLUMN'

Но в одном пакете нельзя так делать, нужно отделять их GO. Ещё не очень нравиться перечислять все поля, которые переименовываю.
+ переименование индексов. После переноса, наименование индексов должны соответствовать как у исходных таблиц!

Или через
INSERT INTO Table_1_arh (список полей)
SELECT (список полей)
FROM Table_1

но это долго. Если убить индексы в Table_1_arh, а потом создать - да это ускорит процесс. А можно ещё быстрей? :)

Создать архивную таблицу, у которой наименования полей совпадают с исходной нельзя (по условиям этой задачи). Только так, как у меня в скрипте.


Как решить такую задачу?
В идеале хочется хранимую процедуру с двумя входными параметрами, таблица откуда и таблица куда.
23 ноя 14, 12:17    [16891751]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Таблицы сделать одинаковыми по структуре и с одинаковыми индексами + представление на архивную, в котором поля поименованы как требуется.
Тогда перенос делается очень просто:
truncate table [Архивная];
alter table [Основная] switch to [Архивная];
23 ноя 14, 13:47    [16891845]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
invm,

Спасибо за подсказку, это проще чем динамический sql.

+

declare 
	@tab1 varchar(50) = 'Table_1', 
	@tab_arh varchar(50) = 'Table_1_arh', 
	@char13 varchar(10) = CHAR(10) + CHAR(13),
	@sql varchar(max) 

set @sql = ''
+ 'USE ' + DB_NAME() + @char13
+ 'go ' + @char13
+ 'sp_rename ' + @tab1 +', Table_temp' + @char13
+ 'go ' + @char13
+ 'sp_rename ' + @tab_arh + ', ' + @tab1 + @char13
+ 'go ' + @char13
+ 'sp_rename Table_temp, ' + @tab_arh + @char13
+ 'go ' + @char13

--print @sql

SELECT @sql = @sql +
'sp_rename ''' + arh.Objects_arh + '.' + tab.Columns  + ''', ' +
	'''' + arh.Columns_arh +''', ''COLUMN''' + @char13
+ 'go ' + @char13
+ 'sp_rename ''' + tab.Objects + '.' + arh.Columns_arh  + ''', ' +
	'''' + tab.Columns +''', ''COLUMN''' + @char13
+ 'go ' + @char13
--,
--tab.Objects, 
--tab.Columns, 
--arh.Objects_arh,
--arh.Columns_arh
FROM 
	(
	SELECT	
	o.name Objects,
	c.name Columns,
	c.column_id,
	c.system_type_id
	FROM sys.columns c
		JOIN sys.objects o
		ON c.object_id	= o.object_id	
		AND o.name = @tab1 AND c.name NOT IN('ID','Version1','date_time')
	) tab
	JOIN 
	(
	SELECT	
	o.name Objects_arh,
	c.name	Columns_arh,
	c.column_id,
	c.system_type_id
	FROM	sys.columns	c
	JOIN	sys.objects	o
	ON	c.object_id	=	o.object_id
		AND o.name = @tab_arh AND c.name NOT IN('ID','Version1','date_time')
	) arh
	ON 
		tab.column_id = arh.column_id 
		AND tab.system_type_id = arh.system_type_id

-- print @sql

SELECT @sql = @sql +
CASE
WHEN tab.index_id > 1 THEN			-- если не кластерный индекс
'sp_rename N''' + arh.arh1 +'.' + tab.name_inx + ''', N'''+ 
	arh.name_inx +''''+', N''INDEX''' + @char13
+ 'go ' + @char13
+ 'sp_rename N''' + tab.tab1 +'.' + arh.name_inx + ''', N'''+ 
	tab.name_inx +''''+', N''INDEX''' + @char13
+ 'go ' + @char13
ELSE 
'sp_rename N''' + arh.arh1 +'.' + tab.name_inx + ''', N'''+ 
	'INX_TEMP' +''''+', N''INDEX''' + @char13
+ 'go ' + @char13
+ 'sp_rename N''' + tab.tab1 +'.' + arh.name_inx + ''', N'''+ 
	tab.name_inx +''''+', N''INDEX''' + @char13
+ 'go ' + @char13
+ 'sp_rename N''' + arh.arh1 +'.' + 'INX_TEMP' + ''', N'''+ 
	arh.name_inx +''''+', N''INDEX''' + @char13
+ 'go ' + @char13
END
--	, 
--tab.*,
--arh.*
FROM
	(
	SELECT 
	o.name as tab1,
	i.name  as name_inx,
	i.index_id,
	i.type
	FROM	sys.indexes	i
		JOIN	sys.objects	o
		ON	i.object_id	=	o.object_id
		AND o.name = @tab1 
	) tab
	INNER JOIN 
	(	
	SELECT 
	o.name as arh1,
	i.name as name_inx,
	i.index_id,
	i.type
	FROM	sys.indexes	i
		JOIN	sys.objects	o
		ON	i.object_id	=	o.object_id
		AND o.name = @tab_arh 	
	) arh
	ON tab.index_id = arh.index_id
	AND tab.type = arh.type	

print @sql



Пока писал скрипт, возник вопрос: почему некластерные индексы можно так(см. ниже) переименовывать, а для кластерного - нужно через временное наименование индекса?
sp_rename N'Table_1_arh.inx_01', N'inx_03', N'INDEX'
go 
sp_rename N'Table_1.inx_03', N'inx_01', N'INDEX'

для кластерного ругался
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.


К сообщению приложен файл (перенос в архив.sql - 2Kb) cкачать
23 ноя 14, 19:39    [16892860]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
trew
Пока писал скрипт, возник вопрос: почему некластерные индексы можно так(см. ниже) переименовывать, а для кластерного - нужно через временное наименование индекса?
Нормально переименовываются кластерные индексы, без всяких временных наименований. Очевидно, вы промахнулись с именем индекса и получили ошибку.
23 ноя 14, 19:57    [16892955]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
invm,

Возможно.

сейчас пробую
sp_rename N'Table_1.PK__Table_1__3214EC270F975522', N'PK__Table_1___3214EC271367E606', N'INDEX'

Error: The new name 'PK__Table_1___3214EC271367E606' is already in use as a INDEX name and would cause a duplicate that is not permitted.
У меня индекс PK__Table_1___3214EC271367E606 есть на второй таблице.

А некластерные переименовываются без проблем.

select @@VERSION
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86)
23 ноя 14, 20:06    [16892984]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
trew,

Потому что в вашем случае, одновременно с кластерным индексом переименовывается и соответствующий constraint. И получаете дубликат наименования объекта, а не индекса.
23 ноя 14, 20:22    [16893050]     Ответить | Цитировать Сообщить модератору
 Re: sp_rename или как-то ещё, перекинуть 16Гб  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
invm,

Большое спасибо за ответы!
23 ноя 14, 21:05    [16893176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить