Solved

cursored table copy

Posted on 2013-02-06
6
152 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
ID: 38859917
something odd...i can't get into that cursored table list distinctly.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38859922
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
ID: 38860182
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 17

Author Comment

by:dbaSQL
ID: 38860262
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
ID: 39029301
hey lowfat, did you see this last message?
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 39142247
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

19 Experts available now in Live!

Get 1:1 Help Now