如何使用查找值将记录插入到SQL中？ [英] How to insert records to SQL with looked up values?

问题描述

方案

我需要通过电子表格（唯一可用的选项）每日更新SQL 2008数据库。格式是非常基本的，但是可能有数百万条记录。 Column1和Column3将具有许多预定义的重复值，已经被拉出到单独的表中。

I need to update a SQL 2008 database daily via a spreadsheet (the only option available). The format is pretty basic, however there are potentially millions of records. Column1 and Column3 will have many predefined duplicate values, already pulled out into separate tables.

电子表格样本

Column1 Column2 Column3
Apple   10      Red
Apple   20      Red
Apple   15      Blue
Apple   21      Green
Orange  10      Orange
Orange  7       Orange
Orange  9       Red
Orange  70      Blue
Orange  10      Blue

数据库设置

我的数据库设置有三个单独的表：

My database is set up with three separate tables:

//Lookup_Column1
id type
1  Apple
2  Orange

//Lookup_Column3
id type
1  Red
2  Blue
3  Green
4  Orange

//Main - this is what should be inserted, after Column1
//and Column2 are matched to their respective ID's
key Column1 Column2 Column3
1   1       10      1
2   1       20      1
3   1       15      2
4   1       21      3
5   2       10      4
6   2       7       4
7   2       9       1
8   2       70      2
9   2       10      2

问题

写SQL以插入与查找表中的信息相匹配的记录？如何从这开始：

How can I write the SQL to insert records that match the information from the lookup tables? How can I go from this:

INSERT INTO Main(Column1, Column2) VALUES ('Apple', 10, 'Red');

为此：

INSERT INTO Main(Column1, Column2) VALUES (1, 10, 1);
//pulled from lookup tables, where Apple = 1 and Red = 1

推荐答案

您可以尝试以下方式：

    INSERT INTO Main(Column1, Column2, Column3) VALUES 
    (
    (SELECT id FROM Lookup_Column1 WHERE type = 'Apple'),
    10, 
    (SELECT id FROM Lookup_Column3 WHERE type = 'Red')
    );

没有任何容错，但只要您可以解析您的电子表格，它将工作值到SELECT语句。

There isn't any fault-tolerance, but it would work as long as you could parse your spreadsheet values into SELECT statements.

这篇关于如何使用查找值将记录插入到SQL中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！