Transformed my query into a cursor

Question

Post reply

Transformed my query into a cursor

samirca007

SSC-Addicted

Points: 417

November 29, 2019 at 9:27 pm

#3702185

Hello everyone

I would like to put these insertion requests in a cursor

Who has an idea please

INSERT INTO Integration_ANTECEDENT_AUTO_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_ANTECEDENT_AUTO_Bulk

INSERT INTO Integration_ANTECEDENT_CYCLO_MRH_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_ANTECEDENT_CYCLO_MRH_Bulk


INSERT INTO Integration_ANTECEDENT_MOTO_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_ANTECEDENT_MOTO_Bulk



INSERT INTO Integration_APPORTEUR_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_APPORTEUR_Bulk

INSERT INTO Integration_CLAUSE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CLAUSE_Bulk


INSERT INTO Integration_COMMENTAIRE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_COMMENTAIRE_Bulk

INSERT INTO Integration_COMPAGNIE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_COMPAGNIE_Bulk

INSERT INTO Integration_CONDUCTEUR_1_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONDUCTEUR_1_Bulk

INSERT INTO Integration_CONDUCTEUR_2_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONDUCTEUR_2_Bulk

INSERT INTO Integration_CONDUCTEUR_3_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONDUCTEUR_3_Bulk
 
INSERT INTO Integration_CONTRAT_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONTRAT_Bulk

INSERT INTO Integration_CONTRAT_EMISSION_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONTRAT_EMISSION_Bulk

INSERT INTO Integration_CONTRAT_VENTIL_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_CONTRAT_VENTIL_Bulk

INSERT INTO Integration_EVENEMENT_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_EVENEMENT_Bulk


INSERT INTO Integration_FRANCHISE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_FRANCHISE_Bulk


INSERT INTO Integration_GARANTIE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_GARANTIE_Bulk


INSERT INTO Integration_GARANTIE_CONNECTEE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_GARANTIE_CONNECTEE_Bulk


INSERT INTO Integration_HABITATION_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_HABITATION_Bulk

INSERT INTO Integration_IBAN_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_IBAN_Bulk


INSERT INTO Integration_OPTION_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_OPTION_Bulk


INSERT INTO Integration_PIECE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_PIECE_Bulk


INSERT INTO Integration_PRODUIT_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_PRODUIT_Bulk


INSERT INTO Integration_QUITTANCE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_QUITTANCE_Bulk



INSERT INTO Integration_QUITTANCE_COMMENTAIRE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_QUITTANCE_COMMENTAIRE_Bulk


INSERT INTO Integration_SINISTRE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_SINISTRE_Bulk


INSERT INTO Integration_SOUSCRIPTEUR_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_SOUSCRIPTEUR_Bulk


INSERT INTO Integration_SOUSCRIPTION_DATE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_SOUSCRIPTION_DATE_Bulk

INSERT INTO Integration_USER_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_USER_Bulk

INSERT INTO Integration_VEHICULE_Bulk
SELECT * FROM [XXXXXXXXX].Distind_Importation_tampon.dbo.Integration_VEHICULE_Bulk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is your objective? What extra benefits do you think a cursor will give you?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

samirca007 SSC-Addicted Points: 417 More actions · Answer 2

i have an application that creates sql tables that ends pat xxxxx_bulk and i must automated the synchronization process

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

IF OBJECT_ID('dbo.INFGenerateInsertStatement','P') IS NULL BEGIN
    EXEC ('CREATE PROCEDURE dbo.INFGenerateInsertStatement AS')
