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
dplyr has some functions to help with this,
I think you’ll want to work with
Aren’t most of those functions moved to
dbplyr now ?
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")
Awesome :). Thanks and Appreciated
Appreciated :). Thanks