none
SQL08 - insert/select order by behavior RRS feed

  • Question

  • Good morning,

    There is the blog post from SQL Server Engine Team related with SQL 05 behavior: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

    Is #4 from there remains the same in SQL 08 and expected to be the same in the future versions?

    Here is what I'm trying to accomplish:

    I have the set of the records submitted from the client in TVP. I need to split and insert the data into 2 tables with identity PK where one table references another. Unfortunately input data does not have any unique attributes I can use for the join. So I wonder if I can rely on the following code.

    Thanks,
    Dmitri

    create table A (
    	A_ID int not null identity,
    	Field1 int
    )
    
    create table B(
    	B_ID int not null identity,
    	Field2 int,
    	A_ID int foreign key references A(A_ID)
    )
    
    create type tvpInput as table
    (
    	InternalId smallint, -- Pseudo-identity populated on the client: 1,2,3...
    	Field1 int,
    	Field2 int
    )
    
    create proc InsertData (@Data tvpInput)
    as
    begin
    	declare
    		@Ids table (InsertedId int)
    	
    	insert into A(Field1)
    	output inserted.A_ID into @Ids(InsertedId)
    		select Field1
    		from @Data
    		order by InternalId
    		option (MAXDOP 1)
    
    	;with AIds(InternalId, InsertedId)
    	as
    	(
    		select Row_Number() over(order by InsertedId) as InternalId, InsertedId
    		from @Ids
    	)
    	insert into B(Field2, A_ID)
    		select d.Field2, AIds.InsertedId
    		from @Data d join AIds on d.InternalId = AIds.InternalId
    end
    
    Thursday, December 24, 2009 3:22 PM

Answers

  • If you need to have the foreign key constraint, then two solutions:

    1). You can use INSERT...EXEC to execute the MEGRE statement as dynamic SQL and insert the results. Since it runs as separate statement executed via dynamic SQL the foreign key constraint is not a problem
    2). You can simply use two statements, first the MERGE and insert the results to table variable, then insert into table B.
    Plamen Ratchev
    Thursday, December 24, 2009 5:38 PM
    Moderator

All replies

  • If you remove the foreign key constraint in table B then you can use composable DML in SQL Server 2008 (http://pratchev.blogspot.com/2008/04/composable-dml.html):

    CREATE PROCEDURE InsertData 
      @Data tvpInput READONLY
    AS
    	DECLARE @Ids table (InsertedId int);
    	
    	INSERT INTO B (Field2, A_ID)
    	SELECT field2, a_id
    	FROM (MERGE INTO A
    	      USING @Data AS D
    	         ON A.A_ID IS NULL
    	      WHEN NOT MATCHED
    	      THEN INSERT (field1) VALUES(D.field1)
    	      OUTPUT Inserted.A_ID, D.Field2) AS S(a_id, field2);

    Plamen Ratchev
    Thursday, December 24, 2009 5:32 PM
    Moderator
  • If you need to have the foreign key constraint, then two solutions:

    1). You can use INSERT...EXEC to execute the MEGRE statement as dynamic SQL and insert the results. Since it runs as separate statement executed via dynamic SQL the foreign key constraint is not a problem
    2). You can simply use two statements, first the MERGE and insert the results to table variable, then insert into table B.
    Plamen Ratchev
    Thursday, December 24, 2009 5:38 PM
    Moderator
  • If you need to have the foreign key constraint, then two solutions:

    1). You can use INSERT...EXEC to execute the MEGRE statement as dynamic SQL and insert the results. Since it runs as separate statement executed via dynamic SQL the foreign key constraint is not a problem
    2). You can simply use two statements, first the MERGE and insert the results to table variable, then insert into table B.
    Plamen Ratchev

    Thank you very much, Plamen! Obviously the real code is much more complicated than the example I provided, but solution #2 would work for me.
    Monday, December 28, 2009 3:05 PM