How to use a scalar function in INSERT stmt?

Question

How to use a scalar function in INSERT stmt?

mjdemaris

SSC-Addicted

Points: 497
More actions
April 24, 2024 at 12:53 am

#4414759

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

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

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

Phil Parkin SSC Guru Points: 246893 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246893 More actions · Answer 2

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

frederico_fonseca SSCoach Points: 16141 More actions · Answer 3

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.

Jonathan AC Roberts SSCoach Points: 18328 More actions · Answer 4

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

mjdemaris SSC-Addicted Points: 497 More actions · Answer 5

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.

Jonathan AC Roberts SSCoach Points: 18328 More actions · Answer 6

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
;

MMartin1 One Orange Chip Points: 27793 More actions · Answer 7

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.

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

MMartin1 One Orange Chip Points: 27793 More actions · Answer 8

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.

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