27,580
社区成员
发帖
与我相关
我的任务
分享
begin tran
--insert into [MT_IDENCAM_Extract].[dbo].[HIP_TEST_DETAIL_RECORD_BAK] ([TRACK_ID])
--select [TRACK_ID] from #T1
declare @i int,@j int
set @i=1
while(@i<=10)
SET @intRowCount = @@ROWCOUNT;
begin
insert into #Tab1( [RECORD_TYPE]
,[TRACK_ID]
,[RECORD_DATE]
,[TEST_CODE]
,[TEST_CHANNEL]
,[TEST_VALUE]
,[TEST_FREQUENCY]
,[PASS_FAIL]
,[TEST_VOLTAGE]
,[LOWER_LIMIT]
,[UPPER_LIMIT]
,[TEST_TIME]
,[ATTEMPTS]
,[MEASUREMENT_UNITS]
,[RETEST_FLAG]
,[FILE_INDEX]
,[UPLOAD_DATE] )
values(
select [RECORD_TYPE]
,(select [TRACK_ID] from #T1 where row_no=@i) as [TRACK_ID]
,[RECORD_DATE]
,[TEST_CODE]
,[TEST_CHANNEL]
,[TEST_VALUE]
,[TEST_FREQUENCY]
,[PASS_FAIL]
,[TEST_VOLTAGE]
,[LOWER_LIMIT]
,[UPPER_LIMIT]
,[TEST_TIME]
,[ATTEMPTS]
,[MEASUREMENT_UNITS]
,[RETEST_FLAG]
,[FILE_INDEX]
,[UPLOAD_DATE] from #Tab1)
end
set @i=@i+1
--select * from #Tab1
rollback
CREATE TABLE [dbo].[HIP_TEST_DETAIL_RECORD_BAK](
[RECORD_TYPE] [varchar](20) NULL,
[TRACK_ID] [varchar](30) NULL,
[RECORD_DATE] [datetime] NULL,
[TEST_CODE] [varchar](50) NULL,
[TEST_CHANNEL] [numeric](38, 0) NULL,
[TEST_VALUE] [numeric](38, 6) NULL,
[TEST_FREQUENCY] [numeric](38, 6) NULL,
[PASS_FAIL] [varchar](1) NULL,
[TEST_VOLTAGE] [numeric](38, 6) NULL,
[LOWER_LIMIT] [numeric](38, 6) NULL,
[UPPER_LIMIT] [numeric](38, 6) NULL,
[TEST_TIME] [numeric](38, 0) NULL,
[ATTEMPTS] [numeric](38, 0) NULL,
[MEASUREMENT_UNITS] [varchar](15) NULL,
[RETEST_FLAG] [varchar](1) NULL,
[FILE_INDEX] [varchar](50) NULL,
[UPLOAD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
数据如下
RECORD_TYPE TRACK_ID RECORD_DATE TEST_CODE TEST_CHANNEL TEST_VALUE TEST_FREQUENCY PASS_FAIL TEST_VOLTAGE LOWER_LIMIT UPPER_LIMIT TEST_TIME ATTEMPTS MEASUREMENT_UNITS RETEST_FLAG FILE_INDEX UPLOAD_DATE
TEST QL03730 2016-01-06 13:25:10.000 RCU_APP_BOOST_STATUS_REQ 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:25:10.000 WRITESTATIONNUMBER 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:54.000 SSENBER 0 0.020623 2441.000000 P 3.700000 -100000000.000000 0.100000 10 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:48.000 OUTPUTPOWER 0 1.098186 2441.000000 P 3.700000 -6.000000 4.000000 6 0 DBM 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:42.000 GREENLIGHT 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:42.000 BLUELIGHT 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:42.000 MERGELKEY 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 5 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:41.000 EXITFACTESTMODEFORLED 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:41.000 ENTERLEDTESTMODE 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:41.000 REDLIGHT 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:36.000 READBTADD 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:30.000 WRITEBTADD 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 MFICHECK 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 BATTERYCAPACITY 0 4027.000000 2441.000000 P 3.700000 3850.000000 4300.000000 0 0 MV 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 FIRMWAREVERCHECKBC7 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 FIRMWAREVERCHECKD9 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 WRITEUNITSN 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:27.000 READANDVIRIFYUNITSN 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:26.000 MFBBUTTONRELEASE 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:25.000 VOLUMDOWNBUTTONRELEASE 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:25.000 MFBBUTTONPRESS 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:18.000 VOLUMDOWNBUTTONPRESS 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:17.000 VOLUMUPBUTTONRELEASE 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:16.000 VOLUMUPBUTTONPRESS 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:24:10.000 ENTERBUTTONTESTMODE 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:23:59.000 RESETBUTTONPRESS 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 11 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:23:58.000 TESTED BY LIANGHU.WU-GUOXIU 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:23:58.000 DEVICE IS MFGED FOLLOWING HIP-WSZE-TST-WI-Q007 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:23:58.000 READSTATIONNUMBER 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 0 0 NA 0 NULL 2017-02-08 08:38:42.000
TEST QL03730 2016-01-06 13:23:58.000 UNPAIRALL 0 1.000000 2441.000000 P 3.700000 0.000000 1.000000 1 0 NA 0 NULL 2017-02-08 08:38:42.000
----测试数据
IF not object_ID('#表一') is null
drop table #表一
Go
CREATE TABLE #表一
(
track_id NVARCHAR(100) ,
otherdata NVARCHAR(100)
)
if not object_ID('#表二') is null
drop table #表二
Go
CREATE TABLE #表二
(
track_id NVARCHAR(100) ,
[No] NVARCHAR(100)
)
INSERT INTO #表二
select 'NN00001','测试1' UNION ALL
select 'NN00002','测试2' UNION ALL
select 'NN00003','测试3'
---测试数据结束
DECLARE @i INT ,
@track_id NVARCHAR(100) ,
@No NVARCHAR(100)
--利用游标
DECLARE MyCURSOR CURSOR
FOR
SELECT *
FROM #表二
OPEN MyCURSOR
---对表二每条数据循环30次,插入到表一
FETCH NEXT FROM MyCURSOR INTO @track_id, @No
WHILE ( @@fetch_status = 0 )
BEGIN
SET @i = 0
WHILE @i < 30
BEGIN
INSERT INTO #表一
VALUES ( @track_id, @no )
SET @i = @i + 1
END
FETCH NEXT FROM MyCURSOR INTO @track_id, @No
END
CLOSE MyCURSOR
DEALLOCATE MyCURSOR
----测试结果
SELECT *
FROM #表一
---删除临时表
DROP TABLE #表一,#表二
----测试数据
IF not object_ID('#表一') is null
drop table #表一
Go
CREATE TABLE #表一
(
track_id NVARCHAR(100) ,
otherdata NVARCHAR(100)
)
INSERT INTO #表一
select 'QL03730','测试1' UNION ALL
select 'QL03730','测试2' UNION ALL
select 'QL03730','测试3'
if not object_ID('#表二') is null
drop table #表二
Go
CREATE TABLE #表二
(
track_id NVARCHAR(100) ,
[No] INT
)
INSERT INTO #表二
select 'NN00001',1 UNION ALL
select 'NN00002',2
----测试数据结束 表一3条数据,这三条数据同一个track_id的
---插入数据
INSERT INTO #表一
SELECT track_id ,
t.otherdata
FROM #表二
CROSS APPLY ( SELECT otherdata
FROM #表一
) t
---读取测试结果
SELECT *
FROM #表一
---删除临时表
DROP TABLE #表一,#表二
WITH T1(row_no, TRACK_ID) AS
(
select 1, 'NL01513' UNION ALL
select 2, 'NN00001' UNION ALL
select 3, 'NN00002' UNION ALL
select 4, 'NN00003' UNION ALL
select 5, 'NN00004 '
),T2(F1,F2,F3) As
(
select 'a1', 'a2','a3' UNION ALL
select 'b1', 'b2','b3' UNION ALL
select 'c1', 'c2','c3'
)
Select b.TRACK_ID,a.* from T2 a cross apply T1 b