---
title: "SQLInsert"
language: "en"
type: "Symbol"
summary: "SQLInsert[conn, table, cols, data] inserts data into a table in an SQL connection."
canonical_url: "https://reference.wolfram.com/language/DatabaseLink/ref/SQLInsert.html"
source: "Wolfram Language Documentation"
related_guides: 
  - 
    title: "DatabaseLink Data Access and Manipulation"
    link: "https://reference.wolfram.com/language/DatabaseLink/guide/SQLDataAccessAndManipulation.en.md"
  - 
    title: "DatabaseLink SQL Operations"
    link: "https://reference.wolfram.com/language/DatabaseLink/guide/SQLDatabaseOperations.en.md"
  - 
    title: "Database Connections"
    link: "https://reference.wolfram.com/language/DatabaseLink/guide/DatabaseConnectionsAndResources.en.md"
related_functions: 
  - 
    title: "SQLExecute"
    link: "https://reference.wolfram.com/language/DatabaseLink/ref/SQLExecute.en.md"
  - 
    title: "SQLDelete"
    link: "https://reference.wolfram.com/language/DatabaseLink/ref/SQLDelete.en.md"
  - 
    title: "SQLSelect"
    link: "https://reference.wolfram.com/language/DatabaseLink/ref/SQLSelect.en.md"
  - 
    title: "SQLUpdate"
    link: "https://reference.wolfram.com/language/DatabaseLink/ref/SQLUpdate.en.md"
related_tutorials: 
  - 
    title: "DatabaseLink User Guide"
    link: "https://reference.wolfram.com/language/DatabaseLink/tutorial/Overview.en.md"
  - 
    title: "Inserting Data in DatabaseLink User Guide"
    link: "https://reference.wolfram.com/language/DatabaseLink/tutorial/InsertingData.en.md"
  - 
    title: "Inserting Data with Raw SQL in DatabaseLink User Guide"
    link: "https://reference.wolfram.com/language/DatabaseLink/tutorial/SQLInsertingData.en.md"
---
## DatabaseLink\`

# SQLInsert

SQLInsert[conn, table, cols, data] inserts data into a table in an SQL connection.

## Details and Options

* To use ``SQLInsert``, you first need to load [*DatabaseLink*](https://reference.wolfram.com/language/DatabaseLink/guide/SQLDatabaseOperations.en.md) using ``Needs["DatabaseLink`"]``.

* The following options can be given:

|                      |              |                                                                            |
| -------------------- | ------------ | -------------------------------------------------------------------------- |
| "ColumnSymbols"      | None         | symbols to be associated with results                                      |
| "EscapeProcessing"   | True         | translate escaped JDBC function syntax                                     |
| "FetchSize"          | Automatic    | JDBC driver hint for filling result sets                                   |
| "GetAsStrings"       | False        | return all results as strings                                              |
| "GetGeneratedKeys"   | False        | return keys associated with updated records                                |
| "MaxFieldSize"       | Automatic    | byte limit for variable-length column types                                |
| "MaxRows"            | Automatic    | the maximum number of rows to return                                       |
| "ShowColumnHeadings" | False        | whether to return headings with the results                                |
| "Timeout"            | \$SQLTimeout | the timeout for the query                                                  |
| "BatchSize"          | 1000         | process inserts in batches of this many records                            |
| "JavaBatching"       | True         | perform parameter batching in Java layer instead of Wolfram Language layer |

## Examples (5)

### Basic Examples (1)

```wl
In[1]:= Needs["DatabaseLink`"]
```

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the ``DatabaseLink`DatabaseExamples``` package, as described in [Using the Example Databases](https://reference.wolfram.com/language/DatabaseLink/tutorial/UsingTheExampleDatabases.en.md).

Open a connection:

```wl
In[2]:= conn = OpenSQLConnection["demo"];

In[3]:= SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]}];
```

Insert one row:

```wl
In[4]:= SQLInsert[conn, "TEST", {"COL1", "COL2"}, {10, 10.5}]

Out[4]= 1

In[5]:= SQLSelect[conn, "TEST"]

Out[5]= {{10, 10.5}}
```

Insert multiple rows:

```wl
In[6]:= SQLInsert[conn, "TEST", {"COL1", "COL2"}, {{10, 10.5}, {20, 55.1}}]

Out[6]= {1, 1}

In[7]:= SQLSelect[conn, "TEST"]

Out[7]= {{10, 10.5}, {10, 10.5}, {20, 55.1}}

In[8]:= SQLDropTable[conn, "TEST"];

In[9]:= CloseSQLConnection[conn];
```

### Scope (1)

#### Working with Dates and Times (1)

```wl
In[1]:= Needs["DatabaseLink`"]
```

Date and time data may be supplied using ``DateObject``, ``TimeObject`` or ``SQLDateTime``. Create a table with ``DATE``, ``TIME``, and ``DATETIME`` column types:

```wl
In[2]:= conn = OpenSQLConnection[JDBC["HSQL(Memory)", "temp"]];

In[3]:=
SQLCreateTable[conn, SQLTable["DateTimeTable"], {SQLColumn["Col1", "DataTypeName" -> "INTEGER", "Nullable" -> True], 
	SQLColumn["Col2", "DataTypeName" -> "DATE", "Nullable" -> True], SQLColumn["Col3", "DataTypeName" -> "TIME", "Nullable" -> True], 
	SQLColumn["Col4", "DataTypeName" -> "TIMESTAMP", "Nullable" -> True]}];
```

Generate some data for insertion:

```wl
In[4]:=
lower = Round@AbsoluteTime[Now];
upper = Round@AbsoluteTime[DatePlus[Now, {1, "Year"}]];
randomRow[i_Integer] := With[{t = DateList@DateObject[RandomInteger[{lower, upper}]]}, 
	{RandomInteger[100], DateObject[t], TimeObject[t[[-3 ;; ]]], SQLDateTime[t]}
	]

In[5]:=
in = Table[randomRow[i], {i, 10 ^ 4}];
RandomSample[in, 1]

Out[5]= {{100, DateObject[{2016, 2, 29}, TimeObject[{20, 47, 41.}, TimeZone -> -6.], TimeZone -> -6.], TimeObject[{20, 47, 41.}, TimeZone -> -6.], SQLDateTime[{2016, 2, 29, 20, 47, 41.}]}}
```

Insert the generated data:

```wl
In[6]:= SQLInsert[conn, "DateTimeTable", {"Col1", "Col2", "Col3", "Col4"}, in]//Length

Out[6]= 10000
```

Selected date and time data will return with head ``SQLDateTime`` :

```wl
In[7]:= Column[out = SQLSelect[conn, SQLTable["DateTimeTable"], "MaxRows" -> 3]]

Out[7]=
{85, SQLDateTime[{2016, 5, 8}], SQLDateTime[{22, 44, 47}], SQLDateTime[{2016, 5, 8, 22, 44, 47.}]}
{25, SQLDateTime[{2016, 7, 23}], SQLDateTime[{10, 33, 52}], SQLDateTime[{2016, 7, 23, 10, 33, 52.}]}
{27, SQLDateTime[{2016, 4, 25}], SQLDateTime[{18, 39, 40}], SQLDateTime[{2016, 4, 25, 18, 39, 40.}]}
```

Express the selected data in its original form:

```wl
In[8]:= Inner[Apply, {Identity, DateObject, TimeObject, Identity}, #, List]& /@ out//Column

Out[8]=
{85, DateObject[{2016, 5, 8}], TimeObject[{22, 44, 47.}, TimeZone -> -6.], {2016, 5, 8, 22, 44, 47.}}
{25, DateObject[{2016, 7, 23}], TimeObject[{10, 33, 52.}, TimeZone -> -6.], {2016, 7, 23, 10, 33, 52.}}
{27, DateObject[{2016, 4, 25}], TimeObject[{18, 39, 40.}, TimeZone -> -6.], {2016, 4, 25, 18, 39, 40.}}
```

Clear the table:

```wl
In[9]:= SQLDelete[conn, "DateTimeTable"]

Out[9]= 10000
```

A ``DATE`` column will ignore minute, hour, and second elements in supplied date constructs. A ``TIME`` column will ignore year, month, and day elements in supplied date constructs.

```wl
In[10]:=
SQLInsert[conn, "DateTimeTable", {"Col2", "Col3"}, {
	{DateObject[], DateObject[]}
	}];

In[11]:= SQLSelect[conn, "DateTimeTable", {"Col2", "Col3", "Col4"}]

Out[11]= {{SQLDateTime[{2016, 1, 28}], SQLDateTime[{14, 34, 34}], Null}}
```

For column types that record both date and time elements, the time will be taken to be 00:00:00 if not supplied. The date will be taken to be today if not supplied:

```wl
In[12]:=
SQLDelete[conn, "DateTimeTable"];
	
SQLInsert[conn, "DateTimeTable", {"Col4"}, {
	{DateObject[{2019, 1, 1}]}, 
	{TimeObject[]}
	}];
	
SQLSelect[conn, "DateTimeTable", {"Col4"}]//Column

Out[12]=
{SQLDateTime[{2019, 1, 1, 0, 0, 0.}]}
{SQLDateTime[{2016, 1, 28, 14, 34, 17.7387}]}

In[13]:= SQLDropTable[conn, "DateTimeTable"];

In[14]:= CloseSQLConnection[conn];
```

### Options (2)

#### "BatchSize" (1)

```wl
In[1]:=
Needs["DatabaseLink`"];

Needs["JLink`"];
```

Adjust batch size to trade off memory use and speed when issuing queries with long parameter lists:

```wl
In[2]:=
AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];

connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpec

Out[2]=
DatabaseLink`SQLConnection[DatabaseLink`JDBC["oracle.jdbc.driver.OracleDriver", 
  "jdbc:oracle:thin:@dillont2win.wri.wolfram.com:1521/xe"], 
 JLink`Objects`vm1`JavaObject22843958975528961, 1, "Catalog" -> Automatic, "Description" -> None, 
 "Location" -> None, "Name" -> None, "Password" -> "GUEST", "Properties" -> {}, 
 "ReadOnly" -> Automatic, "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic, 
 "UseConnectionPool" -> Automatic, "Username" -> "GUEST", "Version" -> None]

In[3]:=
SQLCreateTable[conn, SQLTable["SCRATCH"], {
	SQLColumn["A", "DataTypeName" -> "FLOAT"], 
	SQLColumn["B", "DataTypeName" -> "INTEGER"], 
	SQLColumn["C", "DataTypeName" -> "VARCHAR", "DataLength" -> 128]
	}]

Out[3]= 0
```

Generate random data for insertion:

```wl
In[4]:= randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];

In[5]:= data = randomRows[2 * 10 ^ 5];

In[6]:= ByteCount@data

Out[6]= 30391792
```

Compute memory in use, including both kernel and Java layers:

```wl
In[7]:=
javaMem[] := Module[{rt}, 
	LoadJavaClass["java.lang.Runtime"];
	rt = Runtime`getRuntime[];
	rt@totalMemory[](* allocated; some is free for jvm use *)
	];
memReport[] := TableForm[List@{MemoryInUse[], javaMem[], MemoryInUse[] + javaMem[]}, TableHeadings -> {None, {"Kernel", "Java", "Total"}}]

In[8]:= memReport[]

Out[8]=
| "Kernel"  | "Java"   | "Total"   |
| :-------- | :------- | :-------- |
| 143123688 | 10043392 | 153223416 |
```

Run query with small batch size, resulting in relatively light Java-side memory use:

```wl
In[9]:=
AbsoluteTiming@SQLInsert[conn, "SCRATCH", {"A", "B", "C"}, data, 
	"BatchSize" -> 10
	]//First

Out[9]= 27.035662

In[10]:= memReport[]

Out[10]//TableForm=
| "Kernel"  | "Java"    | "Total"   |
| :-------- | :-------- | :-------- |
| 143379368 | 102588416 | 245967936 |
```

Reset:

```wl
In[11]:=
SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]

Out[11]//TableForm=
| "Kernel"  | "Java"  | "Total"   |
| :-------- | :------ | :-------- |
| 143491328 | 8478720 | 151976480 |
```

Rerun with larger batch size, necessitating fewer server trips:

```wl
In[12]:=
AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"BatchSize" -> 100000
	]//First

Out[12]= 4.953815
```

Java memory use is higher in this case:

```wl
In[13]:= memReport[]

Out[13]//TableForm=
| "Kernel"  | "Java"    | "Total"   |
| :-------- | :-------- | :-------- |
| 143511920 | 205586432 | 349098504 |

In[14]:= SQLDropTable[conn, "SCRATCH"]

Out[14]= 0

In[15]:= CloseSQLConnection[conn];
```

#### "JavaBatching" (1)

```wl
In[1]:=
Needs["DatabaseLink`"];
Needs["JLink`"];
```

Switch parameter batching from Java layer to Wolfram Language layer to trade speed for memory:

```wl
In[2]:=
AddToClassPath["/Users/me/Documents/DatabaseLink/drivers"];
connSpec = {JDBC["oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@server.domain.com:1521:xe"], Username -> "GUEST", Password -> "GUEST"};conn = OpenSQLConnection@@connSpec

Out[2]=
DatabaseLink`SQLConnection[DatabaseLink`JDBC["oracle.jdbc.driver.OracleDriver", 
  "jdbc:oracle:thin:@dillont2win.wri.wolfram.com:1521/xe"], 
 JLink`Objects`vm1`JavaObject22843958975528961, 1, "Catalog" -> Automatic, "Description" -> None, 
 "Location" -> None, "Name" -> None, "Password" -> "GUEST", "Properties" -> {}, 
 "ReadOnly" -> Automatic, "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic, 
 "UseConnectionPool" -> Automatic, "Username" -> "GUEST", "Version" -> None]

In[3]:=
SQLCreateTable[conn, SQLTable["SCRATCH"], {
	SQLColumn["A", "DataTypeName" -> "FLOAT"], 
	SQLColumn["B", "DataTypeName" -> "INTEGER"], 
	SQLColumn["C", "DataTypeName" -> "VARCHAR", "DataLength" -> 128]
	}]

Out[3]= 0
```

Generate random data for insertion:

```wl
In[4]:= randomRows[n_] := Transpose[{RandomInteger[10000, n], RandomReal[1, n], RandomChoice[WordData[], n]}];

In[5]:= data = randomRows[5 * 10 ^ 4];

In[6]:= ByteCount@data

Out[6]= 7599672
```

Compute memory in use, including both kernel and Java layers:

```wl
In[7]:=
javaMem[] := Module[{rt}, 
	LoadJavaClass["java.lang.Runtime"];
	rt = Runtime`getRuntime[];
	rt@totalMemory[](* allocated; some is free for jvm use *)
	];
memReport[] := TableForm[List@{MemoryInUse[], javaMem[], MemoryInUse[] + javaMem[]}, TableHeadings -> {None, {"Kernel", "Java", "Total"}}]

In[8]:= memReport[]

Out[8]//TableForm=
| "Kernel"  | "Java"  | "Total"   |
| :-------- | :------ | :-------- |
| 127764344 | 9306112 | 137127360 |
```

Run query with Java-side batching:

```wl
In[9]:=
AbsoluteTiming@SQLInsert[conn, "SCRATCH", {"A", "B", "C"}, data, 
	"JavaBatching" -> True, 
	"BatchSize" -> 10
	]//First

Out[9]= 6.800207

In[10]:= memReport[]

Out[10]//TableForm=
| "Kernel"  | "Java"   | "Total"   |
| :-------- | :------- | :-------- |
| 128021048 | 32124928 | 160145824 |
```

Reset:

```wl
In[11]:=
SQLDelete[conn, "SCRATCH"];
CloseSQLConnection[conn];
ReinstallJava[];
conn = OpenSQLConnection@@connSpec;
memReport[]

Out[11]//TableForm=
| "Kernel"  | "Java"  | "Total"   |
| :-------- | :------ | :-------- |
| 128133576 | 7905280 | 136045376 |
```

Run query with Wolfram Language-side batching:

```wl
In[12]:=
AbsoluteTiming@SQLExecute[conn, "INSERT INTO SCRATCH (A, B, C) VALUES (?, ?, ?)", 
	data, 
	"JavaBatching" -> False, 
	"BatchSize" -> 10
	]//First

Out[12]= 11.346014
```

Total memory use is lower in this case:

```wl
In[13]:= memReport[]

Out[13]//TableForm=
| "Kernel"  | "Java"  | "Total"   |
| :-------- | :------ | :-------- |
| 128642176 | 9306112 | 137948384 |

In[14]:= SQLDropTable[conn, "SCRATCH"];

In[15]:= CloseSQLConnection[conn];
```

### Possible Issues (1)

A DateObject entered without a setting for the ``TimeZone`` option will use the default ``\$TimeZone``. Note that a numeric value for the time zone will not adjust for daylight saving that might apply to the date.

Here the value of \$TimeZone is a numerical value.

```wl
In[1]:= $TimeZone

Out[1]= -5.
```

This time zone setting is used by DateObject[{2017,1,1}].

```wl
In[2]:= data = {25, DateObject[{2017, 3, 12}], TimeObject[] , DateObject[{2017, 1, 1}]};

In[3]:= SQLInsert[conn, "DateTimeTable", {"Col1", "Col2", "Col3", "Col4"}, data]

In[4]:= 1
```

The time returned by SQLDateTime comes back using the default timezone of "America/Chicago".

```wl
In[5]:= SQLSelect[conn, "DateTimeTable"]

In[6]:= {{25, SQLDateTime[{2017, 3, 11}], SQLDateTime[{10, 45, 39.}], SQLDateTime[{2016, 12, 31, 23, 0, 0.}]}}
```

To avoid this problem, use a named zone ID or  ``\$TimeZoneEntity``.

```wl
In[7]:= data = {26, DateObject[{2017, 3, 12}, TimeZone -> "America/Chicago"], TimeObject[] , DateObject[{2017, 1, 1}, TimeZone -> $TimeZoneEntity]};

In[8]:= SQLInsert[conn, "DateTimeTable", {"Col1", "Col2", "Col3", "Col4"}, data]

In[9]:= 1

In[10]:= SQLSelect[conn, "DateTimeTable"]

In[11]:= {{26, SQLDateTime[{2017, 3, 12}], SQLDateTime[{10, 46, 30.}], SQLDateTime[{2017, 1, 1, 0, 0, 0.}]}}
```

## See Also

* [SQLExecute](https://reference.wolfram.com/language/DatabaseLink/ref/SQLExecute.en.md)
* [SQLDelete](https://reference.wolfram.com/language/DatabaseLink/ref/SQLDelete.en.md)
* [SQLSelect](https://reference.wolfram.com/language/DatabaseLink/ref/SQLSelect.en.md)
* [SQLUpdate](https://reference.wolfram.com/language/DatabaseLink/ref/SQLUpdate.en.md)

## Tech Notes

* [*DatabaseLink* User Guide](https://reference.wolfram.com/language/DatabaseLink/tutorial/Overview.en.md)
* [Inserting Data in *DatabaseLink* User Guide](https://reference.wolfram.com/language/DatabaseLink/tutorial/InsertingData.en.md)
* [Inserting Data with Raw SQL in *DatabaseLink* User Guide](https://reference.wolfram.com/language/DatabaseLink/tutorial/SQLInsertingData.en.md)

## Related Guides

* [DatabaseLink Data Access and Manipulation](https://reference.wolfram.com/language/DatabaseLink/guide/SQLDataAccessAndManipulation.en.md)
* [DatabaseLink SQL Operations](https://reference.wolfram.com/language/DatabaseLink/guide/SQLDatabaseOperations.en.md)
* [Database Connections](https://reference.wolfram.com/language/DatabaseLink/guide/DatabaseConnectionsAndResources.en.md)