locked
how to insert with increment value? RRS feed

  • Question

  • I have 2 tables - abc, def

    abc has all the data.

    def has new data. I need to insert all data from def to abc. However, data with fiscalyear 1617, I want a new id on incrementid.

    Below is some script to generate the sample. I'm trying to insert abcid 7, 8, 9  into abc with incrementid 5, 6 for the 2 rows in def with fiscalyear 1617


    /* create test data for orders */ CREATE TABLE #abc ( abcId INT ,fiscalyr VARCHAR(4) ,incrementid INT ) INSERT INTO #abc VALUES (1,'1314',1); INSERT INTO #abc VALUES (2,'1415',1); INSERT INTO #abc VALUES (3,'1516',1); INSERT INTO #abc VALUES (4,'1314',2); INSERT INTO #abc VALUES (5,'1415',3); INSERT INTO #abc VALUES (6,'1516',4); CREATE TABLE #def (abcid int, fiscalyear VARCHAR(4)); INSERT INTO #def VALUES (7, 1617); INSERT INTO #def VALUES (8, 1617); INSERT INTO #def VALUES (9, 1415);

    The following is the insert statement, i'm not getting an error, but, went into infinite loop

    DECLARE @RowsCount int, @incid INT;
    SELECT @RowsCount = COUNT(*) FROM #def WHERE fiscalyear = 1617;
    WHILE @RowsCount > 0
    BEGIN
    INSERT INTO #abc (abcid, fiscalyr, incrementid )
    SELECT abcid, fiscalyear, @incid
    FROM #def
    SELECT @incid = @incid + 1;
    END

    This is the part of the result after I cancel the execution and do select * from #abc

    1    1314    1
    2    1415    1
    3    1516    1
    4    1314    2
    5    1415    3
    6    1516    4
    7    1617    NULL
    8    1617    NULL
    9    1415    NULL
    7    1617    NULL
    8    1617    NULL
    9    1415    NULL
    7    1617    NULL
    ...

    1) it shouldn't be in an infinite loop

    2) it should just insert 2 rows with 1617 fiscalyear with the following results:

    1    1314    1
    2    1415    1
    3    1516    1
    4    1314    2
    5    1415    3
    6    1516    4
    7    1617    5
    8    1617    6


    Advise? Thanks!

    • Edited by Ami2013 Thursday, December 31, 2015 8:08 PM
    Thursday, December 31, 2015 8:00 PM

