Chris, not bad at all. Your new script runs in just 2' 18'' on the same sample (vs 1h 3' of the previous version). I liked your trick with ColCount;...

Hi Chris,

Its a smart and elegant solution that indeed produces right results.

However, I was (and I still am) somewhat sceptical about keeping all these data in strings in ram. At...

The reason is that the IDs of tblMaster and the already existing IDs of prod.tblMaster may be the same.

Hi,

Columns uniquely identifying collaterals are: CollateralID + CollateralType

Columns uniquely identifying facilities are: ObligorID, AccountID, FacilityTypeID

@chris-2: I have included the above in my posts #862954 (see PK columns) and...

Hi Chris,

Thank you very much once more. Indeed the problem seems trivial but I am stuck with it more than a month now. I don’t believe that a broader sample...

It seems that this code has an error: SET @VALID_FROM_DATE1= (nothing)

Try:

SET @VALID_FROM_DATE1='19910102'

SET @SQL_COSTCENTER='SELECT COST_CENTER,'+CAST(@VALID_FROM_DATE1 AS NVARCHAR(50))+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM INBOUND_SAP_COST_CENTERS_FIN_APPR WITH(NOLOCK) '

Try to alter the procedure first and then excecute it

pink_panther (2/10/2010)

---

I'll give it one last shot:

-- Create a temporary table for the assignments (old ID, new ID)

SELECT CAST(0 AS BIGINT) AS ID, CAST(0 AS BIGINT) AS PrevID, CAST(0 AS...

I'll give it one last shot:

-- Create a temporary table for the assignments (old ID, new ID)

SELECT CAST(0 AS BIGINT) AS ID, CAST(0 AS BIGINT) AS PrevID, CAST(0 AS BIGINT)...

A modified version after Willem's note (thanks Willem):

INSERT INTO

prod.tblMaster

OUTPUT

INSERTED.ID AS ID,

tblMaster.ID AS PrevID

INTO #t

SELECT

*

FROM

tblMaster

INSERT INTO

prob.tblChild

SELECT

tblChild.Column1,

tblChild.Column2,

... ...

Sorry, I didn't notice that the connection was based on an identity column.

In this case try the following:

ALTER TABLE prod.tblMaster ADD PrevID BIGINT -- Or whatever

INSERT INTO

prod.tblMaster

SELECT

tblMaster.Column1,

tblMaster.Column2,

... ...

You could simply run:

INSERT INTO prob.tblMaster SELECT * FROM tblMaster

INSERT INTO prob.tblChild SELECT * FROM tblChild

Lynn I apologize for not being specific in my previous posts. Below is a script for creating the above three tables along with some data. I also include one more...

Hi Chris,

Actually zzzCovers table is not a real table in the database, I used it just to describe the issue isolated from the rest of the process. Let me explain...

Thank you both for your reply.

Lutz, I followed your advice and tried to get the execution plan, however, because of the two while loops which produce queries, the size of...