February 6, 2012 at 5:08 am
Hi All,
I am trying to create an import function and I want to avoid using a cursor, but am not sure I can. I need to import product rows and serialise each one. So if the quantity of 1 item is 10 it needs to import 10 rows, each with a unique id.
CREATE TABLE #testitem (
itm_item varchar(43) NOT NULL,
itm_desc varchar(100) NOT NULL,
wsd_quantity int NOT NULL)
INSERT INTO #testitem
SELECT 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX', 10 UNION ALL
SELECT 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer',5 UNION ALL
SELECT 'CHES07F','Chroma-Q Etherswitch 7F',2
SELECT *
FROM #testitem
DROP TABLE #testitem
I want the output to look like this:
1, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
2, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
3, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
4, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
5, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
6, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
7, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
8, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
9, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
10, 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX'
11, 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer'
12, 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer'
13, 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer'
14, 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer'
15, 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer'
16, 'CHES07F','Chroma-Q Etherswitch 7F'
17, 'CHES07F','Chroma-Q Etherswitch 7F'
Any ideas?
Cheers,
Steve
February 6, 2012 at 5:33 am
something like this?
here's an article on the tally table
http://qa.sqlservercentral.com/articles/Tally+Table/70735/
declare @testitem TABLE (
itm_item varchar(43) NOT NULL,
itm_desc varchar(100) NOT NULL,
wsd_quantity int NOT NULL)
INSERT INTO @testitem
SELECT 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX', 10 UNION ALL
SELECT 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer',5 UNION ALL
SELECT 'CHES07F','Chroma-Q Etherswitch 7F',2
SELECT
ROW_NUMBER() over (order by ti1.itm_item),
ti1.*
FROM @testitem ti1
cross join Tally t
where
t.N <= wsd_quantity
February 6, 2012 at 5:42 am
Something like that, but for SQL 2000 🙂
CREATE TABLE #testitem (
itm_item varchar(43) NOT NULL,
itm_desc varchar(100) NOT NULL,
wsd_quantity int NOT NULL)
INSERT INTO #testitem
SELECT 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX', 10 UNION ALL
SELECT 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer',5 UNION ALL
SELECT 'CHES07F','Chroma-Q Etherswitch 7F',2
CREATE TABLE #item (
itm_serial int NOT NULL IDENTITY(2000,1),
itm_item varchar(43) NOT NULL,
itm_desc varchar(100) NOT NULL)
INSERT INTO #item
SELECT itm_item, itm_desc
FROM #testitem ti1
cross join tally t
where
t.N <= wsd_quantity
SELECT *
FROM #item
DROP TABLE #testitem
DROP TABLE #item
Thank you!
Steve
February 6, 2012 at 5:49 am
Not used table variables before, but they look good to me!
DECLARE @item TABLE (
itm_serial int NOT NULL IDENTITY(5000,1),
itm_item varchar(43) NOT NULL,
itm_desc varchar(100) NOT NULL)
INSERT INTO @item
SELECT itm_item, itm_desc
FROM (SELECT itm_item, itm_desc, SUM(wsd_quantity) AS wsd_quantity
FROM ACLV4.aclv4.dbo.item INNER JOIN ACLV4.aclv4.dbo.stockdetail ON wsd_item = itm_item AND wsd_warehouse = 'WH5'
GROUP BY itm_item, itm_desc) ti1
cross join tally t
where
t.N <= wsd_quantity
SELECT *
FROM @item
February 6, 2012 at 6:09 am
Almost.... since a function was required, and you can't use temp tables in a function:
CREATE TABLE testitem (itm_item varchar(43) NOT NULL,itm_desc varchar(100) NOT NULL,wsd_quantity int NOT NULL)
INSERT INTO testitem
SELECT 'CHCWPSU10DMX','Chroma-Q Color Web Power Supply Unit For 160 Cells - DMX', 10 UNION ALL
SELECT 'CHDMX4', 'Chroma-Q 4Play DMX4 4 Way DMX Buffer',5 UNION ALL
SELECT 'CHES07F','Chroma-Q Etherswitch 7F',2
GO
CREATE FUNCTION ImportFunction()
RETURNS @Import TABLE (itm_serial int, itm_item varchar(43), itm_desc varchar(100))
AS
BEGIN
DECLARE @item table (itm_serial int NOT NULL IDENTITY(2000,1),itm_item varchar(43) NOT NULL,itm_desc varchar(100) NOT NULL)
INSERT INTO @item
SELECT itm_item, itm_desc
FROM testitem ti1cross join Numbers t
wheret.Number <= wsd_quantity
INSERT INTO @Import(itm_serial, itm_item, itm_desc)
SELECT itm_serial, itm_item, itm_desc FROM @item
RETURN
END
GO
SELECT * FROM ImportFunction()
DROP _TABLE testitem
DROP _FUNCTION ImportFunction
John
February 6, 2012 at 6:49 am
Thank you John, that looks very promising.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply