SQL Server SQL Cursor Insert into many databases in one server

6l7fqoea 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(121)

On the same server I have got about 100 databases with the same schemas and I would like to Insert same values into all of them and add new tables and columns to all of them with one script. I don't know why, but when I use my script it finishes with success but nothing happens in my databases (my values are not adding). Could you please tell me where I have mistake and correct my script?

DECLARE @name nvarchar(max)

DECLARE cr_i CURSOR READ_ONY FORWARD_ONLY LOCAL STATIC FOR 
    SELECT       
        SUBSTRING(SUBSTRING([ICS], CHARINDEX('Initial Catalog=', [ICS]) + LEN('Initial Catalog='), 100), 0, 
        CHARINDEX(';', SUBSTRING([ICS], CHARINDEX('Initial Catalog=', [ICS]) + LEN('Initial Catalog='), 100)))
    FROM [TCC].[dbo].T
    WHERE ICS IS NOT NULL AND ICS <> ''

OPEN cr_i

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cr_i INTO @name

    IF @@fetch_status <> 0
        BREAK

    BEGIN TRANSACTION;
 
        ALTER TABLE [dbo].[IMP_IPID] 
            ADD [FSKB] int NOT NULL DEFAULT 0;

        INSERT INTO [__EFMH] ([MId], [PV])
        VALUES (N'some_value', N'other_value');

        COMMIT;
END

CLOSE cr_i
DEALLOCATE cr_i

sql-server

来源：https://stackoverflow.com/questions/76802645/sql-cursor-insert-into-many-databases-in-one-server

1条答案

按热度按时间

hiz5n14c1#

I suppose you should have:

DECLARE @name nvarchar(100), 
        @exec nvarchar(500),
        @c    cursor,
        @sql  nvarchar(max);

SET @sql = N'BEGIN TRANSACTION; 
    ALTER TABLE [dbo].[IMP_IPID] 
    ADD [FSKB] int NOT NULL DEFAULT 0;

    INSERT dbo.[__EFMH] ([MId], [PV])
    VALUES (N''some_value'', N''other_value'');
    COMMIT TRANSACTION;';

SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR 
SELECT SUBSTRING(SUBSTRING([ICS], CHARINDEX('Initial Catalog=', [ICS]) 
       + LEN('Initial Catalog='), 100), 0, 
    CHARINDEX(';', SUBSTRING([ICS], CHARINDEX('Initial Catalog=', [ICS]) 
       + LEN('Initial Catalog='), 100)))
FROM [TCC].[dbo].T WHERE ICS IS NOT NULL AND ICS <> '';

OPEN @c;
FETCH NEXT FROM @c INTO @name;

WHILE @@FETCH_STATUS <> -1
BEGIN
  IF DB_ID(QUOTENAME(@name)) IS NOT NULL
  BEGIN
    SET @exec = QUOTENAME(@name) + N'.sys.sp_executesql';
    EXEC @exec @sql;
  END
  FETCH NEXT FROM @c INTO @name;
END
-- do not need close / deallocate @c

You should probably add error handling to the SQL you're running too, e.g. making it idempotent (only adding the column if it doesn't already exist).

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL Cursor Insert into many databases in one server

1条答案

相关问题

热门标签

最新问答