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

Откуда:
Сообщений: 15
Добрый день!

Есть таблица в Эксель с названиями таблиц и тех. характеристиками.
ColumnName Tech Fkey IsOrNotNull TableName ID RankofRows TableID
ID int NULL not null DIM.ClaimStatus 8 1 1
Name nvarchar(50) NULL not null DIM.ClaimStatus 9 2 1
ID int NULL not null DIM.ClaimSubtype 10 1 2
Name nvarchar(50) NULL not null DIM.ClaimSubtype 11 2 2
ClaimType_ID int FK [DIM.ClaimType] not null DIM.ClaimSubtype 12 3 2
ID int NULL not null DIM.ClaimType 13 1 3
Name nvarchar(50) NULL not null DIM.ClaimType 14 2 3
ID int NULL not null DIM.DamageType 15 1 4
ShortName nvarchar(50) NULL null DIM.DamageType 16 2 4
Name nvarchar(50) NULL not null DIM.DamageType 17 3 4
ID int NULL not null DIM.KindOfClaim 4 1 5
Name nvarchar(250) NULL not null DIM.KindOfClaim 5 2 5
ID int NULL not null DIM.Period 1 1 6
StartDate date NULL not null DIM.Period 2 2 6
PeriodType_ID int FK [DIM.PeriodType] not null DIM.Period 3 3 6
ID int NULL not null DIM.PeriodType 6 1 7
Name nvarchar(50) NULL not null DIM.PeriodType 7 2 7



Задачa:
1) Создать Таблицы с полями в БД.
2) Создать FK для этих таблиц.
Сначала, по моей логике, я залил этот Excel к себе в БД, а потом создаю квери с циклом.

Я пошел с конца, писал скрипт на создания FK для таблиц, полагая, что это проще (он не рабочий, т.к. у меня довольно мало опыта работы с T-SQL)

Declare @Table_Id int = 1
declare @RankOfRows int = 1

declare @TableName nvarchar(max) 
select @TableName = 
					[TableName] 
					FROM [General_Information].[dbo].[TestKTE]
					where TableId = @Table_Id

declare @ColumnName nvarchar(max)
select @ColumnName = 
					[TableName] 
					FROM [General_Information].[dbo].[TestKTE]
					where TableId = @Table_Id
						and RankOfRows = @RankOfRows


declare @Fkey nvarchar(max) 

select @Fkey = SUBSTRING([Fkey], LEN(LEFT([Fkey], CHARINDEX ('[', [Fkey]))) + 1, LEN([Fkey]) - LEN(LEFT([Fkey], CHARINDEX ('[', [Fkey]))) - LEN(RIGHT([Fkey], LEN([Fkey]) - CHARINDEX (']', [Fkey]))) - 1)
 FROM [General_Information].[dbo].[TestKTE]
where TableId = @Table_Id
	and RankOfRows = @RankOfRows


while @Table_Id < (select Max(TableId) FROM [General_Information].[dbo].[TestKTE])
	begin

		Declare @SQL varchar(max)
		
		if @Fkey is not null
		begin
			SELECT @SQL = 'ALTER TABLE '+@TableName+' ADD FOREIGN KEY ('+@ColumnName+') REFERENCES( '+@Fkey+'(ID))'
			Exec (@SQL)

			if @RankOfRows <= (select Max(RankOfRows) FROM [General_Information].[dbo].[TestKTE] where TableId = @Table_Id)
				set @RankOfRows = @RankOfRows + 1
			else
				begin
					set @Table_Id = @Table_Id + 1
					set @RankOfRows = 1
				end
		end
	end


Может кто уже сталкивался с подобной задачей, подскажите пожалуйста!

Заранее спасибо!

К сообщению приложен файл. Размер - 76Kb
24 авг 19, 10:24    [21956885]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт на создание таблиц, а после - внешние ключи  [new]
Alibek B.
Member

Откуда:
Сообщений: 3310
1. Цикл по
select distinct TableID, TableName from ... order by TableID


2. Внутри цикла 1 цикл по
select ... from ... where TableID = @TableID order by RankofRows


3. Затем еще один цикл по
select ColumnName, TableName, substring(trim('[]' from substring(Fkey, 4, 100)), 4, 100) RefTable from ... where Fkey is not null

Внутри цикла создается PK:
alter table @RefTable add constraint PK_@RefTable_ID primary key (ID)

А затем FK:
alter table @TableName add constraint FK_@TableName_@ColumnName foreign key (@ColumnName) references @RefTable (ID)
24 авг 19, 11:05    [21956890]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт на создание таблиц, а после - внешние ключи  [new]
N_Reeke
Member

Откуда:
Сообщений: 15
Alibek B.,

