使用 Cursor 在 SQL Server 中插入表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16626022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting table in SQL server using Cursor
提问by Zame
I am trying to import an excel file to SQL , the file has 3 columns : Box - Code - Validity i am using the following query
我正在尝试将一个 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
I am getting the folowing
我得到以下
11155232026 1 2013-05-18
1 11155232026 2013-05-18
...
...
This is the first line always and Box and Code are changing places on each line , what i am doing wrong ?
这始终是第一行，Box 和 Code 正在改变每一行的位置，我做错了什么？
回答by Andomar
Your second fetchhas the wrong column order:
您的第二个fetch列顺序错误：
fetch next from c into @Code,@Box,@Validity
...
fetch next from c into @Box,@Code,@Validity
Another problem is this statement:
另一个问题是这个声明：
select @Box = Box,@Code = BarCode,@Validity =ValidityDate from Cards
That effectively fetches a random row from the Cardstable, discarding the values from the cursor. Perhaps you could clarify what this line is supposed to do?
这有效地从Cards表中获取随机行，丢弃游标中的值。也许你可以澄清这条线应该做什么？
回答by granadaCoder
declare @Holder table ( ColumnA varchar(24) , ColumnB varchar(32) , ColumnEtcEtc varchar(64))
Insert into @Holder (ColumnA, ColumnB, ColumnEtcEtc ) 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$]')
Delete From @Holder where DATALENGTH(LTRIM(RTRIM(ColumnB))) <=0
OR
或者
Insert into @Holder (ColumnA, ColumnB, ColumnEtcEtc ) 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$] where ColumnA = 2 ')
I'm not an expert on where clauses with excel queries, but I'm guessing there is a way.
我不是带有 excel 查询的 where 子句的专家，但我猜有一种方法。