END
GO
-- *****************************************************************************
-- Sample call
-- EXEC dbo.INFGenerateInsertStatement 'XXXXXXXXX', 'dbo', 'Integration%', 0
-- *****************************************************************************
ALTER PROCEDURE dbo.INFGenerateInsertStatement
(
    @DatabaseName sysname,
    @TableSchema sysname,
    @TableName   sysname,
    @GenerateNewIdentity bit = 0
) AS
BEGIN
    SET NOCOUNT ON
    DECLARE @Cursor cursor
    DECLARE @TABLE_SCHEMA sysname,
            @TABLE_NAME  sysname,
            @COLUMN_NAME sysname,
            @IsIdentity bit,
            @TableHasIdentity bit

    DECLARE @SAVE_TABLE_SCHEMA sysname = '',
            @SAVE_TABLE_NAME  sysname = '',
            @SAVE_COLUMN_NAME  sysname = '',
            @SAVE_IsIdentity  bit,
            @SAVE_TableHasIdentity  bit

    DECLARE @Insert nvarchar(MAX),
            @Select nvarchar(MAX)

    DECLARE @NewLine nvarchar(MAX) = CHAR(13) + CHAR(10)

    SET @Cursor = cursor FOR 
    SELECT QUOTENAME(c.TABLE_SCHEMA) TABLE_SCHEMA,  
           QUOTENAME(c.TABLE_NAME) TABLE_NAME,  
           QUOTENAME(c.COLUMN_NAME) COLUMN_NAME,  
           COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)),c.COLUMN_NAME, 'IsIdentity') IsIdentity,
           c2.HasIdentity
      FROM INFORMATION_SCHEMA.COLUMNS c
     CROSS APPLY(SELECT MAX(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c2.TABLE_SCHEMA) + '.' + QUOTENAME(c2.TABLE_NAME)),c2.COLUMN_NAME, 'IsIdentity')) HasIdentity
                   FROM INFORMATION_SCHEMA.COLUMNS c2
                  WHERE c2.TABLE_SCHEMA = c.TABLE_SCHEMA
                    AND c2.TABLE_NAME = c.TABLE_NAME) c2
     WHERE c.TABLE_SCHEMA LIKE @TableSchema
       AND c.TABLE_NAME LIKE @TableName
     ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION

    OPEN @Cursor
    FETCH NEXT FROM @Cursor INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @IsIdentity, @TableHasIdentity

    WHILE @@FETCH_STATUS = 0 BEGIN
        IF (@SAVE_TABLE_SCHEMA <> @TABLE_SCHEMA) OR (@SAVE_TABLE_NAME <> @TABLE_NAME) BEGIN
            SELECT @Insert='', @Select = ''
            IF (@TableHasIdentity = 1) AND (@GenerateNewIdentity = 0) BEGIN
                SET @Insert = @NewLine + 'SET IDENTITY_INSET ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ' ON'
            END 
            SET @Insert+= @NewLine + 'INSERT INTO ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ' (' 
            SET @Select = 'SELECT'
        END
    
        SELECT @SAVE_TABLE_SCHEMA = @TABLE_SCHEMA, 
               @SAVE_TABLE_NAME = @TABLE_NAME, 
               @SAVE_COLUMN_NAME = @COLUMN_NAME, 
               @SAVE_IsIdentity = @IsIdentity,
               @SAVE_TableHasIdentity = @TableHasIdentity

        FETCH NEXT FROM @Cursor INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @IsIdentity, @TableHasIdentity
        IF  @SAVE_IsIdentity = 0 OR @GenerateNewIdentity = 0 BEGIN
            SET @Select += ' ' + @SAVE_COLUMN_NAME
            SET @Insert += @SAVE_COLUMN_NAME
        END
        IF (@SAVE_TABLE_SCHEMA = @TABLE_SCHEMA) AND (@SAVE_TABLE_NAME = @TABLE_NAME) BEGIN
            IF @SAVE_IsIdentity = 0 OR @GenerateNewIdentity = 0 BEGIN
                SET @Select += ','
                SET @Insert += ', '
            END
        END ELSE BEGIN
            SET @Insert += ')'
            SET @Select += @NewLine + '  FROM ' + QUOTENAME(@DatabaseName) + '.' + @SAVE_TABLE_SCHEMA + '.' + @SAVE_TABLE_NAME + ';' + @NewLine
            IF (@SAVE_TableHasIdentity = 1) AND (@GenerateNewIdentity = 0) BEGIN
                SET @Select +='SET IDENTITY_INSET ' + @SAVE_TABLE_SCHEMA + '.' + @SAVE_TABLE_NAME + ' OFF' + @NewLine 
            END 
            PRINT @Insert
            PRINT @Select 
        END 

    END
    CLOSE @Cursor
    DEALLOCATE @Cursor

END
GO

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

samirca007 wrote:

i have an application that creates sql tables that ends pat xxxxx_bulk and i must automated the synchronization process

Synchronization process? Why wouldn't "replication" work for you?

I've also found the the duplication of data that people otherwise call "synchronization" is unnecessary. What is the reason you need to do so?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)