Solved

cursored table copy

Posted on 2013-02-06
6
150 Views
Last Modified: 2013-05-06
I really don't care for cursors, but I need something yesterday, so I am going to go this route for now, until I can spend more time on a non-cursor solution.  

Simple objective -- cursor through a list of tables, copying data from serverA to local serverB, where not exists.  It's about 55 tables, 13 of which have IDENTITY columns.  I need to use the @SetIdentityInsON and @SetIdentityInsOFF variables, where applicable.

This is run by a job, and is coded to do all tables in a list, without condition.  There are five tables that I need to use a datestamp on, to minimize the lookup.  I look at only current day for those five tables.  This job is to run daily, so under normal circumstances, this is perfectly suitable.  

But, @TableName and @When can be passed in, if desired, possibly to target only one table, or recover from a failure, or anything of that nature.

 See the first @debug statement -- the output of that is correct.  All tables are addressed correctly, in all regards.

If I attempt to debug a single table, such as my 2nd or 3rd statement below, it still processes ALL tables.  And, if the table I list specifically in the statment does NOT have an IDENTITY column, the returned statment is setting IDENTITY_INSERT on and off.  But remember, when I run the @debug without any tables, ALL statments are processed and returned successfully.  To include the IDENTITY INSERT on and off, the date parms, both where applicable, and then everything else.

