April 24, 2024 at 12:53 am
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
April 24, 2024 at 8:17 am
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
April 24, 2024 at 8:19 am
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
April 24, 2024 at 9:27 am
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.
April 24, 2024 at 9:54 am
Have you got an example of the sort of data you'd like to insert?
April 24, 2024 at 4:11 pm
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.
April 24, 2024 at 4:57 pm
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
;
April 24, 2024 at 10:08 pm
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.
----------------------------------------------------
April 24, 2024 at 10:09 pm
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