向SQL表添加数据行

I have a SQL database which consists of several tables like table 1 table 2 table 3 and so on

我有一个SQL数据库,它由几个表组成,如表1表2表3等

I have an excel sheet which contains about 17000 rows of data. The column headers of the excel sheet have been edited such that they match the column headers of the table (say table3) into which I want to import my excel data.

我有一张excel表,其中包含大约17000行数据。已编辑Excel工作表的列标题,使其与我要导入Excel数据的表(例如table3)的列标题相匹配。

For example, in excel sheet, the data is like,

例如,在excel表中,数据就像,

Name   Age
2      23

Table 3 is also of the same form, like

表3也是相同的形式

Name  Age
1      23

I would like to know, like how do I add the data in the above excel sheet to this table 3 in SQL database. Can someone give me some idea or instructions???

我想知道,比如如何将上述excel表中的数据添加到SQL数据库中的这个表3中。有人可以给我一些想法或指示吗?

4 个解决方案

#1

There is an Import / Export wizard included with SQL Server. It's pretty easy and intuitive to use, and as long as your data types match you should not have any problems.

SQL Server附带了一个导入/导出向导。它使用非常简单直观,只要您的数据类型匹配,您就不会有任何问题。

#2

You must see this: http://support.microsoft.com/kb/321686 There is a almost same question :http://*.com/questions/5141521/how-to-insert-data-from-excel-sheet-to-sql-server-2005

你必须看到这个:http://support.microsoft.com/kb/321686有一个几乎相同的问题:http://*.com/questions/5141521/how-to-insert-data-from-excel-sheet -to-SQL服务器2005

#3

I understand that you only want to add data to a specific table at a time.

据我所知,您只想一次向特定表添加数据。

Not sure which coding language you are using, but nevertheless, in short:

不确定您使用的是哪种编码语言,但简而言之:

Read the CSV file line by line with using a stream reader.

使用流阅读器逐行读取CSV文件。

Parse each line to some data type ( StringList or vector< String > etc. ). Delimiter will be ',' and quote character will be ' " '.

将每一行解析为某种数据类型(StringList或vector 等)。分隔符将是','并且引用字符将是'''。

Construct a 'value string' from the parsed data ( needed for SQL command ). What I mean by that: The value string should be of the format "rowid, integer, integer , 'text', 'text', ... ". The amount of 'entries' in this string should be equal to the number of columns in your table ( including the primary key of the table ).

从解析的数据构造“值字符串”(SQL命令需要)。我的意思是:值字符串的格式应为“rowid,integer,integer,'text','text',...”。此字符串中的“条目”数量应等于表中的列数(包括表的主键)。

Entries corresponding to SQL columns with value-type INTEGER need not be surrounded by single quotes. For SQL columns with value-type TEXT, TINYTEXT etc. they must be surrounded by single quotes, as shown above. I do not believe it is even required to specify the rowid, you can just replace that with a '?', giving you:

与值类型为INTEGER的SQL列对应的条目不必用单引号括起来。对于具有值类型TEXT,TINYTEXT等的SQL列,它们必须用单引号括起来,如上所示。我不相信甚至需要指定rowid,你可以用'?'替换它,给你:
```
 "?, integer, 'text', 'text', integer, ..." 
```
or whatever the case may be.

或者无论如何。
Then use a SQL INSERT INTO command to insert each row into the required table, something like the following:

然后使用SQL INSERT INTO命令将每一行插入所需的表,如下所示:
```
String sqlStatement = "INSERT INTO TableName VALUES ( " + valueString + " )";
```

In your case something like:

在你的情况下,像:

    String sqlStatement = "INSERT INTO Table3 VALUES ( ?, 2, 23, ... )";

Then execute the statement ( c++, SQLite, Note the sqlite3* should not really be declared here ):

然后执行语句(c ++,SQLite,注意sqlite3 *不应该在这里声明):
```
int      rc;
sqlite3* db;
char*    zErrMsg = 0;
rc = sqlite3_exec(db, sqlStatement, NULL, NULL, &zErrMsg);
if ( rc != SQLITE_OK )
   return false;
return true;
```
Repeat for each row of csv data. Use transactions and binding to increase performance.

对每行csv数据重复上述操作。使用事务和绑定来提高性能。

#4

Which Database are you using? as the method varies dependant on the database engine and tools available for it. As stated by SWeko, in SQL Server Management Studio you can indeed import Excel files directly, there are tools which are more generic, like DB visualiser, which may allow you to do the same into more databases, or you can use sql to generate the sql insert code for you by witing a template and using vlookup to define the data, then you would just run all the insert commands to add the data to the DB.

您使用的是哪个数据库?因为该方法取决于数据库引擎和可用的工具。正如SWeko所说,在SQL Server Management Studio中你确实可以直接导入Excel文件,有些工具更通用,比如DB可视化工具,可以让你在更多的数据库中做同样的工作,或者你可以用sql来生成sql通过引用模板并使用vlookup定义数据为您插入代码,然后您只需运行所有insert命令将数据添加到DB。

#1