Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Can this be done without using a cursor?

    I have a table that contains this kind of data:
    Code:
    Id	type		
    1	ShpmntHdr	
    2	msgHdr
    3	datatype1
    4	datatype2
    5	msgFtr
    6	msgHdr
    7	datatype5
    8	datatype7
    9	datatype1
    10	datatype13
    11	msgFtr
    ...
    211	ShpmntHdr	
    212	msgHdr
    213	datatype1
    215	msgFtr
    216	msgHdr
    217	datatype1
    218	datatype2
    219	datatype3
    220	msgFtr
    ...
    I need to add a FK to the table that references another table Msg.
    A Msg contains all the records from and including 'msgHdr', all the records (variable number) with any datatypeN until and including 'msgFtr'. The value of the Id column determines the order of the records.
    I need to INSERT a record in Msg, and use the Id of the newly created record as the value for the FK.

    The previous data should become:
    Code:
    Id	type		MsgId
    1	ShpmntHdr	
    2	msgHdr		1
    3	datatype1	1
    4	datatype2	1
    5	msgFtr		1	
    6	msgHdr		2
    7	datatype5	2
    8	datatype7	2
    9	datatype1	2
    10	datatype13	2
    11	msgFtr		2
    ...
    211	ShpmntHdr	
    212	msgHdr		35
    213	datatype1	35
    215	msgFtr		35
    216	msgHdr		36
    217	datatype1	36
    218	datatype2	36
    219	datatype3	36
    220	msgFtr		36
    ...
    I have a solution that uses a cursor, but is it possible to do it without using one?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there probably is, but my immediate reaction is "how often are you planning to do this?"

    looks like a one off
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Untested, but it should give you a place to start:
    Code:
    SELECT [id], [type]
    ,  (SELECT
          CASE
             WHEN h < 1 OR h = f THEN NULL
             ELSE h
          END
          FROM (SELECT
             Sum(CASE WHEN 'msgHdr' THEN 1 END) AS h
    ,        Sum(CASE WHEN 'msgFtr' THEN 1 END) AS f
             FROM myTable AS z
             WHERE  z.id <= a.id) AS z2) AS MsgId
       FROM myTable AS a
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    o wait, I think I got it, give me a few (if I don't get distracted that is)

    Isn't this the jist of what you need?

    Code:
    create table Msg
    (
      MsgId int not null identity(1,1)
    , ShpmntId int not null
    )
    
    insert 
    	Msg(ShpmntId)
    select
    	Id
    from 
    	Shpmnt
    where 
    	type = 'msgHdr'
    order by 
    	Id
    Then I think you just need to update the base table, right?

    Code:
    update
    	u
    set
    	u.MsgId = ml.MsgId
    from
    	Shpmnt u
    join	Msg ml
    	on	u.Id >= ml.ShpmntId
    join	Msg mh
    	on	mh.MsgId = ml.MsgId + 1
    	and	u.Id < mh.ShpmentId
    
    -- finally update the 1-off issue at the end
    declare @MsgId int
    select @MsgId = max(MsgId) from Msg
    update
    	u
    set
    	u.MsgId = @MsgId
    from 
    	Shpmnt u
    where
    	u.MsgId is null
    Last edited by Gagnon; 01-25-11 at 16:08.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you all for your reactions.

    Indeed, it is a one time problem. Our program will be adjusted so it will assign the MgsId's at the moment it fills the table, the next times it runs.
    But we are working on a database on which MS Dynamics runs. We see it launching countless sp's and are affright to introduce problems by locking tables, by using a cursor.
    It is a solution installed by an external firm, so we have no development or test environment, only the production system. And so we only have one shot.

    This is the solution I came up with, by combining the given suggestions, and a previous post by Gagnon.
    Code:
    CREATE TABLE #temp(
    Id	BIGINT	identity(1, 1) not NULL,
    type_	CHAR(10)	NOT NULL,
    msgId	BIGINT		NULL,
    CONSTRAINT pk_temp primary key (Id)
    )
    
    INSERT INTO #temp(type_) VALUES		
    ('ShpmntHdr'), 
    	('msgHdr'), ('datatype1'), ('datatype2'), ('msgFtr'), 
    	('msgHdr'), ('datatype5'), ('datatype7'), ('datatype1'), ('datatype13'), ('msgFtr'), 
    ('ShpmntHdr'), 
    	('msgHdr'), ('datatype10'), ('datatype11'), ('datatype12'), ('datatype13'), ('msgFtr'), 
    	('msgHdr'), ('msgFtr'), 
    	('msgHdr'), ('datatype20'), ('datatype21'), ('datatype22'), ('datatype23'), ('msgFtr'), 
    ('ShpmntHdr'), 
    	('msgHdr'), 
    	('datatype1'), 
    	('msgFtr'), 
    	('msgHdr'), 
    	('datatype1'), 
    	('datatype2'), 
    	('datatype3'), 
    	('msgFtr')
    
    select *
    from #temp
    
    
    DECLARE @MsgId	BIGINT
    DECLARE @LowerBoundTempId	BIGINT
    DECLARE @UpperBoundTempId	BIGINT
    
    SET @MsgId = 1
    
    SELECT @UpperBoundTempId = MIN(id) - 1
    FROM #temp 
    
    SELECT @LowerBoundTempId = MIN(id)
    FROM #temp 
    WHERE type_ = 'msgHdr' AND 
    	Id > @UpperBoundTempId
    
    SELECT @UpperBoundTempId = MIN(id)
    FROM #temp 
    WHERE type_ = 'msgFtr' AND 
    	Id > @LowerBoundTempId
    
    print '@@rowcount = ' + str(@@rowcount)
    print '@LowerBoundTempId = ' + str(@LowerBoundTempId)
    print '@UpperBoundTempId = ' + str(@UpperBoundTempId)
    
    
    UPDATE #temp
    SET msgId = @msgId
    WHERE Id between @LowerBoundTempId AND @UpperBoundTempId
    	
    WHILE (@@rowcount > 0)
    BEGIN
    
    	SET @MsgId = @MsgId + 1
    
    	SELECT @LowerBoundTempId = MIN(id)
    	FROM #temp 
    	WHERE type_ = 'msgHdr' AND 
    		Id > @UpperBoundTempId
    
    	SELECT @UpperBoundTempId = MIN(id)
    	FROM #temp 
    	WHERE type_ = 'msgFtr' AND 
    		Id > @LowerBoundTempId
    
    	print '@msgId = ' + str(@msgId)
    	print '@LowerBoundTempId = ' + str(@LowerBoundTempId)
    	print '@UpperBoundTempId = ' + str(@UpperBoundTempId)
    
    	UPDATE #temp
    	SET msgId = @msgId
    	WHERE Id between @LowerBoundTempId AND @UpperBoundTempId
    END
    
    INSERT INTO MSG(id)
    SELECT DISTINCT MsgId
    FROM #temp
    I am not very sure if our fear for locks due to the use of cursors is justified. Will the above solution introduce locks on itself?
    I haven't used MSSQL for long enough to be able to compare both solutions in that regard.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •