How to use a scalar function in INSERT stmt?

  • Hello all,

    I would like to create an INSERT statement and use a UDF as part of the VALUES() set.  The UDF generates a unique product code based on the last inserted product code value.  Now that I'm typing this out loud, I'm not so sure this would work for more than one record at a time...

    This would basically be a bulk insert, in this case I have 99 serial numbers to insert and would like to generate a product code for each one on insert.  I'm starting to think I might need an SP using a WHILE statement (or something like that) that would generate the product code and insert for each serial number in the sub query...

    Am I on the right track?

    Thanks,

    Mike

     

  • Can you craft a SELECT statement which produces the rows you wish to insert? If yes, the rest is easy:

    INSERT table (column list)
    SELECT column list FROM ...

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • In what form are the 99 serial numbers? Are they in a table?

     

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • mjdemaris wrote:

    Hello all,

    I would like to create an INSERT statement and use a UDF as part of the VALUES() set.  The UDF generates a unique product code based on the last inserted product code value.  Now that I'm typing this out loud, I'm not so sure this would work for more than one record at a time...

    This would basically be a bulk insert, in this case I have 99 serial numbers to insert and would like to generate a product code for each one on insert.  I'm starting to think I might need an SP using a WHILE statement (or something like that) that would generate the product code and insert for each serial number in the sub query...

    Am I on the right track?

    Thanks,

    Mike

    depending on what the UDF is doing it may be an issue - can you post the code for that UDF as well.

    it may be the case that you need to get a new version of the UDF or to have its code within your select.

  • Have you got an example of the sort of data you'd like to insert?

  • Right, here's an example of my first thought:

    INSERT INTO dbo.table (ProductCode, ProductionDate, SerialNumber)
    VALUES ([dbo].[GenerateProductCode], GETDATE(), (SELECT DISTINCT SerialNumber FROM dbo.ItemTable) )

    The serial numbers are alpha-numeric, three to five characters in length and are selected from another table.

    The UDF gets the last (alpha-numeric) product code, formatted as "A100001", adds one to that each new code moves up sequentially.

    No Params

    RETURNS CHAR(7)
    AS
    BEGIN
    -- Declare the return variable here
    DECLARE @Result CHAR(7)
    DECLARE @Code CHAR(7)
    DECLARE @Code2 INT

    -- Add the T-SQL statements to compute the return value here
    SET @Code = (SELECT MAX(ProductCode)
    FROM [dbo].[Products])
    SET @Code2 = CAST(RIGHT(@Code, 6) AS INT)

    SET @Result = CONCAT('A', CONVERT(CHAR(7),@Code2 + 1))

    -- Return the result of the function
    RETURN @Result

    END

    If I could put this in English, I would probably say something like:

    For each unique Serial Number from the Item Table, get that Serial Number, get the current date, and generate a new  unique Product Code based on the last/largest Product Code found in the Products table.

     

  • I'm not sure I quite understand, but maybe something like this:

    INSERT INTO dbo.[table1] (ProductCode, ProductionDate, SerialNumber)
    SELECT 'A' + RIGHT('00000' + CAST(CAST(RIGHT(MAX(ProductCode), 6) AS INT) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))AS VARCHAR), 6),
    GETDATE(),
    x.SerialNumber
    FROM [dbo].[Products]
    CROSS JOIN(SELECT DISTINCT SerialNumber FROM dbo.ItemTable) x
    ;
  • Are you lookging for something like

    SELECT
    CONCAT(s1.Product_code, s2.serialNumber) as newProductCode
    s2.SerialNumber
    GETDATE(),

    FROM (SELECT TOP 1 product_code from [dbo].[Products] Order by Add_date desc) as s1
    CROSS APPLY
    (SELECT SerialNumber FROM dbo.ItemTable) as s2
    ;

    If you have sample data and what you want the output to look like, that would help.

    ----------------------------------------------------

  • Are you lookging for something like

    SELECT
    CONCAT(s1.Product_code, s2.serialNumber) as newProductCode
    s2.SerialNumber
    GETDATE(),

    FROM (SELECT TOP 1 product_code from [dbo].[Products] Order by Add_date desc) as s1
    CROSS APPLY
    (SELECT SerialNumber FROM dbo.ItemTable) as s2
    ;

    If you have sample data and what you want the output to look like, that would help.

    ----------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply