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
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
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