Anybody see it?



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ProcName] (
 @TableName NVARCHAR(64) = NULL,
 @When DATETIME = NULL,
 @Debug bit = 0
)
AS
SET NOCOUNT ON;
/*
Allows for the copy of data in from production to the XX database.
EXEC dbo.ProcName @debug = 1
EXEC dbo.ProcName @TableName ='oneTable', @Debug = 1
EXEC dbo.ProcName @TableName ='anotherTable', @Debug = 1

Auth:  ME
Date:  xx/xx/xxxx
*/
BEGIN
      BEGIN TRY

      DECLARE @RecordCount BIGINT
      DECLARE @SQLInsert NVARCHAR(1000)
      DECLARE @SQLSelect NVARCHAR(2500)
      DECLARE @SQLWhere NVARCHAR(500)

      /* Start time, defaults to current day, can be given.  Useful for catchup, if the task failed. */
      IF(@When IS NULL)
      BEGIN
            SET @When = CONVERT(CHAR(8),GETDATE(),112)
      END
      
      /* TABLE VARIABLE TO STORE COUNTS */
      DECLARE @Counts TABLE (
            CopyDate DATETIME NOT NULL DEFAULT (GETDATE()),
            TableName [VARCHAR](100),
            RecordsCopied [INT]
            )

      /* Placeholder for each table. */
      DECLARE @Table VARCHAR(100)
      DECLARE @IsIdentity BIT
      
      /* Get table details cursor */            
      DECLARE TablesCursor CURSOR FOR
      SELECT t.name, c.is_identity
      FROM sys.Tables t INNER JOIN sys.indexes i
        ON t.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.index_columns ic
          ON i.index_id = ic.index_id
          AND t.OBJECT_ID = ic.OBJECT_ID INNER JOIN sys.columns c
           ON c.OBJECT_ID = t.OBJECT_ID
           AND c.column_id = ic.column_id
      WHERE
            t.name IN(
                  '... list of 55 tables, 13 of which have IDENTITY columns...'
                  )
      GROUP BY
            t.name,
            c.is_identity
      ORDER BY
            t.name

      
      OPEN TablesCursor
      FETCH NEXT FROM TablesCursor INTO @Table,@IsIdentity
      WHILE @@FETCH_STATUS = 0
      BEGIN
            
            /* Set IDENTITY_INSERT ON and OFF, if needed. */
            IF(@IsIdentity = 1)
            BEGIN
                  DECLARE @SetIdentityInsON NVARCHAR(100) = 'SET IDENTITY_INSERT dbo.' + COALESCE(@TableName,@Table) + ' ON'
                  DECLARE @SetIdentityInsOFF NVARCHAR(100) = 'SET IDENTITY_INSERT dbo.' + COALESCE(@TableName,@Table) + ' OFF'
            END
            
            /* get column list per table for INSERT dest line */
            SELECT @SQLInsert = ' INSERT dbo.' + COALESCE(@TableName,@Table) + ' ('
            SELECT @SQLInsert = @SQLInsert + STUFF((SELECT N', ' + c.name
            FROM sys.Tables t INNER JOIN sys.columns c
             ON c.OBJECT_ID = t.OBJECT_ID
            WHERE t.name = COALESCE(@TableName,@Table)
            ORDER BY c.column_id
            FOR XML PATH('')),1, 2,'')
                         
          SELECT  @SQLInsert = @SQLInsert + ')'
                         
            /* get column list per table for SELECT FROM src line */                        
            SELECT  @SQLSelect = 'SELECT '
            SELECT  @SQLSelect = @SQLSelect + STUFF((SELECT N', ' + c.name
            FROM sys.Tables t INNER JOIN sys.columns c
             ON c.OBJECT_ID = t.OBJECT_ID
            WHERE t.name = COALESCE(@TableName,@Table)
            ORDER BY c.column_id
            FOR XML PATH('')),1, 2,'')

            SELECT  @SQLSelect = @SQLSelect + ' FROM servername.dbo.' + COALESCE(@TableName,@Table) + ' src '


            /* Customized WHERE clause for a couple special tables where I need to minimize the lookup. */
            IF(COALESCE(@TableName,@Table)) IN('table1','table2','table3')
            BEGIN
                  SELECT @SQLWhere = ' WHERE src.InsertDate >=  ' + '''' + CONVERT(varchar(20),@When,120) +''' AND NOT EXISTS(SELECT 1 FROM dbo.' + COALESCE(@TableName,@Table) + ' dest WHERE dest.InsertDate >= ''' + CONVERT(varchar(20),@When,120) +  +''' AND '
            END
            ELSE
            IF(COALESCE(@TableName,@Table)) = 'table4'
            BEGIN
                  SELECT @SQLWhere = ' WHERE src.DateAdded >=  ' + '''' +  CONVERT(varchar(20),@When,120) +''' AND NOT EXISTS(SELECT 1 FROM dbo.' + COALESCE(@TableName,@Table) + ' dest WHERE dest.DateAdded >= ''' + CONVERT(varchar(20),@When,120) +''' AND '
            END
            ELSE
            IF(COALESCE(@TableName,@Table)) = 'table5'
            BEGIN
                  SELECT @SQLWhere = ' WHERE src.Created >= ' + '''' +  CONVERT(varchar(20),@When,120) +''' AND NOT EXISTS(SELECT 1 FROM dbo.' + COALESCE(@TableName,@Table) + ' dest WHERE dest.Created >=  ''' + CONVERT(varchar(20),@When,120)  +''' AND '
            END
            ELSE
            -- NON-Custom WHERE clause for the rest of the tables
            BEGIN
                  SELECT  @SQLWhere = ' WHERE NOT EXISTS(SELECT 1 FROM dbo.' + COALESCE(@TableName,@Table) + ' dest WHERE'
            END

            SELECT  @SQLWhere = @SQLWhere + STUFF((SELECT N' AND src.' + c.name + N' = dest.' + c.name       
            FROM sys.Tables t INNER JOIN sys.indexes i
             ON t.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.index_columns ic
              ON i.index_id = ic.index_id
              AND t.OBJECT_ID = ic.OBJECT_ID INNER JOIN sys.columns c
               ON c.OBJECT_ID = t.OBJECT_ID
               AND c.column_id = ic.column_id
            WHERE i.is_primary_key = 1
            AND t.name = COALESCE(@TableName,@Table)
            ORDER BY ic.column_id
            FOR XML PATH('')),1, 4,'')

            SELECT @SQLWhere = @SQLWhere + ')'


            /* Use @SetIdentityInsON / @SetIdentityInsOFF, if necessary. */
            DECLARE @SQLStmt NVARCHAR(MAX)

            /* Debug, or not */
            IF(@Debug = 1)
            BEGIN
                  IF(@IsIdentity = 1)
                  BEGIN
                        SET @SQLStmt = @SetIdentityInsON + @SQLInsert + @SQLSelect + @SQLWhere + @SetIdentityInsOFF
                        SELECT (@SQLStmt)
                  END
                  ELSE
                  IF(@IsIdentity = 0)
                  BEGIN
                        SET @SQLStmt = @SQLInsert + @SQLSelect + @SQLWhere
                        SELECT (@SQLStmt)
                  END
            END
            ELSE
            BEGIN
                  IF(@IsIdentity = 1)
                  BEGIN
                        SET @SQLStmt = @SetIdentityInsON + @SQLInsert + @SQLSelect + @SQLWhere + @SetIdentityInsOFF
                        EXEC (@SQLStmt)
                  END
                  ELSE
                  IF(@IsIdentity = 0)
                  BEGIN
                        SET @SQLStmt = @SQLInsert + @SQLSelect + @SQLWhere
                        EXEC (@SQLStmt)
                  END
            END


            SET @RecordCount = @@ROWCOUNT
            INSERT @Counts (TableName,RecordsCopied)
            SELECT COALESCE(@TableName,@Table),@RecordCount
            
                        
            /* Log record counts per table. */
            INSERT dbo.AuditTable (TableName, RecordCount)
            SELECT
                  TableName,
                  RecordsCopied
            FROM
                  @Counts
            ORDER BY
                  TableName            

            DELETE @Counts
            
            FETCH NEXT FROM TablesCursor INTO @TableName, @IsIdentity
      END

      CLOSE TablesCursor
      DEALLOCATE TablesCursor

      END TRY
      BEGIN CATCH
            EXEC dbo.errorProcedure;
            CLOSE TablesCursor
            DEALLOCATE TablesCursor
      END CATCH
END

SET NOCOUNT OFF;

GO
0
Comment
Question by:dbaSQL
  • 5
6 Comments
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
something odd...i can't get into that cursored table list distinctly.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
COALESCE(@TableName,@Table)

I also should have said I am doing that just to allow for the intermittent manual execution, where the DBA will pass in a @TableName.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
it looks basically ok

BUT why not just use snapshot replication if you want to "publish" the tables once per day...

i'd have put square brackets around the column/table names as i generated the lists...

you should read/obtain and used the schema names from the source system...

are you making an assumption that the identity column will always be used/present in the index this need not be the case...
are  you also assuming that there will only be 1 index per table....
shouldn't you have a max() around the is_identity?

you seem to have overly complicated the debugging ...
surely you should format the commands once and then test the debug flag to see
if you need to display or execute (or both...)?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
hi lowfat.

this is a temporary need.  i do not want to use replication for something that we'll need one day, and maybe not the next

i will do the brackets

unsure why the schema name is necessary, please clarify

the identity column, if exists, is not always present in the index/pk.  the only assumption i am trying to do with the identity column is -- does it exist, if so, set it on and off on the insert

why max it?

i wasn't sure on the debugging.  initially it was just the select or exec.  that's all i want with the debug -- see it or execute it.  one table or all.  but, when i couldn't figure out how to ONLY reference one table, i changed it to also handle my SetIdentityInsON and OFF.  i can pull this out.

the problem is -- when i execute this, the results are correct:
EXEC dbo.ProcName @debug = 1

Yet when I execute it with a table name, I get ALL tables.
If the tablename i pass does NOT have an identity, the tablename i passed is returned first, with IDENTITY ON and OFF.  All of the rest of the tables are returned (when I would only expect the one I pass in).  They are correct.  AND the one I passed in, is also returned down in the list where it is in the order, and it is correct.

did that make sense?

if i pass this:
EXEC dbo.ProcName @TableName ='oneTable', @Debug = 1

Where 'oneTable' does NOT have an identity column -

The 'oneTable' is returned first in the list, with IDENTITY on and off.
The rest of the tables are returned after 'oneTable', correctly.
The 'oneTable' is listed again, down in the list, correctly.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
hey lowfat, did you see this last message?
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
My apologies for the delay.  The cursor was not checking if my tablename parm was passed in.  It was opening for the whole list, and then just acting on that list.  other than that, it is ok.  thank you for looking, lowfat.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now