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.

_the generated query is giving the error_ You are going to need to post the generated query. In fact I suggest you post something that can reproduce the error, that's a pretty standard requirement on SO. There's no way anyone can help with your current problem description. — Nick.Mc, May 15 '22 at 00:11
That error for that example SQL code means exactly what it says: the column doesn't exist. Your entire question is very confusing right now. It seems the problem is with renaming a column and changing a data type. You haven't posted anything about that. — Nick.Mc, May 15 '22 at 12:22
BTW the answer in the other question is wrong - there are a few different ways to rename and change the data type of a column that has a FK _without_ dropping the table — Nick.Mc, May 15 '22 at 12:24
I really don't understand what's the trouble to understand the question.. there's a script generated which is trying to insert a column with a name which it says is invalid even though that's the name of the column.. — CodeMonkey, May 15 '22 at 21:13
What other ways are there? When I tried changing the name and data type I got a message saying that for this kind of change the table must be dropped and recreated — CodeMonkey, May 15 '22 at 21:14
You now have three different problem statements. Which one is the problem? 1) Change a columns name and type. 2) Column is missing when you run an insert statements 3) Rearrange order of existing columns. — Nick.Mc, May 15 '22 at 23:08
For Q1, it seems like you've tried to do this but never posted any details, for 2, the error says your column doesn't exist. For 3, the order of columns is technically irrelevant, the only reason to change this is because you as a developer don't like the look of it, or if you have some very bad SQL syntax that assumes a column order. I apologise if this comes across as snarky, but I suggest you take one of your three problems and explain clearly, with sample code, what the issue is. — Nick.Mc, May 15 '22 at 23:10
2 i never said as there's nothing that doesn't exists, and 3 was only addition of information and not an additional problem.. the only question is 1 and I posted the specific line with the problem.. I really don't know what else you need to see.. There's a db table with a column named $node_id_D17E19B333A445E99513686DEC9E2929 and I get that error when trying to insert to the column — CodeMonkey, May 16 '22 at 07:06
The error says it doesn't exist. Do you get the same error when you run `SELECT [$node_id_D17E19B333A445E99513686DEC9E2929] FROM [dbo].[ConfigsTbl]` ? — Nick.Mc, May 16 '22 at 07:17
It would help if you posted the DDL of the table, i.e. generate the create table statement. Then we could try and reproduce — Nick.Mc, May 16 '22 at 07:18

eshirvana · Answer 1 · 2022-05-19T15:32:55.013

$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.

Drop and recreate a table with data of a Graph DB

1 Answers1