DECLARE @CategoryID INT
DECLARE @SubCategoryID INT
-- Check that the Category exists
IF NOT EXISTS (SELECT * FROM tblCategories WHERE CategoryName = @pCategoryName)
BEGIN
-- Your insert statement here, then grab the ID
SET @CurrencyID = scope_identity()
END
ELSE
BEGIN
-- Set the category ID here
END
VBA 宏的代码类似于：
Private Sub CommandButton1_Click()
Dim cnt As ADODB.Connection
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim intActiveRow As Long
Dim intInsuranceProduct As Variant
' Get our connection
Set cnt = CreateConnection()
' Read the input sheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
' Ignore the header row
intActiveRow = 2
' process every row into the database
Do While (wsSheet.Cells(intActiveRow, 1) "")
' execute the stored procedure, GenerateScript would create your SQL
cnt.Execute (GenerateScript(wsSheet, intActiveRow))
' increment i for row count
intActiveRow = intActiveRow + 1
Loop
End If
'Cleaning up.
cnt.Close
Set cnt = Nothing
Set wbBook = Nothing
Set wsSheet = Nothing
End Sub
I had a similar problem a while ago, and did not find any easy way to do this using an import wizard. The way I resolved the import (as this was a one-off task, and not something that was going to hang around) was to create a simple macro (VBA) from excel that would simply call a stored proc, using each row as the parameters.
The stored proc would intelligently insert each parameter (column), and then grab the ID to use as the foreign key on the next parameter insert.
For example:
DECLARE @CategoryID INT
DECLARE @SubCategoryID INT
-- Check that the Category exists
IF NOT EXISTS (SELECT * FROM tblCategories WHERE CategoryName = @pCategoryName)
BEGIN
-- Your insert statement here, then grab the ID
SET @CurrencyID = scope_identity()
END
ELSE
BEGIN
-- Set the category ID here
END
The VBA macro had code similar to:
Private Sub CommandButton1_Click()
Dim cnt As ADODB.Connection
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim intActiveRow As Long
Dim intInsuranceProduct As Variant
' Get our connection
Set cnt = CreateConnection()
' Read the input sheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
' Ignore the header row
intActiveRow = 2
' process every row into the database
Do While (wsSheet.Cells(intActiveRow, 1) "")
' execute the stored procedure, GenerateScript would create your SQL
cnt.Execute (GenerateScript(wsSheet, intActiveRow))
' increment i for row count
intActiveRow = intActiveRow + 1
Loop
End If
'Cleaning up.
cnt.Close
Set cnt = Nothing
Set wbBook = Nothing
Set wsSheet = Nothing
End Sub
您可能想要调查以前已知的 SSIS (SQL Server Integration Services)作为 DTS（数据转换服务）。 在 SSIS 中，可以使用 Excel 作为数据源，其中您可以指定数据的过滤器和转换以加载到适当的 SQL Server 表中。 它可能需要一些研究，但它是一个非常强大的工具，如果您需要做一些非开箱即用的事情，还支持创建脚本任务的能力。
You might want to investigate SSIS (SQL Server Integration Services) formerly known as DTS (Data Transformation Services). In SSIS there is the ability to use Excel as a data source where you can specify filters and transformations of the data for loading into the appropriate SQL Server tables. It might take a little research but it is a pretty robust tool, and also support the ability to create a Script task if you need to do something not Out of the Box.
Actually a good software to use that was developed specifically for this type of work is Relational Excel - there's a trial edition but it can be used past the trial period it just shows nag screens every once in a while. www.relationalexcel.com
发布评论
评论（4）
我不久前遇到了类似的问题，并且没有找到任何简单的方法来使用导入向导来完成此操作。 我解决导入问题的方法（因为这是一项一次性任务，而不是长期存在的任务）是从 Excel 创建一个简单的宏 (VBA)，该宏将简单地调用存储过程，使用每一行作为参数。
存储过程会智能地插入每个参数（列），然后获取 ID 用作下一个参数插入的外键。
例如：
DECLARE @CategoryID INT DECLARE @SubCategoryID INT -- Check that the Category exists IF NOT EXISTS (SELECT * FROM tblCategories WHERE CategoryName = @pCategoryName) BEGIN -- Your insert statement here, then grab the ID SET @CurrencyID = scope_identity() END ELSE BEGIN -- Set the category ID here ENDVBA 宏的代码类似于：
Private Sub CommandButton1_Click() Dim cnt As ADODB.Connection Dim wbBook As Workbook Dim wsSheet As Worksheet Dim intActiveRow As Long Dim intInsuranceProduct As Variant ' Get our connection Set cnt = CreateConnection() ' Read the input sheet Set wbBook = ActiveWorkbook Set wsSheet = wbBook.Worksheets(1) ' Ignore the header row intActiveRow = 2 ' process every row into the database Do While (wsSheet.Cells(intActiveRow, 1) "") ' execute the stored procedure, GenerateScript would create your SQL cnt.Execute (GenerateScript(wsSheet, intActiveRow)) ' increment i for row count intActiveRow = intActiveRow + 1 Loop End If 'Cleaning up. cnt.Close Set cnt = Nothing Set wbBook = Nothing Set wsSheet = Nothing End SubI had a similar problem a while ago, and did not find any easy way to do this using an import wizard. The way I resolved the import (as this was a one-off task, and not something that was going to hang around) was to create a simple macro (VBA) from excel that would simply call a stored proc, using each row as the parameters.
The stored proc would intelligently insert each parameter (column), and then grab the ID to use as the foreign key on the next parameter insert.
For example:
DECLARE @CategoryID INT DECLARE @SubCategoryID INT -- Check that the Category exists IF NOT EXISTS (SELECT * FROM tblCategories WHERE CategoryName = @pCategoryName) BEGIN -- Your insert statement here, then grab the ID SET @CurrencyID = scope_identity() END ELSE BEGIN -- Set the category ID here ENDThe VBA macro had code similar to:
Private Sub CommandButton1_Click() Dim cnt As ADODB.Connection Dim wbBook As Workbook Dim wsSheet As Worksheet Dim intActiveRow As Long Dim intInsuranceProduct As Variant ' Get our connection Set cnt = CreateConnection() ' Read the input sheet Set wbBook = ActiveWorkbook Set wsSheet = wbBook.Worksheets(1) ' Ignore the header row intActiveRow = 2 ' process every row into the database Do While (wsSheet.Cells(intActiveRow, 1) "") ' execute the stored procedure, GenerateScript would create your SQL cnt.Execute (GenerateScript(wsSheet, intActiveRow)) ' increment i for row count intActiveRow = intActiveRow + 1 Loop End If 'Cleaning up. cnt.Close Set cnt = Nothing Set wbBook = Nothing Set wsSheet = Nothing End Sub您可能想要调查以前已知的 SSIS (SQL Server Integration Services)作为 DTS（数据转换服务）。
在 SSIS 中，可以使用 Excel 作为数据源，其中您可以指定数据的过滤器和转换以加载到适当的 SQL Server 表中。 它可能需要一些研究，但它是一个非常强大的工具，如果您需要做一些非开箱即用的事情，还支持创建脚本任务的能力。
You might want to investigate SSIS (SQL Server Integration Services) formerly known as DTS (Data Transformation Services).
In SSIS there is the ability to use Excel as a data source where you can specify filters and transformations of the data for loading into the appropriate SQL Server tables. It might take a little research but it is a pretty robust tool, and also support the ability to create a Script task if you need to do something not Out of the Box.
实际上，专门为此类工作开发的一个很好用的软件是 Relational Excel - 有一个试用版，但它可以在试用期过后使用，只是偶尔会显示导航屏幕。 www.relationalexcel.com
Actually a good software to use that was developed specifically for this type of work is Relational Excel - there's a trial edition but it can be used past the trial period it just shows nag screens every once in a while. www.relationalexcel.com
解决方法非常快，就是使用MS Access中的“分析表”工具，你的表就会正常化，试试吧！
The solution very fast is to use the tool "Analyze Table" in MS Access, your Table will be Normalyze, try!