none
Insert into AutoNumber

    Question

  • Simple question I hope.

     

    I am trying to do an insert into with an autonumber required the code is below. There is a field called RepGoal_Id in the data structure it does not allow nulls. How can I run q subquery against the table and then insert it into here as an autonumber increment of 1 ?

    Insert

    into dbo.RepGoalbk(fk_Goal_Id

     

    ,fk_Month_Id

     

    ,fk_User_Role_id

     

    ,fk_Region_id

     

    ,fk_Department_id

     

    ,RepGoal_MonthlyAmount

     

    ,RepGoal_SalesDepartmentOLD)

    SELECT

    [Goal_Id]

     

    ,[Month_Id]

     

    ,[User_Role_Id]

     

    ,[region_id]

     

    ,[department_id]

     

    ,[Monthly_Amount]

     

    ,[department_id]

     

    FROM [Testing_Database].[dbo].[upload_view]

     

    Tuesday, December 06, 2011 3:46 PM

Answers

  • Try This.
    Insert into dbo.RepGoal( 
    RepGoal_Id
    ,fk_Goal_Id
    ,fk_Month_Id
    ,fk_User_Role_id
    ,fk_Region_id
    ,fk_Department_id
    ,RepGoal_MonthlyAmount
    ,RepGoal_SalesDepartmentOLD)
    SELECT
    ROW_NUMBER() OVER(ORDER BY(SELECT 0))+(select max(RepGoal_Id) from [Testing_Database].[dbo].[RepGoal])
    ,[Goal_Id]
    ,[Month_Id]
    ,[User_Role_Id]
    ,[region_id]
    ,[department_id]
    ,[Monthly_Amount]
    ,[department_id]
    FROM [Testing_Database].[dbo].[upload_view]

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Tuesday, December 06, 2011 5:13 PM

All replies

  • Why don't you make RepGoal_Id as identity column for more pls check here http://msdn.microsoft.com/en-us/library/ms186775.aspx
    http://uk.linkedin.com/in/ramjaddu
    Tuesday, December 06, 2011 4:02 PM
  • You want to add a unique id?

    ROW_NUMBER() can generate row numbers. Added to the current MAX(Id) will generate unique numbers.

    Tuesday, December 06, 2011 4:23 PM
    Moderator
  • You want to add a unique id?

    ROW_NUMBER() can generate row numbers. Added to the current MAX(Id) will generate unique numbers.


    Brian I have slightly changed the code: The problem with this code is that the Id it actually inserts will always be the same it should be previous row +1 and so on. It almost looks like I need to loop the whole insert statement?  I think where this is confusing is the row count is coming from RepGoal and NOT where Select statement at the bottom([Testing_Database].[dbo].[upload_view]) . Hopefully that helps.

    Insert

    into dbo.RepGoal( RepGoal_Id

     

    ,fk_Goal_Id

     

    ,fk_Month_Id

     

    ,fk_User_Role_id

     

    ,fk_Region_id

     

    ,fk_Department_id

     

    ,RepGoal_MonthlyAmount

     

    ,RepGoal_SalesDepartmentOLD)

    SELECT

    (select max(RepGoal_Id)+1 from [Testing_Database].[dbo].[RepGoal]

    )

     

    ,[Goal_Id]

     

    ,[Month_Id]

     

    ,[User_Role_Id]

     

    ,[region_id]

     

    ,[department_id]

     

    ,[Monthly_Amount]

     

    ,[department_id]

     

    FROM [Testing_Database].[dbo].[upload_view]
    Tuesday, December 06, 2011 4:57 PM
  • Howsabout max(RepGoal_Id) + ROW_NUMBER() OVER (ORDER BY RepGoal_Id)
    Tuesday, December 06, 2011 5:09 PM
    Moderator
  • Try This.
    Insert into dbo.RepGoal( 
    RepGoal_Id
    ,fk_Goal_Id
    ,fk_Month_Id
    ,fk_User_Role_id
    ,fk_Region_id
    ,fk_Department_id
    ,RepGoal_MonthlyAmount
    ,RepGoal_SalesDepartmentOLD)
    SELECT
    ROW_NUMBER() OVER(ORDER BY(SELECT 0))+(select max(RepGoal_Id) from [Testing_Database].[dbo].[RepGoal])
    ,[Goal_Id]
    ,[Month_Id]
    ,[User_Role_Id]
    ,[region_id]
    ,[department_id]
    ,[Monthly_Amount]
    ,[department_id]
    FROM [Testing_Database].[dbo].[upload_view]

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Tuesday, December 06, 2011 5:13 PM
  • this works perfectly !
    Wednesday, December 07, 2011 8:14 PM