Спасибо большое!

Не совсем все понял, как работают цикл в цикле, но буду пробовать!
24 авг 19, 11:53    [21956903]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт на создание таблиц, а после - внешние ключи  [new]
N_Reeke
Member

Откуда:
Сообщений: 15
Alibek B.,

А можно поподробней хотя бы в первых двух пунктах... Где мой Exec должен быть - во вложенном цикле?

И первый пункт у меня идет в табличный параметр, который я буду дальше использовать или как?

Второй пункт - какую я выборку делаю и откуда? select ... from ... where
24 авг 19, 12:15    [21956919]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт на создание таблиц, а после - внешние ключи  [new]
invm
Member

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

Метаданные неполные. Поэтому исходим из предположения, что ID - это ПК
+
use tempdb;
set xact_abort on;
go

declare @t table (ColumnName sysname, Tech sysname, Fkey sysname null, IsOrNotNull sysname, TableName sysname, ID int, RankofRows int, TableID int);
insert into @t
values
('ID', 'int', null, 'not null', 'DIM.ClaimStatus', '8', '1', '1'),
('Name', 'nvarchar(50)', null, 'not null', 'DIM.ClaimStatus', '9', '2', '1'),
('ID', 'int', null, 'not null', 'DIM.ClaimSubtype', '10', '1', '2'),
('Name', 'nvarchar(50)', null, 'not null', 'DIM.ClaimSubtype', '11', '2', '2'),
('ClaimType_ID', 'int', 'FK [DIM.ClaimType]', 'not null', 'DIM.ClaimSubtype', '12', '3', '2'),
('ID', 'int', null, 'not null', 'DIM.ClaimType', '13', '1', '3'),
('Name', 'nvarchar(50)', null, 'not null', 'DIM.ClaimType', '14', '2', '3'),
('ID', 'int', null, 'not null', 'DIM.DamageType', '15', '1', '4'),
('ShortName', 'nvarchar(50)', null, 'null', 'DIM.DamageType', '16', '2', '4'),
('Name', 'nvarchar(50)', null, 'not null', 'DIM.DamageType', '17', '3', '4'),
('ID', 'int', null, 'not null', 'DIM.KindOfClaim', '4', '1', '5'),
('Name', 'nvarchar(250)', null, 'not null', 'DIM.KindOfClaim', '5', '2', '5'),
('ID', 'int', null, 'not null', 'DIM.Period', '1', '1', '6'),
('StartDate', 'date', null, 'not null', 'DIM.Period', '2', '2', '6'),
('PeriodType_ID', 'int', 'FK [DIM.PeriodType]', 'not null', 'DIM.Period', '3', '3', '6'),
('ID', 'int', null, 'not null', 'DIM.PeriodType', '6', '1', '7'),
('Name', 'nvarchar(50)', null, 'not null', 'DIM.PeriodType', '7', '2', '7');

select * from @t;

declare @s nvarchar(max);

with t as
(
 select
  *,
  max(RankofRows) over (partition by TableID) as last_column
 from
  @t
),
sql (x)
as
(
 select
  d.c as [*]
 from
  t cross apply
  (select replace(replace(t.Fkey, N'FK [', ''), ']', '')) a(ReferencedTableName) cross apply
  (
   select
    case when t.RankofRows = 1 then N'create table ' + quotename(TableName) + N' (' else N'' end +
    quotename(t.ColumnName) + N' ' + t.Tech +
    case when t.ColumnName = N'ID' then N' constraint ' + quotename(N'PK_' + TableName) + N' primary key' else N'' end +
    case when t.RankofRows = t.last_column then N'); ' else N', ' end
  ) b(c) cross apply
  (
   select 0, b.c
   union all
   select 1,
    N'alter table ' +
    quotename(TableName) +
    N' add constraint ' +
    quotename(N'FK_' + t.TableName + N'__' + a.ReferencedTableName) +
    N' foreign key (' + quotename(t.ColumnName) + N') references ' +
    quotename(a.ReferencedTableName)
    + N' (ID)'
   where
    t.RankofRows = t.last_column and
    a.ReferencedTableName is not null
  ) d(so, c)
 order by
  d.so, t.TableID, t.RankofRows
 for xml path(''), type
)
select
 @s = x.value('.', 'nvarchar(max)')
from
 sql;

select @s;

begin tran;
exec sys.sp_executesql @s;
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME in (select TableName from @t)
rollback;
24 авг 19, 13:06    [21956942]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт на создание таблиц, а после - внешние ключи  [new]
N_Reeke
Member

Откуда:
Сообщений: 15
invm,

Дай Бог здоровья, благодарю!
24 авг 19, 13:34    [21956949]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить