Drop and recreate a table with data of a Graph DB

Question

This is a follow up question to this question:

SQL drop table and re-create and keep data

I got a table in a Graph db (inside SQL Server) for which I need to change one of its columns' name and type. Sql server said I need to drop and recreate the table for that. I used the accepted answer of the linked question to generate a query to do that. However, there is another column with a name: [$node_id_D17E19B333A445E99513686DEC9E2929] which is used by the graph db, and the generated query is giving the error:

Invalid column name '$node_id_D17E19B333A445E99513686DEC9E2929'.

Like I said, this column already exist with this name.. How can I run the query to drop and recreate the table with the new other column's name?

An example of a line with the error:

INSERT [dbo].[ConfigsTbl] ([$node_id_D17E19B333A445E99513686DEC9E2929], [ID], [Key], [Value], [Group], [Permissions]) VALUES (N'{"type":"node","schema":"dbo","table":"ConfigsTbl","id":1512}', 1513, N'SomeId', N'SomeIdValue', 0, 3)
GO

Addition:

There is still the problem with the name, but it seems that the whole reason for telling that I need to drop and recreate the table is because the column is in the middle of the scheme. If I try to add the new column as the last one and only remove the old one from the middle there is no problem and I don't need to drop the table in that case. And yet, I do want to keep the order of the column. Hoping this information helps to understand what might cause it.

Answer 1

$node_id_UUID and graph_id_UUID are the built-in graph tables that sql server uses to connect edges and nodes and are populated automatically. So you don't need to handle them. And I think this is something that SSMS can't handle properly. Here is what you can do:

    -- backup data into a temp table 
    SELECT  cttbl.ID
          , cttbl.[KEY]
          , cttbl.VALUE
          , cttbl.[GROUP]
          , cttbl.PERMISSIONS 
    INTO temp_ConfigsTbl
    FROM dbo.ConfigsTbl AS cttbl

    -- drop the table 
    DROP TABLE  [dbo].[ConfigsTbl]

    -- recreate the table with the new column
    CREATE TABLE [dbo].[ConfigsTbl]
    (
        ID            INT PRIMARY KEY
        , [KEY]       VARCHAR(100)
        , [VALUE]     VARCHAR(100)
        , NEW_COL     INT
        , [GROUP]     INT
        , PERMISSIONS INT
    ) AS NODE;

    -- restore data from temp back up table 
    INSERT INTO [dbo].[ConfigsTbl] ([ID], [Key], [Value], NEW_col,  [Group], [Permissions])
    SELECT  cttbl.ID
          , cttbl.[KEY]
          , cttbl.VALUE
          , NULL AS NEW_col 
          , cttbl.[GROUP]
          , cttbl.PERMISSIONS 
    FROM  temp_ConfigsTbl  cttbl

    -- and finally drop the temp back up table 
    DROP dbo.temp_ConfigsTbl


    SELECT * FROM  [dbo].[ConfigsTbl]

be noticed that UUID will be changed since sql server rebuilds those columns. Which shouldn't be any problem since you should NOT be using those columns directly in your queries anyway.