Using a cursor to loop through data in SQL

Austin Muts

7y905SQL

My @SQL is not executing because of my synatax,any help will be appreciated

DECLARE @SQL NVARCHAR(MAX) = '';
BEGIN
IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config;
CREATE TABLE #Config
(
[TempID] INT IDENTITY(1, 1),
[ID] NVARCHAR(36),
[DocID] NVARCHAR(36),
[FieldNo] INT,
[FieldName] NVARCHAR(450),
[DisplayName] NVARCHAR(450),
[Description] NVARCHAR(450),
[DefaultValue] NVARCHAR(450),
[DisplayFormat] NVARCHAR(450),
[MaxLength] INT,
[MinLength] INT,
[DataType] SMALLINT,
[ColumnName] NVARCHAR(450),
[BarcodeNo] INT,
[DataBehaviour] SMALLINT,
[ListID] NVARCHAR(36),
[MapToString] BIT,
[ScanIndex] INT,
[ScanRequired] INT,
[ValidationID] NVARCHAR(450),
[RegexValue] NVARCHAR(450),
[ValidationTrigger] SMALLINT,
[DecimalPrecision] INT,
[DBCanNull] BIT ,
[IsPrimary] BIT,
[IsAuditable] BIT,
[DBSchemaDefault] BIT,
[DBSchemaDefaultValue] NVARCHAR(450),
[Updated] DATETIME,
[UpdatedBy] NVARCHAR(450) ,
[Created] DATETIME,
[CreatedBy] NVARCHAR(450),
[SourceID] NVARCHAR(450)
)
INSERT INTO #Config VALUES (NEWID(), 'b5f3cf6e-190b-4132-b90e-d320414cfaee', 1, 'ApplicationID', 'Application_ID', 'Idx1', N'', N'', 30, 0, 0, N'ApplicationID', 0, 0, N'', 0, 0, 0, N'', NULL, 4, 0, 1, 0, 0, 0, NULL, CAST(N'2019-09-23 10:26:35.943' AS DateTime), N'1aa591f0-a2c1-41b3-815b-68e0f5ef9478', CAST('2019-09-23 10:19:25.197' AS DateTime), '1aa591f0-a2c1-41b3-815b-68e0f5ef9478', NULL)
SELECT * FROM #Config
DECLARE @TempID INT = 0;
DECLARE @ID NVARCHAR(36) = '';
DECLARE @DocID NVARCHAR(36) = '';
DECLARE @FieldNo INT = 0;
DECLARE @FieldName NVARCHAR(450) = '';
DECLARE @DisplayName NVARCHAR(450) = '';
DECLARE @Description NVARCHAR(450) = '';
DECLARE @DefaultValue NVARCHAR(450) = '';
DECLARE @DisplayFormat NVARCHAR(450) = '';
DECLARE @MaxLength INT = 0;
DECLARE @MinLength INT = 0;
DECLARE @DataType SMALLINT = 0;
DECLARE @ColumnName NVARCHAR(450) = '';
DECLARE @BarcodeNo INT = 0;
DECLARE @DataBehaviour SMALLINT = 0;
DECLARE @ListID NVARCHAR(36) = '';
DECLARE @MapToString BIT = '';
DECLARE @ScanIndex INT = 0;
DECLARE @ScanRequired INT = 0;
DECLARE @ValidationID NVARCHAR(450) = '';
DECLARE @RegexValue NVARCHAR(450) = '';
DECLARE @ValidationTrigger SMALLINT = 0;
DECLARE @DecimalPrecision INT = 0;
DECLARE @DBCanNull BIT = '';
DECLARE @IsPrimary BIT = '';
DECLARE @IsAuditable BIT = '';
DECLARE @DBSchemaDefault BIT = '';
DECLARE @DBSchemaDefaultValue BIT = '';
DECLARE @Updated DATETIME = '';
DECLARE @UpdatedBy NVARCHAR(450) = '';
DECLARE @Created DATETIME = '';
DECLARE @CreatedBy NVARCHAR(450) = '';
DECLARE @SourceID NVARCHAR(450) = '';
DECLARE cursor_document CURSOR
FOR SELECT
[TempID] ,
[ID] ,
[DocID] ,
[FieldNo] ,
[FieldName] ,
[DisplayName] ,
[Description] ,
[DefaultValue] ,
[DisplayFormat] ,
[MaxLength] ,
[MinLength] ,
[DataType] ,
[ColumnName] ,
[BarcodeNo] ,
[DataBehaviour] ,
[ListID] ,
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID] ,
[RegexValue] ,
[ValidationTrigger] ,
[DecimalPrecision] ,
[DBCanNull] ,
[IsPrimary] ,
[IsAuditable] ,
[DBSchemaDefault] ,
[DBSchemaDefaultValue] ,
[Updated] ,
[UpdatedBy] ,
[Created] ,
[CreatedBy] ,
[SourceID]
FROM
#Config;
OPEN cursor_document;
FETCH NEXT FROM cursor_document INTO
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CREATING DOCUMENT :' + @DocID + ' FROM : ' + CAST(@DocID AS VARCHAR);
SELECT @SQL = N'
INSERT INTO [dbo].[SysDoc]
([ID],
[DocID],
[FieldNo],
[FieldName],
[DisplayName],
[Description],
[DefaultValue],
[DisplayFormat],
[MaxLength],
[MinLength],
[DataType],
[ColumnName],
[BarcodeNo],
[DataBehaviour],
[ListID],
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID],
[RegexValue],
[ValidationTrigger],
[DecimalPrecision],
[DBCanNull],
[IsPrimary],
[IsAuditable],
[DBSchemaDefault],
[DBSchemaDefaultValue],
[Updated],
[UpdatedBy],
[Created],
[CreatedBy],
[SourceID]
SELECT
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID '
PRINT @SQL
EXEC @SQL
FETCH NEXT FROM cursor_document INTO
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID
END
CLOSE cursor_document;
DEALLOCATE cursor_document;
END

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jefferson S. Motta7y ago
Your INSERT syntax is not correct:

