I have come to this forum seeking suggestions/expertise on the below issue I am facing.
I need to transfer data from one table to another.
To explain it more, I need transfer rows from a table (named holdings table) where sourcecode=100 AND date=getdate() to another table. The holdings table has an identity column on it called holdingId and has clustered index on this id column.
The SP currently selects all the relevant rows into a temp table and then in a while loop, select 5000 rows from this temp table using identity column created on this temp table. Something like
declare @min int
while ( @@rowcount>0)
set @min = @min+5000
That SP now needs to be marked for replication. The problem is that creation of temp table is not allowed in SPs that are replicated.
Please advise me on how this can be done without using any temp table. Also let me know if you need to more info on this or I am not clear.
You can put all the statements in a procedure and mark the procedure for replication. while the procedure is called at primary instead of sending the ddl the procedure will be executed in the standby server.