Huge CTE - and log usage in simple mode.,

the below query still goes over the max of log space on disk..

this is a one time operation..

is there a way to break the above code to do in chunks so that the log does not go over (even though it is in simple mode)

there are 50M records in the main table.. so even if we can break into doing by 5M at a time, that will be good... is there a logic possible to break it into batches?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
the query logic is:


with replaceOrder as (
select
      KeyColumn,column1, column2,column3,column4,column5,
      key_id=row_number() over (order by KeyColumn),
      column1_from=row_number() over (order by newid()),
      column2_from=row_number() over (order by newid()),
      column3_from=row_number() over (order by newid()),
      column4_from=row_number() over (order by newid()),
      column5_from=row_number() over (order by newid())  
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = LEFT(c.column3,1)+substring(c.column3,charindex (',',c.column3)+2,1),
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
0
Scott PletcherSenior DBACommented:
I'd use a temp table rather than the CTE, maybe something like below.  Then, if needed, the final UPDATEs from the temp table to the main table could be done in batches.

Although I'd need to see sample data with results, the query plan and know the data types to be any more specific than this.


CREATE #replaceOrder (
    id int IDENTITY(1, 1) NOT NULL,
    KeyColumn <datatype> PRIMARY KEY NOT NULL,
    column1 <datatype> NULL,
    column2 <datatype> NULL,
    column3 <datatype> NULL,
    column4 <datatype> NULL,
    column5 <datatype> NULL,
    column1_from int NOT NULL,
    column2_from int NOT NULL,
    column3_from int NOT NULL,
    column4_from int NOT NULL,
    column5_from int NOT NULL
    )

INSERT INTO #replaceOrder (
    KeyColumn,
    column1, column2, column3, column4, column5,
    column1_from, column2_from, column3_from, column4_from, column5_from
    )
SELECT
      KeyColumn,
      column1, column2, column3, column4, column5,
      row_number() over (order by newid()),
      row_number() over (order by newid()),
      row_number() over (order by newid()),
      row_number() over (order by newid()),
      row_number() over (order by newid())  
from dbo.MainTable


UPDATE mt
SET
    ... = ...
FROM dbo.MainTable mt
INNER JOIN #replaceOrder ro ON
    ro.KeyColumn = mt.KeyColumn
0
25112Author Commented:
Scott, please see below for data type and preferred out put (basically jumbles the data beyond recognition.. below sample has only 6 rows.. but in real table, we will have 50M+ records).

---------------------


CREATE TABLE [dbo].[MainTable](
      [KeyColumn] [numeric](19,0) NULL,
      [column1] [varchar](75) NULL,
      [column2] [varchar](75) NULL,
      [column3] [varchar](85) NULL,
      [column4] [varchar](32) NULL,
      [column5] [datetime] NULL
) ON [PRIMARY]


insert into  [MainTable]
select 1,'abc1','bcd1','cde1','def1',GETDATE()-1 union
select 2,'abc2','bcd2','cde2','def2',GETDATE()-2 union
select 3,'abc3','bcd3','cde3','def3',GETDATE()-3 union
select 4,'abc4','bcd4','cde4','def4',GETDATE()-4 union
select 5,'abc5','bcd5','cde5','def5',GETDATE()-5 union
select 6,'abc6','bcd6','cde6','def6',GETDATE()-6
go

select * from MainTable;
with replaceOrder as (
select
      KeyColumn,column1, column2,column3,column4,column5,
      key_id=row_number() over (order by KeyColumn),
      column1_from=row_number() over (order by newid()),
      column2_from=row_number() over (order by newid()),
      column3_from=row_number() over (order by newid()),
      column4_from=row_number() over (order by newid()),
      column5_from=row_number() over (order by newid())  
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = c.column3,
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
select * from MainTable;
drop table MainTable
0
SharathData EngineerCommented:
May I know why do you want to do this? You may have duplicate entries in each column. Check the data in your table after the update for the sample records provided.
http://sqlfiddle.com/#!3/6678d/2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
scott, using temp table, i filled up tempdb and it quit after 30 minutes.

sharath, if i use CTE, log fills up and breaks.

any other way to have this contained?

if i put in bulk logged, will it help?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.