sql - 使用 Cursor 在 SQL Server 中插入表

Question

我正在尝试将 excel 文件导入 SQL，该文件有 3 列:框 - 代码 - 有效性
我正在使用以下查询

USE [Libatel]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetDataExcel]

as

DECLARE c CURSOR FOR select  Box, Code , Validity FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Barcodes.xlsx;HDR=YES',     'SELECT Box, Code , Validity FROM [sheet1$]')
declare @Code as bigint
declare @Box as bigint
declare @Validity as date

begin


open c 

fetch next from c into @Code,@Box,@Validity

WHILE @@FETCH_STATUS = 0
begin
select @Box = Box,@Code = BarCode,@Validity =ValidityDate from Cards 
Insert into Cards (BarCode,Box,ValidityDate) values (@Box,@Code,@Validity)
fetch next from c into @Box,@Code,@Validity
end
CLOSE c
DEALLOCATE c


end

我得到以下信息

11155232026    1        2013-05-18  

1       11155232026     2013-05-18  


...
...

这始终是第一行，并且 Box 和 Code 在每一行都在改变位置，我做错了什么？

Answer 1

您的第二个 fetch列顺序错误:

fetch next from c into @Code,@Box,@Validity
...
fetch next from c into @Box,@Code,@Validity

另一个问题是这个语句:

select @Box = Box,@Code = BarCode,@Validity =ValidityDate from Cards 

这有效地从 Cards 中获取随机行表，丢弃游标中的值。也许你可以澄清这条线应该做什么？