{"id":8270,"date":"2019-08-04T14:58:34","date_gmt":"2019-08-04T12:58:34","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=8270"},"modified":"2019-08-04T15:06:25","modified_gmt":"2019-08-04T13:06:25","slug":"access-sql-figer-le-resultat-dune-requete-dans-une-table","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=8270","title":{"rendered":"ACCESS (SQL) : figer le r\u00e9sultat d&#8217;une requ\u00eate dans une table"},"content":{"rendered":"<p style=\"text-align: justify;\">Les requ\u00eates SQL permettent d&#8217;extraire des informations de bases de donn\u00e9es. Le r\u00e9sultat de ces requ\u00eates peut lui-m\u00eame \u00eatre interrog\u00e9 par une autre requ\u00eate (sous-requ\u00eate) et ainsi de suite. Appliqu\u00e9e \u00e0 des volumes de donn\u00e9es cons\u00e9quents, cette pratique de sous-requ\u00eates entra\u00eene des temps de traitements qui s&#8217;allongent \u00e0 chaque niveau d&#8217;interrogation. Pour pallier \u00e0 cet inconv\u00e9nient, il est possible de figer le r\u00e9sultat des requ\u00eates dans une table avant interrogation par la requ\u00eate suivante.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT Champs et calculs INTO TableDestination FROM TableOrigine<\/pre>\n<p style=\"text-align: justify;\">L&#8217;assertion <em>INTO TableDestination<\/em> envoie le r\u00e9sultat de la requ\u00eate <em>SELECT&#8230; FROM TableOrigine<\/em> dans une nouvelle table nomm\u00e9e <em>TableDestination<\/em>.<\/p>\n<p style=\"text-align: justify;\">Pour figer le r\u00e9sultat de la requ\u00eate dans une table situ\u00e9e dans une autre base de donn\u00e9es, il suffit d&#8217;ajouter une clause <em>IN<\/em> suivie de du chemin complet d&#8217;acc\u00e8s \u00e0 la base de donn\u00e9es externe :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">SELECT Champs et calculs INTO TableDestination IN 'C:\\base.accdb' FROM TableOrigine<\/pre>\n<p style=\"text-align: justify;\">L&#8217;exemple qui suit illustre l&#8217;utilisation de l&#8217;assertion <em>INTO&#8230; IN&#8230;<\/em>&nbsp;appliqu\u00e9e \u00e0 un FEC :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">SELECT CodeEntit\u00e9, JournalCode, JournalLib, Ecriturenum, EcritureDate, CompteNum, CompteLib, CompteAuxNum, CompteAuxLib, Pi\u00e8ceR\u00e9f, Pi\u00e8ceDate, EcritureLib, D\u00e9bit, Cr\u00e9dit\r\nINTO ECRITURES IN 'C:\\ANA-FEC.accdb'\r\nFROM ECRITURES_brutes\r\nWHERE Left([CompteNum],1)&lt;&gt;\"9\";<\/pre>\n<p style=\"text-align: justify;\">Les champs list\u00e9s dans la clause <em>SELECT<\/em> contenus dans la table <em>ECRITURES_brutes<\/em> (clause <em>FROM<\/em>) et dont le num\u00e9ro de compte g\u00e9n\u00e9ral ne commence pas par un 9 (<em>WHERE Left([CompteNum],1)&lt;&gt;&#8221;9&#8243;<\/em>) sont envoy\u00e9s dans la table <em>ECRITURES<\/em> cr\u00e9\u00e9e \u00e0 cet effet (<em>INTO<\/em>) dans la base de donn\u00e9es <em>ANA-FEC.accdb<\/em> stock\u00e9es sur le disque C:\\ (clause <em>IN<\/em>).<\/p>\n<p style=\"text-align: justify;\">Dans l&#8217;hypoth\u00e8se o\u00f9 la table <em>ECRITURES<\/em> existait d\u00e9j\u00e0, une <a href=\"https:\/\/www.auditsi.eu\/?p=7000\">requ\u00eate insertion<\/a> permettrait d&#8217;ins\u00e9rer le r\u00e9sultat de l&#8217;extraction \u00e0 la suite de ses donn\u00e9es :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">INSERT INTO ECRITURES IN 'C:\\ANA-FEC.accdb'\r\nSELECT CodeEntit\u00e9, JournalCode, JournalLib, Ecriturenum, EcritureDate, CompteNum, CompteLib, CompteAuxNum, CompteAuxLib, Pi\u00e8ceR\u00e9f, Pi\u00e8ceDate, EcritureLib, D\u00e9bit, Cr\u00e9dit\r\nFROM ECRITURES_brutes WHERE Left([CompteNum],1)&lt;&gt;\"9\";<\/pre>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?tag=access\">Ma\u00eetriser ACCESS<\/a> \/&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?cat=1820\">Ma\u00eetriser le langage de requ\u00eate SQL<\/a>&nbsp;\/ <a href=\"https:\/\/www.auditsi.eu\/?cat=50\">Analyse de donn\u00e9es<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"ACCESS%20%28SQL%29%20%3A%20figer%20le%20r%C3%A9sultat%20d%27une%20requ%C3%AAte%20dans%20une%20table\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Les requ\u00eates SQL permettent d&#8217;extraire des informations de bases de donn\u00e9es. Le r\u00e9sultat de ces requ\u00eates peut lui-m\u00eame \u00eatre interrog\u00e9 par une autre requ\u00eate (sous-requ\u00eate) et ainsi de suite. Appliqu\u00e9e \u00e0 des volumes de donn\u00e9es cons\u00e9quents, cette pratique de sous-requ\u00eates entra\u00eene des temps de traitements qui s&#8217;allongent \u00e0 chaque niveau d&#8217;interrogation. Pour pallier \u00e0 cet &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=8270\" class=\"more-link\">Continue reading &lsquo;ACCESS (SQL) : figer le r\u00e9sultat d&#8217;une requ\u00eate dans une table&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"ACCESS%20%28SQL%29%20%3A%20figer%20le%20r%C3%A9sultat%20d%27une%20requ%C3%AAte%20dans%20une%20table\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[50,1820,282],"tags":[60,2044,1217,57,2056,2060,1643],"class_list":["post-8270","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-requetes-sql","category-sql","tag-access","tag-insert-into","tag-optimisation-du-code","tag-requete-sql","tag-select-into-from","tag-select-into-in-from","tag-sql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8270"}],"version-history":[{"count":5,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8270\/revisions"}],"predecessor-version":[{"id":8312,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8270\/revisions\/8312"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}