我的表格称为prd0101:
codi descr nomfam nomsub nomgru marc codf peso estado fe_i
---------------------------------------------------------------------------------------------------------------------------------------------------------
0301-011611 TIRAS DE PRUEBA PRODUCTOS TERMINADOS ALIGERADOS ALIGERADOS ND 123456789ABCDEF 0.0250 1 2019-06-27 00:00:00.000
0301-011612 TIRAS PRUEBAS 2 PRODUCTOS TERMINADOS ALIGERADOS ALIGERADOS ND 123456789ABCDE1 0.0360 1 2019-06-27 00:00:00.000
0301-011613 TIRAS PRUEBA 3 PRODUCTOS TERMINADOS ALIGERADOS ALIGERADOS ND 123456789ABCDEF2 0.0690 1 2019-06-27 00:00:00.000
这是我的查询选择数据并将其插入另一个称为product2:
的表DECLARE
@COUNTER INT = 0 ,
@MAX INT = (SELECT COUNT(*)
FROM prd0101 p
LEFT JOIN tbl01itm i ON p.codi = i.codi
LEFT JOIN tbl01grp g ON g.codgru = i.codgru
LEFT JOIN tbl01sbf s ON s.codsub = g.codsub
LEFT JOIN tbl01fam f ON f.codfam = s.codfam
WHERE p.fe_i >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)),
@CODI NVARCHAR(100),
@DESCR NVARCHAR(100),
@NOMFAM NVARCHAR(100),
@NOMSUB NVARCHAR(100),
@NOMGRU NVARCHAR(100),
@MARC NVARCHAR(100),
@CODF NVARCHAR(100),
@PESO NVARCHAR(100),
@ESTADO NVARCHAR(100)
WHILE @COUNTER < @MAX
BEGIN
--cte
;WITH cte AS
(
SELECT
p.codi, p.descr, f.nomfam, s.nomsub,
g.nomgru, p.marc, p.codf, p.peso, p.estado, p.fe_i
FROM
prd0101 p
LEFT JOIN
tbl01itm i ON p.codi = i.codi
LEFT JOIN
tbl01grp g ON g.codgru = i.codgru
LEFT JOIN
tbl01sbf s ON s.codsub = g.codsub
LEFT JOIN
tbl01fam f ON f.codfam = s.codfam
)
SELECT
@CODI = cte.codi, @DESCR = cte.descr,
@NOMFAM = cte.nomfam, @NOMSUB = cte.nomsub,
@NOMGRU = cte.nomgru, @MARC = cte.marc,
@CODF = cte.codf, @PESO = cte.peso, @ESTADO = cte.estado
FROM
cte
WHERE
cte.fe_i >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND cte.estado = 1
IF NOT EXISTS (SELECT codi FROM Product2 WHERE codi = @CODI)
BEGIN
INSERT INTO Product2 (codi, "Description", Familia__c, Subfamilia__c, Grupo__c, Marca__c, ProductCode, Peso__c, IsActive)
VALUES (@CODI, @DESCR, @NOMFAM, @NOMSUB, @NOMGRU, @MARC, @CODF, @PESO, @ESTADO)
END
SET @COUNTER = @COUNTER + 1
END
问题是在循环中始终分配相同的记录。Codi = 0301-011613
的记录我在循环中打印一些变量,并为其中3个变量打印了同一行。
我希望插入3个记录,但仅插入最后一个记录。
原始tsql(带有CTE(似乎返回每个循环的相同值(结果集中的最后一个记录?(。也就是说,标量变量@Counter增量超过@max,但是CTE/SELECT语句似乎不会改变其返回的内容。
似乎您希望将当今的产品(无论时间如何(插入表product2,但前提
如果是这样,假设Codi包含唯一的值,则可以通过插入语句进行此操作:
INSERT Product2(codi
,Description
,Familia__c
,Subfamilia__c
,Grupo__c
,Marca__c
,ProductCode
,Peso__c
,IsActive
)
SELECT p.codi
,p.descr
,f.nomfam
,s.nomsub
,g.nomgru
,p.marc
,p.codf
,p.peso
,p.estado
FROM prd0101 AS p
LEFT JOIN tbl01itm AS i
ON p.codi=i.codi
LEFT JOIN tbl01grp AS g
ON g.codgru=i.codgru
LEFT JOIN tbl01sbf AS s
ON s.codsub=g.codsub
LEFT JOIN tbl01fam AS f
ON f.codfam=s.codfam
WHERE -- Note: Select all rows from current day, regardless of time:
p.fe_i >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
AND p.estado = 1
AND NOT EXISTS
(SELECT P2.codi
FROM Product2 AS P2
WHERE p2.codi = p.codi
)
;
SELECT *
FROM Product2
;
输出显示三行,如预期:
| codi | Description | Familia__c | Subfamilia__c | Grupo__c | Marca__c | ProductCode | Peso__c | IsActive |
|-------------|-----------------|------------|---------------|----------|----------|------------------|---------|----------|
| 0301-011611 | TIRAS DE PRUEBA | 1 | 1 | 1 | ND | 123456789ABCDEF | 0 | 1 |
| 0301-011612 | TIRAS PRUEBAS 2 | 1 | 1 | 1 | ND | 123456789ABCDE1 | 0 | 1 |
| 0301-011613 | TIRAS PRUEBA 3 | 1 | 1 | 1 | ND | 123456789ABCDEF2 | 0 | 1 |
sql小提琴(引用了其他表格,如数据未提供;此外,我用" 2019-06-27"的日期替换了GetDate((,以匹配提供的数据(:http://sqlfiddle.com/#!18/5ba09/18
我修复了它,将其添加到CTE选择:
ROW_NUMBER() OVER(ORDER BY p.codi) AS 'Row1'
以及在其中的子句之后的CTE外面添加了此信息:
AND Row1 = @COUNTER + 1
迭代了我所期望的所有行。