shreessmrv Ответов: 2

Создайте новую таблицу из существующей таблицы для каждого отдельного значения определенного столбца и назовите новую таблицу этим отдельным значением


У меня есть таблица в базе данных SQL Server, из которой мне нужно создать разные таблицы для каждого уникального значения конкретного столбца.

Вот изображение существующей таблицы -
Existing_Table		
Column_1	Column_2	Column_3
A	        XYZ	        Group1
B	        ABC	        Group2
C	        CBA	        Group1
D	        PQR	        Group3
E	        XXX	        Group2
F	        ABC123	        Group1

Таким образом, уникальные значения, для которых мне нужно будет создать новую таблицу, будут -
UNIQUE Column_3
Group1
Group2
Group3

Теперь вновь созданные таблицы ожидаемо будут выглядеть так-

New_Table_Group1	
Column_1	Column_2
A	        XYZ
C	        CBA
F	        ABC123

New_Table_Group2	
Column_1	Column_2
B	        ABC
E	        XXX

New_Table_Group3	
Column_1	Column_2
D	        PQR


Здесь имя таблицы будет основано на уникальном значении, на основе которого создается таблица.

Не смог добиться этого. Может ли кто-нибудь, пожалуйста, помочь достичь ожидаемого результата? Любая помощь очень ценится.

Что я уже пробовал:

Я попробовал следующий код-

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
DECLARE @temp_table TABLE (
    idx smallint Primary Key IDENTITY(1,1), Group varchar(200)
    )

-- populate group table
INSERT @temp_table
SELECT distinct Column_3 FROM Existing_Table

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table) 
IF @numrows > 0
    WHILE (@i <= @indexcount)
    BEGIN
      -- get the next Group primary key
        SET @Group = (SELECT Group FROM @temp_table WHERE idx = @i);
        IF OBJECT_ID('dbo.New_Table_@Group', 'U') IS NOT NULL 
            DROP TABLE dbo.New_Table_@Group;
        select Column_1, Column_2  
        into New_Table_@Group
        from Existing_Table where Column_3 = @Group;
        -- increment counter for next Column_3 group value
        SET @i = @i + 1
    END


Это не удается, потому что он создает только одну новую таблицу по имени "New_Table_@Group" сам и не заменяет ожидаемое уникальное значение из предполагаемого столбца и, следовательно, не создает новую таблицу для каждого уникального значения предполагаемого столбца.

CHill60

Почему ты хочешь это сделать?

shreessmrv

Это требование для того, над чем я работаю. Эти новые таблицы будут использоваться различными приложениями для дальнейшей обработки.

CHill60

Есть по крайней мере два лучших подхода
1. Сделайте так, чтобы другие приложения получили доступ к исходной таблице, или (лучше)
2. Создание Просмотры это фильтр таблицы на основе группы.

Это очень плохая практика, чтобы создать дублирующую информацию, как это

shreessmrv

Согласованный. Но в моем сценарии это неосуществимо. приложение нуждается в вводе данных только в определенном формате с заранее определенным набором полей. Базовая таблица содержит больше деталей, поэтому приложение не принимает ее и, следовательно, не выполняет ожидаемую задачу. Только после проверки всего этого мы пришли к этому плану создания различных эксклюзивных таблиц.

CHill60

Вы только что описали точную причину наличия взглядов (это также противоречит тому, как вы описали свою проблему). Все, что вы описываете, может быть достигнуто с помощью представлений без дублирования данных.
Для вызывающего приложения не должно быть очевидного различия между запросом представления и запросом таблицы.
Если приложение не "принимает" результаты запроса к таблице (или представлению), то возникает проблема с приложением. Например, возможно использование "SELECT * FROM ..." - это очень плохая практика. Перечислите необходимые столбцы.

shreessmrv

Ахилл, Ах! Есть одна проблема. Вы рассказывали о минусах. о создании нескольких таблиц и скорее выборе представлений. Ладно, я это понял! Но у меня нет проблем с созданием таблицы или представления как такового. На самом деле моя проблема заключается в динамическом создании одного из них для каждого существующего и будущих значений конкретного столбца. Так что я отвечал с другой точки зрения!
Значения столбцов, на основе которых мне нужно создать таблицы или представления, будут получать свои значения динамически. Поэтому независимо от того, создаю ли я таблицы или представления, я хочу знать, как их создать, и да, это должно происходить для любых будущих значений, которые получит столбец. Если вы поможете мне достичь этого, это будет более полезно. :)

Herman&lt;T&gt;.Instance

поставить [] вокруг @ - это переменная
Лучше:
Объявить @TableName NVARCHAR(200)
Набор ДБО @Имятаблицы ='.New_Table_'+ [@Группа]

Если OBJECT_ID([@TableName], 'U') не равен NULL
DROP TABLE [@TableName]

Herman&lt;T&gt;.Instance

Что должна делать эта часть?
выберите Column_1, Column_2
в группу New_Table_@
из Existing_Table, где Column_3 = @Group;

попытка поместить данные в таблицу без каких-либо столбцов?

shreessmrv

Эта часть предназначена для создания новых таблиц (подход select-into-создание таблиц с использованием столбцов из существующей таблицы).
Это создаст новую таблицу для каждого уникального значения Column_3, назвав таблицы в формате 'New_Table_[@Group]' со столбцами Column_1, Column_2, где Column_3= [@Group]

Herman&lt;T&gt;.Instance

А сейчас это работает?

shreessmrv

К сожалению, нет! Это дает мне ошибку "недопустимое имя столбца" -

Msg 207, Уровень 16, состояние 1, процедура sp_Tables_For_Group, строка 25
Недопустимое имя столбца "@Group".
Msg 207, Уровень 16, состояние 1, процедура sp_Tables_For_Group, строка 26
Недопустимое имя столбца "@TableName".

Herman&lt;T&gt;.Instance

смотрите цитаты в моем примере:
Объявить @TableName NVARCHAR(200)
Набор ДБО @Имятаблицы ='.New_Table_'+ [@Группа]

shreessmrv

Да, я использовал его именно так, как вы предлагали. Я не пропустил цитаты.

2 Ответов

Рейтинг:
5

shreessmrv

Я получил это, работая с курсором.

Вот код, который я использую-

DECLARE @Group varchar(200);
DECLARE @CreateTableScript nvarchar(MAX);

DECLARE groups CURSOR LOCAL FAST_FORWARD FOR
    SELECT
          Column_3 AS [Group]
        , N'IF OBJECT_ID(N''dbo.' + QUOTENAME('New_Table_' + Column_3) + ''', ''U'') IS NOT NULL 
                DROP TABLE dbo.' + QUOTENAME('New_Table_' + Column_3) + ';
            select Column_1, Column_2  
            into dbo.' + QUOTENAME('New_Table_' + Column_3) + '
            from Existing_Table where Column_3 = @Group;' AS CreateTableScript
    FROM (SELECT DISTINCT Column_3 FROM Existing_Table) AS groups;

OPEN groups;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM groups INTO @Group, @CreateTableScript;
    IF @@FETCH_STATUS = -1 BREAK;
    PRINT @CreateTableScript;
    EXEC sp_executesql
          @CreateTableScript
        , N'@Group varchar(200)'
        , @Group = @Group;
END;
CLOSE groups;
DEALLOCATE groups;
GO


Спасибо @digimanus и @CHill60 за помощь в этом, которая помогла мне узнать некоторые хорошие вещи вокруг этого.


CHill60

Вам было бы лучше использовать цикл WHILE, чем курсор, который может заблокировать исходную таблицу. Используйте курсоры только в самом крайнем случае.
В любом случае, учитывая, что именно мое решение указало вам на использование динамического sql и выполнение sp_executesql, я нахожу довольно грубым, что вы приняли свое собственное решение, но спасибо вам за то, что вы включили кредит в свое решение.

shreessmrv

Остынь, извини, если это прозвучало грубо. Это никогда не входило в мои намерения. Все, чего я хотел, это чтобы это сработало. Как раз в это время я решил заняться тем, что у меня получилось раньше всего. Я все еще открыт для понимания и использования правильного подхода и тем самым узнаю об этом. Пожалуйста, дайте мне знать, как я должен действовать, чтобы достичь этого с помощью цикла while. Мне очень интересно знать это и использовать то же самое.

CHill60

Вы использовали цикл while в исходном коде. Преимущество цикла while над курсором заключается в том, что вы не будете блокировать таблицу во время обработки. Вам понадобится уникальный идентификатор в вашей групповой таблице (как у вас уже было)

Рейтинг:
18

CHill60

Вам необходимо использовать динамический sql, чтобы вставить правильное имя группы В имя(ы) таблицы.
Например (непроверенный)

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
DECLARE @temp_table TABLE (
    idx smallint Primary Key IDENTITY(1,1), [Group] varchar(200)
    )

-- populate group table
INSERT @temp_table
SELECT distinct Column_3 FROM #test		-- sub your table name here

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table) 
IF @numrows > 0
    WHILE (@i <= @indexcount)
    BEGIN
      -- get the next Group primary key
        SET @Group = (SELECT [Group] FROM @temp_table WHERE idx = @i);

		declare @sql nvarchar(max)
		declare @drop nvarchar(max)

		set @drop = 'IF OBJECT_ID(''dbo.New_Table_' + @Group + ''', ''U'') IS NOT NULL DROP TABLE dbo.New_Table_' + @Group
		EXECUTE sp_executesql  @drop

		set @sql = 'SELECT Column_1, Column_2 INTO New_Table_' + @Group + ' FROM #test WHERE Column_3 = ''' + @Group + ''''
		EXECUTE sp_executesql  @sql

        -- increment counter for next Column_3 group value
        SET @i = @i + 1
    END
Также обратите внимание на использование квадратных скобок вокруг имени столбца Group в таблице @temp_table (и везде она используется). Это связано с тем, что группа является зарезервированным словом и не должна использоваться в качестве имени столбца. Его можно использовать в качестве имени столбца только в том случае, если оно заключено в квадратные скобки - [Group]

[EDIT] для удобства других читателей, пожалуйста, ознакомьтесь с моими комментариями к OP. Лучшим подходом было бы создать Просмотры для использования другим приложением. это описание может быть достигнуто с помощью представлений без дублирования данных. Для вызывающего приложения не должно быть очевидного различия между запросом представления и запросом таблицы.
Приведенное выше решение может быть изменено для создания представлений, а не таблиц, главное, чтобы операторы sql были динамическими.


Maciej Los

Ну а я предпочитаю решение "вид", о котором вы уже упоминали в комментарии к вопросу.
А5!

CHill60

Хороший момент - я действительно обновлю свое решение, чтобы привлечь к этому внимание. Спасибо, Мацей. Вы вернулись к своему старому имени пользователя?
Edit - игнорируйте этот последний бит, теперь он показывает ваше обычное имя! Как странно

Maciej Los

Странное поведение Ника/логина наблюдается с момента последнего обновления CP... Я читал об этом на форуме "баги и...".

shreessmrv

CHill6, это создает разные таблицы; но он заменяет только первую букву каждого уникального значения, например -
если мои уникальные значения-AGroup1, AGroup2, BGroup1, CGroup1, то это создание таблиц, использующих только первую букву этих уникальных значений, например -
New_Table_A, New_Table_B, New_Table_C (и да, только одна копия для каждой уникальной первой буквы, New_Table_A-это вновь созданная таблица для значений AGroup1 и AGroup2). Кроме того, эти столы пусты.

CHill60

Вы никогда не упоминали об использовании column_1 как части имени вашей таблицы, и мой код ведет себя не так, как вы описываете. Улучшите свой вопрос с помощью кода, который вы на самом деле используете, и обновите требования с помощью того, что вы на самом деле пытаетесь сделать

shreessmrv

I didn't get you. Column_1 is never part of my Table name. My new tables should have the Column_3 values as part of their names and this I have mentioned explicitly in my question. In the Existing_table that I have provided, the Column_3 values are all starting with the word Group. So If I had to explain the result I was getting, I should have told that it is creating only one new table with letter G. So just to make it understandable I mentioned different texts as column names in my previous comment to differentiate how it is creating table when there are Column_3 values that are starting with same letters and those that are starting with different letters. (just to show that only one table is created even when there are more than one unique value, but which start with same letter)

CHill60

В своем комментарии выше вы ссылаетесь на уникальные значения AGroup1, AGroup2, BGroup1 и что созданные таблицы являются New_Table_A, New_Table_B, New_Table_C