CODEWITHSUNDEEP
sql-serversyntaxcursordynamic-sql
abc
abc

Reputation: 11

Syntax error in Cursor and dynamic SQL

My following code gives error: (SQL Server 2008R2; Fetch two database name in cursor and then insert data to Table1 in two databases)

DECLARE @SQL NVARCHAR(max)
DECLARE @DB  VARCHAR(50)

DECLARE CUR_DB CURSOR FAST_FORWARD FOR 
        SELECT NAME FROM MASTER.SYS.DATABASES
        WHERE DATABASE_ID IN ('5', '81')
;

OPEN CUR_DB;
FETCH NEXT FROM CUR_DB INTO @DB;

WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @SQL = 'INSERT INTO @DB.dbo.Table1 VALUES (100, ''abc'', def'', 0)'
    EXEC(@SQL)
    FETCH NEXT FROM CUR_DB INTO @DB 
END;

CLOSE CUR_DB;
DEALLOCATE CUR_DB;

Error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'.

Upvotes: 0

Views: 1081

Answers (3)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Please try the below script

DECLARE @SQL NVARCHAR(max)
DECLARE @DB  VARCHAR(50)

DECLARE CUR_DB CURSOR FAST_FORWARD FOR 
    SELECT NAME FROM MASTER.SYS.DATABASES
    WHERE DATABASE_ID IN ('5', '81');

OPEN CUR_DB;
FETCH NEXT FROM CUR_DB INTO @DB;

WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @SQL = 'INSERT INTO ['+@DB+'].[dbo].[Table1] VALUES (100,''abc'',''def'', 0)'
    EXEC( @SQL)
    FETCH NEXT FROM CUR_DB INTO @DB 
END;

CLOSE CUR_DB;
DEALLOCATE CUR_DB;

There were 2 Issues in the script:

  1. You were using the parameter name inside the Query string

    'INSERT INTO **@DB**.dbo.Table1 VALUES (100, ''abc'', def'', 0)'

this will cause an error because the EXEC statement runs all the statements given inside the brackets as a separate batch and none of the parameters that you declared before that comes outside the scope of the EXEC statement. So Instead change it as follow

     SELECT @SQL = 'INSERT INTO **['+@DB+']**.[dbo].[Table1] VALUES (100, ''abc'', ''def'', 0)'

  1. There were 2 single quotes missing before the before the value def

     VALUES (100, ''abc'', **def''**, 0)'

So I just added that.

Hope that helps.

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Why are you using a cursor for this? You have limited this to only two databases. Why not just code this as two insert statements and forget all the craziness with a cursor. This entire cursor structure is really this simple.

INSERT INTO DataBaseNameForDataBaseID5.dbo.Table1 VALUES (100, 'abc', 'def', 0)
INSERT INTO DataBaseNameForDataBaseID81.dbo.Table1 VALUES (100, 'abc', 'def', 0)

---EDIT---

Since you say you need to do this on more than those two databases you still don't need to resort to cursors for this kind of thing. Take a look at this. It utilizes dynamic sql but doesn't need a loop.

declare @SQL nvarchar(MAX) = N''

select @SQL = @SQL + N'INSERT INTO ' + QuoteName(name) + N'.dbo.Table1 VALUES (100, ''abc'', ''def'', 0);'
from sys.databases
where DATABASE_ID IN (5, 81) --No quotes here, these are integers, not strings

exec sp_executesql @SQL

Upvotes: 0

Lamak
Lamak

Reputation: 70638

You aren't concatenating the name of the database in your dynamic SQL:

DECLARE @SQL NVARCHAR(max)
DECLARE @DB  VARCHAR(50)

DECLARE CUR_DB CURSOR FAST_FORWARD FOR 
        SELECT NAME FROM MASTER.SYS.DATABASES
        WHERE DATABASE_ID IN ('5', '81')
;

OPEN CUR_DB;
FETCH NEXT FROM CUR_DB INTO @DB;

WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @SQL = 'INSERT INTO ' + QUOTENAME(@DB) + '.dbo.Table1 VALUES (100, ''abc'', def'', 0)'
    EXEC(@SQL)
    FETCH NEXT FROM CUR_DB INTO @DB 
END;

CLOSE CUR_DB;
DEALLOCATE CUR_DB;

Upvotes: 1

Related Questions