Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

cursored table copy

Posted on 2013-02-06
6
Medium Priority
?
168 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 2000 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

571 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