Syntax error in Cursor and dynamic SQL

Question

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

There is no db called @DB. Try print @Sql before executing. — SqlZim
– SqlZim, Commented Mar 23, 2017 at 19:13
When doing dynamic SQL like this, always make it a habit to comment out the EXEC(@SQL) statement, and replace it with a PRINT @SQL; statement. Then you can see the SQL you're producing, and easily spot any errors, or copy the output and paste into a query window and try to execute it to test if it's good. — pmbAustin
– pmbAustin, Commented Mar 23, 2017 at 19:16

Lamak · Accepted Answer · 2017-03-23 19:13:00Z

1

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;

answered Mar 23, 2017 at 19:13

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean Lange · Accepted Answer · 2017-03-23 20:05:35Z

0

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

edited Mar 23, 2017 at 20:05

answered Mar 23, 2017 at 19:55

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

2 Comments

abc Over a year ago

By the way, I am not sure "DataBaseNameForDataBaseID5" ? Do you mean: MASTER.SYS.DATABASESFORDatabaseID5 OR DatabasenameFORDatabaseID5? No space between FOR and database name? I mean the syntax issue.

Sean Lange Over a year ago

that is just the name of the database. I don't know what they are because your original code only had the databaseid. :) It is just 3 part naming. [DatabaseName].[SchemaName].[TableName] Does that make sense now?

Jayasurya Satheesh · Accepted Answer · 2017-03-23 20:26:11Z

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:

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)'

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.

Collectives™ on Stack Overflow

Syntax error in Cursor and dynamic SQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related