CREATE TABLE Tabx_Test_Import
(
ID INT NOT NULL IDENTITY(1, 1),
Week NVARCHAR(2000),
Code NVARCHAR(20),
Amount NUMERIC(19, 6),
Name NVARCHAR(50),
Last_Name NVARCHAR(50),
RC NVARCHAR(11)
) ON [PRIMARY]
CREATE TABLE Tabx_Test_Inz
(
ID INT NOT NULL IDENTITY(1, 1),
Week INT NOT NULL,
RC NVARCHAR(10),
Code NVARCHAR(20),
Amount NUMERIC(19, 6),
) ON [PRIMARY]
INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'01,02,03', N'012016A15', 11.50, N'Juraj', N'Novotny', N'050671/8652')
INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04,05,08', N'012016G45', 22.30, N'Peter', N'Pychly', N'030888/3553')
INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'10,11,12,13', N'012016A18', 8.70, N'Juraj', N'Novotny', N'050671/8652')
INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04,05,06', N'012016T66', 12.12, N'Peter', N'Pychly', N'030888/3553')
INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04', N'012016H11', 55.00, N'Peter', N'Pychly', N'030888/3553')
每周將在一行中 - 必須為代碼和客戶打破匯入表中的周記錄，而客戶則有多少行用逗號分隔。
這意味著一周，一個客戶和一個代碼將在一行中。
INSERT INTO Tabx_Test_Inz (Week, RC, Code, Amount)
SELECT
SUBSTRING(Week,1,2),
REPLACE(RC,'/','') , Code, Amount
FROM
Tabx_Test_Import;
INSERT INTO Tabx_Test_Inz (Week, RC, Code, Amount)
SELECT
SUBSTRING(Week,4,2),
REPLACE(RC,'/','') , Code, Amount
FROM
Tabx_Test_Import;
INSERT INTO Tabx_Test_Inz (Week, RC, Code, Amount)
SELECT
SUBSTRING(Week,7,2),
REPLACE(RC,'/','') , Code, Anount
FROM
Tabx_Test_Import;
INSERT INTO Tabx_Test_Inz (Week, RC, Code, Amount)
SELECT
SUBSTRING(Week,10,2),
REPLACE(RC,'/','') , Code, Amount
FROM
Tabx_Test_Import;
我試過這個...但它不正確。
uj5u.com熱心網友回復：
看起來您想為周的每個不同值插入相同的行，您可以string_split用于此目的：
insert into Tabx_Test_Inz (Week, RC, Code, Amount)
select value, replace(RC,'/',''), code, amount
from Tabx_Test_Import
cross apply String_Split(week,',')
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397710.html
標籤：sql sql-server 查询语句