Storing dataframe in sql server

sql

#1

Dears,
Greetings.
Have a question please. If i have a data frame and i need to insert its columns in a SQL server database. while not all of its column will be exported to sqlserver database. i need to store some of its columns only. because order or columns at table is different from order of the data frame. How can i do that please ?
Appreciate your support please :slight_smile:


#2

There’s this short discussion on db.rstudio.com, odbc >> writing

I know dplyr has some functions to help with this, backend_db
I think you’ll want to work with db_create_table and db_insert_into


#3

Aren’t most of those functions moved to dbplyr now ?


#4

Hi @Hady, you can use the copy_to() command to move the data.frame into your SQL server.


#5

Order of columns is important in SQL. It helps to create a working data frame in the exact structure of your target database table - select() is your friend here. If you need an empty column use NULL for value.

For inserting consider script like this:

db_insert_into(con = your_db_connection,
               table = target_table,
               values = source_dataframe)

For an one-off operation it would be an overkill, but if you plan to repeat this operation often you could use a temporary “staging” table, which would be later flipped over to the target table.

During this “flipping over” you can apply a little database magic on your data - say filter out duplicate values, or append technical columns such as date & time of inserting and source of your data.

This flip might look like this code (written in PostgreSQL flavor of SQL)
dbSendQuery(your_db_connection, "insert into final select *, current_timestamp from stage on conflict (id) do nothing")


#6

Awesome :). Thanks and Appreciated


#7

Appreciated :). Thanks