Answers

  •  
    
    CREATE TABLE #abc (abcId int, fiscalyr VARCHAR(10),incrementid INT,  city VARCHAR(20), state  VARCHAR(20), country  VARCHAR(20))
     
    insert #abc (abcid, fiscalyr, incrementid, city, [state], country) values
    (1, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (2, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (3, '1314', 2, 'Portland', 'OR', 'USA'),
    (4, '1415', 3, 'Portland', 'OR', 'USA')
     
    
    CREATE TABLE #def (defid int, fiscalyr  VARCHAR(20),  city  VARCHAR(20), [state]  VARCHAR(20), country  VARCHAR(20))
     
    insert #def(defid, fiscalyr, city, [state], country) values
    (1, '1314', 'Seattle', 'WA', 'USA'),
    (2, '1516', 'Seattle', 'WA', 'USA')
     
    
    ;with mycte as (
    select abcid, fiscalyr, incrementid, city, state, country, 1 as lvl 
    from #abc
    union all
    select defid, fiscalyr, null as incrementid, city, state, country , 2 as lvl 
    from #def)
    
    ,mycte2 as (
    Select lvl, row_number() Over(Order by lvl, abcid) rn, fiscalyr,  city, state, country   
     ,dense_rank()Over(Order by  fiscalyr,city desc,state,country ) incrementid
    from mycte )
    
    
    Insert INTO   #abc (abcid, fiscalyr, incrementid, city, state, country)
    
    Select rn as abcid, fiscalyr,incrementid,  city, state, country 
    from mycte2 
    where lvl=2
    
    
    select * from #abc
    
     
    drop table #abc, #def

    • Proposed as answer by pituachMVP Friday, January 8, 2016 7:47 PM
    • Marked as answer by Eric__Zhang Thursday, January 21, 2016 10:39 AM
    Friday, January 8, 2016 7:20 PM

All replies

  • Does this get you where you want to be?

    DECLARE @abc TABLE (
    		abcId INT
    		,fiscalyr VARCHAR(4)
    		,incrementid INT
    		)
    INSERT INTO @abc VALUES (1,'1314',1); 
    INSERT INTO @abc VALUES (2,'1415',1); 
    INSERT INTO @abc VALUES (3,'1516',1); 
    INSERT INTO @abc VALUES (4,'1314',2); 
    INSERT INTO @abc VALUES (5,'1415',3); 
    INSERT INTO @abc VALUES (6,'1516',4); 	
    
    DECLARE @def TABLE (abcid int, fiscalyear VARCHAR(4));
    INSERT INTO @def VALUES (7, 1617);
    INSERT INTO @def VALUES (8, 1617);
    INSERT INTO @def VALUES (9, 1415);
    
    INSERT INTO @abc ( abcId, fiscalyr, incrementid )
    SELECT abcID, fiscalyear, ROW_NUMBER() OVER (ORDER BY abcid) + ((SELECT MAX(abcid) FROM @abc)-2) AS newIncrementID
      FROM @def
    
    SELECT *
      FROM @abc


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, December 31, 2015 8:32 PM
  • Correct me if I'm wrong, if I understood it correctly, you are using ABCID minus 2 to get the incrementID.

    Perhaps my example is not entirely correct. But, there's no relation between ABCID and incrementID. From my original example, table def has 3 rows, abcid 9 fiscalyear is 1415; if the other data points (not included in the table example) are the same, when abcid 9 gets inserted into table abc, incrementid could be 1 or 3 (again, pending other data points that matches table def's abcid 9).

    With your example, if def's abcid 9 gets inserted into table abc, it'll become 7, correct?

    Thursday, December 31, 2015 8:59 PM
  • Hi Ami,

    You said that the result should be:

    1    1314    1
    2    1415    1
    3    1516    1
    4    1314    2
    5    1415    3
    6    1516    4
    7    1617    5
    8    1617    6

    But if I understood your description then it should be 

    1    1314    1
    2    1415    1
    3    1516    1
    4    1314    2
    5    1415    3
    6    1516    4
    7    1617    1   <<< This the first time you get 1617 so it should be 1
    8    1617    5

    Am I correct?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, December 31, 2015 9:59 PM
  • Hi Ami2013,

    Your requirement is not clear. The result can be various depending on how is the incrementid determined. Please see below sample.

    --Global-wise
    DECLARE @MaxIncID INT
    SELECT @MaxIncID=MAX(incrementid) FROM #abc
    
    --INSERT INTO #abc
    SELECT abcid,fiscalyear,ROW_NUMBER() OVER(ORDER BY abcid)+@MaxIncID FROM #def
    
    SELECT * FROM #abc
    GO
    
    --fiscalyr-wise  
    ;WITH Cte AS
    (
    SELECT fiscalyr,MAX(incrementid) startIncID FROM #abc GROUP BY fiscalyr
    )
    --INSERT INTO #abc
    SELECT abcid,fiscalyear,COALESCE(c.startIncID,0)+ROW_NUMBER() OVER(PARTITION BY d.fiscalyear ORDER BY abcid)
    FROM #def d LEFT JOIN Cte C ON d.fiscalyear=c.fiscalyr

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Friday, January 1, 2016 1:49 AM
  • I apologize for the confusion. Let's see if I can clear it up a little or possibly muddle it more. :(

    In #abc, there are 30+ other columns; with the criteria if columns fiscalyr, city, state, country are similar, column incrementid will be the same.

    If data from #def is different in any one of the 4 columns ( fiscalyr, city, state, country), then column incrementid will get the next number from #abc when record is inserted.

    So as an example: If #abc has the following rows:

    abcid, fiscalyr, incrementid, city, state, country:

    1,'1314', 1, 'Seattle', 'WA', 'USA'

    2,'1314', 1, 'Seattle', 'WA', 'USA'

    3,'1314', 2, 'Portland','OR', 'USA'

    4, '1415',3,'Portland','OR','USA'

    If these are rows of data that is in #def that will be inserted to #abc

    defid, fiscalyr, city, state, country

    1, '1314','Seattle','WA',USA'

    2,'1516','Seattle,'WA','USA'

    Then the end results in #abc needs to be

    abcid, fiscalyr, incrementid, city, state, country:

    1,'1314', 1, 'Seattle', 'WA', 'USA'

    2,'1314', 1, 'Seattle', 'WA', 'USA'

    3,'1314', 2, 'Portland','OR', 'USA'

    4, '1415',3,'Portland','OR','USA'

    5, '1314',1,'Seattle','WA','USA'

    6, '1516',4,'Seattle','WA','USA'

    incrementid = 4 for abcid = 6

    Again, I apologize for the confusion and thanks for all your help.

    Tuesday, January 5, 2016 9:10 PM
  • Hi,

    Let's try to understand :-)
    Everything that I write is assuming that I understood you explanation
    You current explanation is totally different :-)

    First: Your requirement is a bit problematic if you want to use multiple users. You will probably need to use lock! You can not use the approach of select the MAX incrementid without lock with multiple users.

    Think about case that user A have a row which is unique values, therefore incrementid should get the maximum incrementid value + 1, but at the same time another user might try to add a row with different data which is unique. If there is no lock the both users will give the new row the same value since both got the same max value.

    I think that this solution should fit your need:

    /************************************* DDL+DML */
    drop table if exists #abc
    drop table if exists #def
    -- the above work only on SQL Server 2016
    
    CREATE TABLE #abc (abcId INT IDENTITY(1,1), fiscalyr   VARCHAR(4),incrementid INT,  city NVARCHAR(11), [state] NVARCHAR(11), country NVARCHAR(11))
    SET IDENTITY_INSERT #abc ON
    insert #abc (abcid, fiscalyr, incrementid, city, [state], country) values
    (1, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (2, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (3, '1314', 2, 'Portland', 'OR', 'USA'),
    (4, '1415', 3, 'Portland', 'OR', 'USA')
    SET IDENTITY_INSERT #abc OFF
    
    CREATE TABLE #def (defid int IDENTITY(1,1), fiscalyr VARCHAR(4),  city NVARCHAR(11), [state] NVARCHAR(11), country NVARCHAR(11))
    SET IDENTITY_INSERT #def ON
    insert #def(defid, fiscalyr, city, [state], country) values
    (1, '1314', 'Seattle', 'WA', 'USA'),
    (2, '1516', 'Seattle', 'WA', 'USA')
    SET IDENTITY_INSERT #def OFF
    
    select * from #abc
    select * from #def
    
    
    
    /*************************************** Solution */
    insert #abc (fiscalyr, city, [state], country, incrementid)
    select o.fiscalyr, o.city, o.[state], o.country
    	,incrementid = CASE 
    		WHEN EXISTS(SELECT * from #abc i where o.city = i.city and o.country = i.country and o.fiscalyr = i.fiscalyr and o.[state] = i.[state]) 
    			then (SELECT top 1 incrementid from #abc i where o.city = i.city and o.country = i.country and o.fiscalyr = i.fiscalyr and o.[state] = i.[state]) 
    		else (select MAX (incrementid) from #abc) + 1
    	END
    from #def o
    with (TABLOCK)
    GO
    
    select * from #abc
    GO
    
     

    * This is probably not the best solution, but since you did not post a real DDL+DML there is no meaning for performance, at this time. Moreover, first we need to make sure that we understand the question and that this result fit your need :-)

    I hope this is useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, January 8, 2016 6:33 PM
  •  
    
    CREATE TABLE #abc (abcId int, fiscalyr VARCHAR(10),incrementid INT,  city VARCHAR(20), state  VARCHAR(20), country  VARCHAR(20))
     
    insert #abc (abcid, fiscalyr, incrementid, city, [state], country) values
    (1, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (2, '1314', 1, 'Seattle' , 'WA', 'USA'),
    (3, '1314', 2, 'Portland', 'OR', 'USA'),
    (4, '1415', 3, 'Portland', 'OR', 'USA')
     
    
    CREATE TABLE #def (defid int, fiscalyr  VARCHAR(20),  city  VARCHAR(20), [state]  VARCHAR(20), country  VARCHAR(20))
     
    insert #def(defid, fiscalyr, city, [state], country) values
    (1, '1314', 'Seattle', 'WA', 'USA'),
    (2, '1516', 'Seattle', 'WA', 'USA')
     
    
    ;with mycte as (
    select abcid, fiscalyr, incrementid, city, state, country, 1 as lvl 
    from #abc
    union all
    select defid, fiscalyr, null as incrementid, city, state, country , 2 as lvl 
    from #def)
    
    ,mycte2 as (
    Select lvl, row_number() Over(Order by lvl, abcid) rn, fiscalyr,  city, state, country   
     ,dense_rank()Over(Order by  fiscalyr,city desc,state,country ) incrementid
    from mycte )
    
    
    Insert INTO   #abc (abcid, fiscalyr, incrementid, city, state, country)
    
    Select rn as abcid, fiscalyr,incrementid,  city, state, country 
    from mycte2 
    where lvl=2
    
    
    select * from #abc
    
     
    drop table #abc, #def

    • Proposed as answer by pituachMVP Friday, January 8, 2016 7:47 PM
    • Marked as answer by Eric__Zhang Thursday, January 21, 2016 10:39 AM
    Friday, January 8, 2016 7:20 PM
  • Nice solution Jingyang :-)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, January 8, 2016 7:47 PM
  • Pituach,

    Thank you for trying to solve the problem.

    I just follow your thread for another option.

    Friday, January 8, 2016 8:10 PM