(check the order and number of the columns too).
INSERT INTO [dbo].[SysDoc]
([ID],
[DocID],
[FieldNo],
[FieldName],
[DisplayName],
[Description],
[DefaultValue],
[DisplayFormat],
[MaxLength],
[MinLength],
[DataType],
[ColumnName],
[BarcodeNo],
[DataBehaviour],
[ListID],
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID],
[RegexValue],
[ValidationTrigger],
[DecimalPrecision],
[DBCanNull],
[IsPrimary],
[IsAuditable],
[DBSchemaDefault],
[DBSchemaDefaultValue],
[Updated],
[UpdatedBy],
[Created],
[CreatedBy],
[SourceID]
)
VALUES (
SELECT
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID
)
Austin Muts7y ago
i sorted the syntax finally thanks.
Amit Mohanty7y ago
Hey try this
DECLARE @SQL NVARCHAR(MAX) = '';
BEGIN
IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config;
    CREATE TABLE #Config
    (
        [TempID] INT IDENTITY(1, 1),
        [ID] NVARCHAR(36),
        [DocID] NVARCHAR(36),
        [FieldNo] INT,
        [FieldName] NVARCHAR(450),
        [DisplayName] NVARCHAR(450),
        [Description] NVARCHAR(450),
        [DefaultValue] NVARCHAR(450),
        [DisplayFormat] NVARCHAR(450),
        [MaxLength] INT,
        [MinLength] INT,
        [DataType] SMALLINT,
        [ColumnName] NVARCHAR(450),
        [BarcodeNo] INT,
        [DataBehaviour] SMALLINT,
        [ListID] NVARCHAR(36),
        [MapToString] BIT,
        [ScanIndex] INT,
        [ScanRequired] INT,
        [ValidationID] NVARCHAR(450),
        [RegexValue] NVARCHAR(450),
        [ValidationTrigger] SMALLINT,
        [DecimalPrecision] INT,
        [DBCanNull] BIT ,
        [IsPrimary] BIT,
        [IsAuditable] BIT,
        [DBSchemaDefault] BIT,
        [DBSchemaDefaultValue] NVARCHAR(450),
        [Updated] DATETIME,
        [UpdatedBy] NVARCHAR(450) ,
        [Created] DATETIME,
        [CreatedBy] NVARCHAR(450),
        [SourceID] NVARCHAR(450)
        )
    INSERT INTO #Config VALUES (NEWID(), 'b5f3cf6e-190b-4132-b90e-d320414cfaee', 1, 'ApplicationID',
    'Application_ID', 'Idx1', N'', N'', 30, 0, 0, N'ApplicationID', 0, 0, N'', 0, 0, 0, N'', NULL,
    4, 0, 1, 0, 0, 0, NULL, CAST(N'2019-09-23 10:26:35.943' AS DateTime), N'1aa591f0-a2c1-41b3-815b-68e0f5ef9478',
    CAST('2019-09-23 10:19:25.197' AS DateTime), '1aa591f0-a2c1-41b3-815b-68e0f5ef9478', NULL)
    --SELECT * FROM #Config

    DECLARE @TempID INT = 0;
    DECLARE @ID NVARCHAR(36) = '';
    DECLARE @DocID NVARCHAR(36) = '';
    DECLARE @FieldNo INT = 0;
    DECLARE @FieldName NVARCHAR(450) = '';
    DECLARE @DisplayName NVARCHAR(450) = '';
    DECLARE @Description NVARCHAR(450) = '';
    DECLARE @DefaultValue NVARCHAR(450) = '';
    DECLARE @DisplayFormat NVARCHAR(450) = '';
    DECLARE @MaxLength INT = 0;
    DECLARE @MinLength INT = 0;
    DECLARE @DataType SMALLINT = 0;
    DECLARE @ColumnName NVARCHAR(450) = '';
    DECLARE @BarcodeNo INT = 0;
    DECLARE @DataBehaviour SMALLINT = 0;
    DECLARE @ListID NVARCHAR(36) = '';
    DECLARE @MapToString BIT = '';
    DECLARE @ScanIndex INT = 0;
    DECLARE @ScanRequired INT = 0;
    DECLARE @ValidationID NVARCHAR(450) = '';
    DECLARE @RegexValue NVARCHAR(450) = '';
    DECLARE @ValidationTrigger SMALLINT = 0;
    DECLARE @DecimalPrecision INT = 0;
    DECLARE @DBCanNull BIT = '';
    DECLARE @IsPrimary BIT = '';
    DECLARE @IsAuditable BIT = '';
    DECLARE @DBSchemaDefault BIT = '';
    DECLARE @DBSchemaDefaultValue BIT = '';
    DECLARE @Updated DATETIME = '';
    DECLARE @UpdatedBy NVARCHAR(450) = '';
    DECLARE @Created DATETIME = '';
    DECLARE @CreatedBy NVARCHAR(450) = '';
    DECLARE @SourceID NVARCHAR(450) = '';

    DECLARE cursor_document CURSOR READ_ONLY
    FOR SELECT
    [TempID],[ID],[DocID],[FieldNo],[FieldName],[DisplayName],[Description],[DefaultValue],[DisplayFormat],[MaxLength],[MinLength],[DataType],
    [ColumnName],[BarcodeNo],[DataBehaviour],[ListID],[MapToString],[ScanIndex],[ScanRequired],[ValidationID],[RegexValue],[ValidationTrigger],
    [DecimalPrecision],[DBCanNull],[IsPrimary],[IsAuditable],[DBSchemaDefault],[DBSchemaDefaultValue],[Updated],[UpdatedBy],[Created],[CreatedBy],
    [SourceID] FROM #Config;
    OPEN cursor_document;
    FETCH NEXT FROM cursor_document INTO
    @TempID,@ID,@DocID,@FieldNo,@FieldName,@DisplayName,@Description,@DefaultValue,@DisplayFormat,@MaxLength,@MinLength,@DataType,@ColumnName,
    @BarcodeNo,@DataBehaviour,@ListID,@MapToString,@ScanIndex,@ScanRequired,@ValidationID,@RegexValue,@ValidationTrigger,@DecimalPrecision,
    @DBCanNull,@IsPrimary,@IsAuditable,@DBSchemaDefault,@DBSchemaDefaultValue,@Updated,@UpdatedBy,@Created,@CreatedBy,@SourceID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'CREATING DOCUMENT :' + @DocID + ' FROM : ' + CAST(@DocID AS VARCHAR(50));
        SET @SQL = N'
        INSERT INTO [dbo].[SysDoc]
        ([ID],[DocID],[FieldNo],[FieldName],[DisplayName],[Description],[DefaultValue],[DisplayFormat],[MaxLength],[MinLength],
        [DataType],[ColumnName],[BarcodeNo],[DataBehaviour],[ListID],[MapToString],[ScanIndex],[ScanRequired],[ValidationID],[RegexValue],
        [ValidationTrigger],[DecimalPrecision],[DBCanNull],[IsPrimary],[IsAuditable],[DBSchemaDefault],[DBSchemaDefaultValue],[Updated],
        [UpdatedBy],[Created],[CreatedBy],[SourceID]) SELECT '''+
        @ID+''','''+@DocID+''','+CAST(@FieldNo AS VARCHAR)+','''+@FieldName+''','''+@DisplayName+''','''+@Description+''','''+@DefaultValue+''','''+
        @DisplayFormat+''','+CAST(@MaxLength AS VARCHAR)+','+CAST(@MinLength AS VARCHAR)+','+CAST(@DataType AS VARCHAR)+','''+@ColumnName+''','+
        CAST(@BarcodeNo AS VARCHAR)+','+CAST(@DataBehaviour AS VARCHAR)+','''+@ListID+''','''+IIF(@MapToString=1,'TRUE','FALSE')+''','+
        CAST(@ScanIndex AS VARCHAR)+','+CAST(@ScanRequired AS VARCHAR)+','''+@ValidationID+''','''+ISNULL(@RegexValue,'')+''',' +
        CAST(@ValidationTrigger AS VARCHAR)+','+CAST(@DecimalPrecision AS VARCHAR)+','+CAST(@DBCanNull AS VARCHAR)+','+CAST(@IsPrimary AS VARCHAR)+','+
        CAST(@IsAuditable AS VARCHAR)+','+CAST(@DBSchemaDefault AS VARCHAR)+','+CAST(ISNULL(@DBSchemaDefaultValue,'') AS VARCHAR)+','''+
        CAST(@Updated AS VARCHAR)+''','''+ @UpdatedBy+''','''+CAST(@Created AS VARCHAR)+''','''+@CreatedBy+''','''+ISNULL(@SourceID,'') +''')'
        PRINT @SQL
        EXEC @SQL
        FETCH NEXT FROM cursor_document INTO
        @TempID,@ID,@DocID,@FieldNo,@FieldName,@DisplayName,@Description,@DefaultValue,@DisplayFormat,@MaxLength,@MinLength,
        @DataType,@ColumnName,@BarcodeNo,@DataBehaviour,@ListID,@MapToString,@ScanIndex,@ScanRequired,@ValidationID,@RegexValue,
        @ValidationTrigger,@DecimalPrecision,@DBCanNull,@IsPrimary,@IsAuditable,@DBSchemaDefault,@DBSchemaDefaultValue,@Updated,
        @UpdatedBy,@Created,@CreatedBy,@SourceID
    END
    CLOSE cursor_document;
    DEALLOCATE cursor_document;
END
Jaimin Shethiya7y ago
Hello Austin,
Can you please try to While loop and also you can use Select into statement for insert a data instead of cursor.

Thanks
Austin Muts7y ago
Msg 203, Level 16, State 2, Line 219
The name '
INSERT INTO [dbo].[SysDoc]
([ID],
[DocID],
[FieldNo],
[FieldName],
[DisplayName],
[Description],
[DefaultValue],
[DisplayFormat],
[MaxLength],
[MinLength],
[DataType],
[ColumnName],
[BarcodeNo],
[DataBehaviour],
[ListID],
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID],
[RegexValue],
[ValidationTrigger],
[DecimalPrecision],
[DBCanNull],
[IsPrimary],
[IsAuditable],
[DBSchemaDefault],
[DBSchemaDefaultValue],
[Updated],
[UpdatedBy],
[Created],
[CreatedBy],
[SourceID]
SELECT
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxL' is not a valid identifier.
Salman Beg7y ago
Hi. What error you are getting?. I need to compile it because there are lots of line are present. Thanks.