{"id":4537,"date":"2024-09-24T09:43:52","date_gmt":"2024-09-24T02:43:52","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4537"},"modified":"2024-09-24T09:45:12","modified_gmt":"2024-09-24T02:45:12","slug":"go-sql-server-insert","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/go-sql-server\/go-sql-server-insert\/","title":{"rendered":"Go SQL Server: Inserting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert a row into a table from a Go program.<\/p>\n\n\n\n

Insert a row into a table #<\/a><\/h2>\n\n\n\n

Step 1. Create a new file author.go<\/code> within the project directory with the following code:<\/p>\n\n\n

package main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"time\"<\/span>\n)\n\ntype Author struct {\n    AuthorID  int\n    FirstName string\n    LastName  string\n    BirthDate time.Time\n}\n\nfunc InsertAuthor(db *sql.DB, author *Author) (int64, error) {\n    \/\/ Prepare the INSERT statement<\/span>\n    stmt, err<\/span> := db.Prepare(\"INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);\"<\/span>)\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    defer stmt.Close()\n\n    \/\/ Execute the prepared statement<\/span>\n    result, err<\/span> := stmt.Exec(\n        sql.Named(\"FirstName\"<\/span>, author.FirstName),\n        sql.Named(\"LastName\"<\/span>, author.LastName),\n        sql.Named(\"BirthDate\"<\/span>, author.BirthDate),\n    )\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    \/\/ Get the number of row inserted<\/span>\n    rowInserted, err<\/span> := result.RowsAffected()\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    return<\/span> rowInserted, nil\n\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
type Author struct {\n    AuthorID  int\n    FirstName string\n    LastName  string\n    BirthDate time.Time\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
func InsertAuthor(db *sql.DB, author *Author) (int64, error) {\n    \/\/ Prepare the INSERT statement<\/span>\n    stmt, err<\/span> := db.Prepare(\"INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);\"<\/span>)\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    defer stmt.Close()\n\n    \/\/ Execute the prepared statement<\/span>\n    result, err<\/span> := stmt.Exec(\n        sql.Named(\"FirstName\"<\/span>, author.FirstName),\n        sql.Named(\"LastName\"<\/span>, author.LastName),\n        sql.Named(\"BirthDate\"<\/span>, author.BirthDate),\n    )\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    \/\/ Get the number of row inserted<\/span>\n    rowInserted, err<\/span> := result.RowsAffected()\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    return<\/span> rowInserted, nil\n\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, create a prepared statement with an SQL INSERT<\/code> statement:<\/p>\n\n\n
stmt, err<\/span> := db.Prepare(\"INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);\"<\/span>)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The @FirstName<\/code>, @LastName<\/code>, and @BirthDate<\/code> are placeholders. When executing the prepared statement, you need to pass the corresponding values to substitute these parameters. By using these parameters, you can prevent SQL injection attacks.<\/p>\n\n\n\n
defer stmt.Close()<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, execute the prepared statement with the values that come from the Author struct:<\/p>\n\n\n
result, err<\/span> := stmt.Exec(\n    sql.Named(\"FirstName\"<\/span>, author.FirstName),\n    sql.Named(\"LastName\"<\/span>, author.LastName),\n    sql.Named(\"BirthDate\"<\/span>, author.BirthDate),\n)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, return 0 and error if an error occurred:<\/p>\n\n\n
if<\/span> err != nil {\n   return<\/span> 0<\/span>, err\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the number of rows inserted:<\/p>\n\n\n
rowInserted, err<\/span> := result.RowsAffected()\nif<\/span> err != nil {\n    return<\/span> 0<\/span>, err\n} else<\/span> {\n    return<\/span> rowInserted, nil\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Modify the main.go<\/code> to call the InsertAuthor<\/code> function to insert a new row into the Authors table:<\/p>\n\n\n
package main\n\nimport<\/span> (\n    \"fmt\"<\/span>\n    \"os\"<\/span>\n    \"time\"<\/span>\n)\n\nfunc main() {\n    \/\/ Load the database configuration<\/span>\n    config, err<\/span> := LoadConfig()\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n\n    \/\/ Connect to the database<\/span>\n    db, err<\/span> := Connect(config)\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n\n    \/\/ Close the database connection once the main function exits<\/span>\n    defer db.Close()\n\n    \/\/ Insert an author<\/span>\n    author<\/span> := Author{\n        FirstName<\/span>: \"John\"<\/span>,\n        LastName<\/span>:  \"Doe\"<\/span>,\n        BirthDate<\/span>: time.Date(1980<\/span>, 1<\/span>, 1<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, time.UTC),\n    }\n\n    rowInserted, err<\/span> := InsertAuthor(db, &author)\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n    fmt.Printf(\"Row inserted: %d\\n\"<\/span>, rowInserted)\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, initialize an author struct for insertion:<\/p>\n\n\n
author := Author{\n    FirstName<\/span>: \"John\"<\/span>,\n    LastName<\/span>:  \"Doe\"<\/span>,\n    BirthDate<\/span>: time.Date(1980<\/span>, 1<\/span>, 1<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, time.UTC),\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert a new row into the Authors<\/code> table and print out the number of rows inserted:<\/p>\n\n\n
rowInserted, err<\/span> := InsertAuthor(db, &author)\nif<\/span> err != nil {\n    fmt.Println(err)\n    os.Exit(1<\/span>)\n}\nfmt.Printf(\"Row inserted: %d\\n\"<\/span>, rowInserted)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Open your terminal and run the program using the following command:<\/p>\n\n\n
go run main.go db.go author.go<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Row inserted: 1<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Launch SQL Server Management Studio (SSMS), connect to the database, and select data from the Authors table:<\/p>\n\n\n
SELECT * FROM authors<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Inserting a row and returning the inserted ID #<\/a><\/h2>\n\n\n\n
Step 1. Modify the InsertAuthor<\/code> function to insert a new row into the Authors<\/code> table and return the inserted author ID:<\/p>\n\n\n
func InsertAuthor(db *sql.DB, author *Author) (int64, error) {\n    \/\/ Prepare the INSERT statement<\/span>\n    stmt, err<\/span> := db.Prepare(`INSERT INTO Authors (FirstName, LastName, BirthDate) \n                             OUTPUT inserted.AuthorID\n                             VALUES (@FirstName, @LastName, @BirthDate);`<\/span>)\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    defer stmt.Close()\n\n    \/\/ Execute the prepared statement and return the query result as rows<\/span>\n    rows, err<\/span> := stmt.Query(\n        sql.Named(\"FirstName\"<\/span>, author.FirstName),\n        sql.Named(\"LastName\"<\/span>, author.LastName),\n        sql.Named(\"BirthDate\"<\/span>, author.BirthDate),\n    )\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n\n    \/\/ Get the author ID<\/span>\n    var<\/span> authorID int64\n    for<\/span> rows.Next() {\n        err = rows.Scan(&authorID)\n        if<\/span> err != nil {\n            return<\/span> 0<\/span>, err\n        }\n    }\n\n    return<\/span> authorID, nil\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works (We’ll focus on what changes):<\/p>\n\n\n\n
First, prepare an insert statement that inserts a new row into a table and returns the inserted author ID<\/code>:<\/p>\n\n\n
stmt, err<\/span> := db.Prepare(`INSERT INTO Authors (FirstName, LastName, BirthDate) \n                         OUTPUT inserted.AuthorID\n                         VALUES (@FirstName, @LastName, @BirthDate);`<\/span>)\nif<\/span> err != nil {\n    return<\/span> 0<\/span>, err\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the Query()<\/code> method to execute the prepared statement and return the query result as rows:<\/p>\n\n\n
rows, err<\/span> := stmt.Query(\n    sql.Named(\"FirstName\"<\/span>, author.FirstName),\n    sql.Named(\"LastName\"<\/span>, author.LastName),\n    sql.Named(\"BirthDate\"<\/span>, author.BirthDate),\n)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, iterate over the rows, read the author ID, and return it:<\/p>\n\n\n
var<\/span> authorID int64\nfor<\/span> rows.Next() {\n    err = rows.Scan(&authorID)\n    if<\/span> err != nil {\n        return<\/span> 0<\/span>, err\n    }\n}\n\nreturn<\/span> authorID, nil<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.go<\/code> to insert a new row into the Authors table and print out the inserted row:<\/p>\n\n\n
package main\n\nimport<\/span> (\n    \"fmt\"<\/span>\n    \"os\"<\/span>\n    \"time\"<\/span>\n)\n\nfunc main() {\n    \/\/ Load the database configuration<\/span>\n    config, err<\/span> := LoadConfig()\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n\n    \/\/ Connect to the database<\/span>\n    db, err<\/span> := Connect(config)\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n\n    \/\/ Close the database connection once the main function exits<\/span>\n    defer db.Close()\n\n    \/\/ Insert an author<\/span>\n    author<\/span> := Author{\n        FirstName<\/span>: \"Jane\"<\/span>,\n        LastName<\/span>:  \"Doe\"<\/span>,\n        BirthDate<\/span>: time.Date(1990<\/span>, 12<\/span>, 15<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, 0<\/span>, time.UTC),\n    }\n\n    authorID, err<\/span> := InsertAuthor(db, &author)\n    if<\/span> err != nil {\n        fmt.Println(err)\n        os.Exit(1<\/span>)\n    }\n\n    fmt.Printf(\"Author ID: %d\\n\"<\/span>, authorID)\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the program:<\/p>\n\n\n
<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n
go run main.go db.go author.go<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Author ID: 2<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the author with ID 2 has been inserted into the Authors<\/code> table.<\/p>\n\n\n\n
Step 4. Launch SQL Server Management Studio (SSMS), connect to the database, and select data from the Authors table:<\/p>\n\n\n
SELECT * FROM authors<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"Go<\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the Exec()<\/code> method to execute a prepared statement to insert a new row into a table and return the number of rows inserted.<\/li>\n\n\n\n
  • Use the Query()<\/code> method to execute a prepared statement to insert a new row into a table and return the inserted ID.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Next, close the prepared statement once the function returns using the defer<\/a> keyword:<\/p>\n\n\n
Step 3. Create the InsertAuthor<\/code> function that inserts a new row<\/a> into the Authors<\/code> table:<\/p>\n\n\n
Step 2. Define the Author<\/code> struct<\/a> that maps to the Authors<\/code> table in the SQL Server database in the author.go<\/code>:<\/p>\n\n\n