#BULK INSERT SQL tutorial

[thedbahub]

Efficient Data Import in SQL Server 2022: BCP vs. BULK INSERT vs. OPENROWSET

Ever found yourself swimming in an ocean of data, wondering the best way to import it into your SQL Server? You’re not alone. SQL Server 2022 comes to the rescue with a trio of tools designed to streamline this process: BCP, BULK INSERT, and OPENROWSET. Each has its unique flair for handling data, and I’m here to guide you through choosing the right tool for the job with some handy T-SQL…

[ryadel]

Wordpress: come aggiungere o togliere categorie a tutti gli articoli

In questo articolo cercherò di spiegare come effettuare una serie di operazioni sulle categorie di Wordpress associate agli articoli utilizzando delle query SQL che agiscano direttamente sul database. Le query di esempio che troverete pubblicate sono state scritte pensando a un database MySQL, ma dovrebbero funzionare - con alcune piccole modifiche - anche su qualsiasi altro DB Engine.

Operazioni preliminari

Inutile dire che, trattandosi di operazioni particolarmente delicate, è opportuno effettuare un backup completo di tutte le tabelle che andremo a modificare (o più semplicemente dell'intero database) prima di mettersi al lavoro, così da avere la possibilità di correggere immediatamente eventuali errori dovuti all'esecuzione di query errate o che non sortiscano l'effetto sperato. Se avete bisogno di uno strumento per assolvere a tale scopo potete utilizzare SQLyog, una soluzione semplice e gratuita che consente di effettuare il backup di singole tabelle e/o di interi database tramite una pratica interfaccia utente e con pochi click. Questo strumento, disponibile sia in versione Windows che su Linux, consente anche di effettuare tramite GUI le query che andremo a illustrare nei paragrafi successivi.

In alternativa, potete utilizzare lo strumento mysqldump da linea di comando, seguendo le istruzioni descritte nella guida ufficiale.

Articoli, Categorie e Relazioni

Prima di dare un'occhiata alle query, è opportuno spendere qualche minuto per comprendere le modalità con cui Wordpress organizza i vari articoli (post) e categorie (category) all'interno della propria base dati. Come probabilmente già sapete, il database utilizzato da Wordpress è di tipo relazionale (RDBMS): nello specifico, l'associazione tra articoli e categorie è realizzata attraverso una relazione di tipo molti a molti (many-to-many); questo significa che ciascun articolo può essere associato a più categorie e che, viceversa, ciascuna categoria può essere associata a più articoli. Per chi non lo sapesse, gli articoli sono memorizzati nella tabella wp_posts: questo significa che, per recuperare un articolo, occorre eseguire una query SQL di questo tipo: SELECT * FROM wp_posts WHERE ID = @ID; Dove @ID, ovviamente, va sostituito con l'ID dell'articolo che vogliamo recuperare. Per le categorie il discorso è leggermente più complesso: queste ultime vengono memorizzate come una tipologia particolare di oggetti, denominati terms, che si trovano nella tabella wp_terms; la definizione della tipologia di ciascun term, però, è memorizzata in una tabella diversa, ovvero wp_term_taxonomy. Questo significa che, per recuperare tutte le categorie, occorrerà fare una query SQL corredata da una istruzione JOIN nel seguente modo: SELECT * FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'category'; Veniamo ora alla relazione molti a molti tra articoli e categorie: quest'ultima si trova nella cartella wp_term_relationships, che contiene il campo object_id (relativo all'articolo) e il term_taxonomy_id (relativo alla categoria). Questo significa che, per recuperare tutte le categorie associate a un dato articolo di cui abbiamo l'ID, occorrerà fare una query SQL di questo tipo: SELECT * FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id JOIN wp_terms t ON t.term_id = tt.term_id WHERE tr.object_id = @PostID AND tt.taxonomy = 'category' Inserendo il post_id del nostro  articolo al posto del placeholder @PostID. Tutto chiaro? Mi auguro di si. Nei prossimi paragrafi vedremo come sarà possibile utilizzare queste informazioni per effettuare una serie di operazioni bulk sui nostri dati.

Aggiungere una categoria a tutti gli articoli

Ipotizziamo di aver appena creato una categoria "News" e di volerla aggiungere a tutti gli articoli: ovviamente potremmo utilizzare l'interfaccia utente offerta dal pannello di amministrazione di Wordpress, ma nell'ipotesi in cui avessimo a disposizione un numero di articoli molto elevato questa soluzione potrebbe non essere ottimale. Ecco come possiamo risolvere il problema con una query SQL: INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT ID, @CategoryID, 0 FROM wp_posts p WHERE p.post_type = 'post' AND p.ID NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); La query di cui sopra funziona in modo piuttosto semplice: inserisce all'interno della tabella wp_term_relationships (che ospita, come sappiamo, le relazioni tra articoli e categorie) una riga per cascun articolo, impostando l'ID dell'articolo come object_id e l'ID della nuova categoria come term_taxonomy_id. E' importante notare come la query contempli anche una condizione NOT IN, necessaria per "saltare" dall'operazione eventuali articoli già associati alla nuova categoria, in modo da evitare eventuali errori e/o relazioni duplicate. Ovviamente, al posto di @CategoryID dovremo inserire l'ID della categoria creata.

Aggiungere una categoria aggiuntiva agli articoli che ne hanno già una

Vediamo ora come aggiungere una categoria ulteriore a tutti gli articoli che hanno già una categoria esistente. Per fare una cosa del genere è necessario eseguire la seguente query SQL: INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT object_id, @CategoryID, 0 FROM wp_term_relationships tr2 WHERE tr2.term_taxonomy_id = @OldCategoryID AND object_id NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); Al posto di @CategoryID dovremo inserire l'ID della categoria aggiuntiva, mentre al posto di @OldCategoryID andrà utilizzato l'ID della categoria già presente. Come possiamo vedere la query è piuttosto simile alla precedente, con un paio di differenze sostanziali: Gli ID degli articoli ai quali associare la nuova categoria non vengono presi dalla tabella wp_post ma direttamente dalla tabella wp_term_relationships, visto che sono necessariamente presenti lì (in quanto ci interessano solo se associati alla vecchia categoria). L'istruzione WHERE è impostata in modo diverso: anziché restituire tutti gli articoli aventi tipologia "post" restituisce tutti i record presenti nella tabella wp_term_relationships associati alla vecchia categoria, dai quali recuperare l'object_id necessario per creare la nuova relazione con la categoria aggiuntiva.

Conclusioni

Per il momento è tutto: ci auguriamo che questo breve tutorial possa esservi di aiuto per effettuare operazioni bulk sugli articoli e/o sulle categorie del vostro sito Wordpress in modo semplice e veloce!   Read the full article

[etltestingtutorial01-blog]

Best ETL Testing Training Tools Online Course.

Whizdom Training gives 100% real-time, practical & placement directed ETL Testing training. Our ETL Testing program focuses from basic level training to excellent level training. Our ETL Testing Tools training in totally focused to get placement in MNC & certification on ETL Testing Tools after fulfillment of our course. Our team of ETL Testing tutors is ETL Testing certified experts with more further real-time knowledge in live projects.

ETL Testing Training Syllabus

DATA WAREHOUSE / SQL TUTORIAL

* Introduction To ETL and Datawarehousing

- How to Handle Data in Bulk ? - Data Ware House Solution. - What is ETL Process? - Different ETL Tools available. - What can be Data Sources? - Pre requisites for ETL Testing.

SQL Concepts

* SQL Installation and ETL Commands

- File v/s Database - Sql Server Installation,sql management studio install - Northwind Sample Database - DDL commands:Create, Alter, Rename and Drop - DML commands:Select,Insert, Update, Delete and Merge - TCL Begin, Commit, Rollback

* SQL Concepts - Part 1

- Filtering data using Clauses - where,LIKE,IN,NOT IN,IS NULL,IS NOT NULL,TOP AND DISTINCT - Constraints: PRIMARY KEY,FOREIGN KEY integrity, CHECK constraint,UNIQUE,NOT NULL,UNIQUE with - UNIQUE pair - Drop any constraint - Use of clauses: WHERE,LIKE,NOT LIKE - Operators: AND, OR - Use of IN,NOT IN,IS NULL,IS NOT NULL

[skqq-net]

Migrate WordPress from Localhost to Server – A Step-by-Step Guide

So your wordpress internet on-line web page on-line is interesting and now you want to switch it out of your localhost to are residing on-line web page on-line/internet webhosting, apt. Setting up and attempting out your web pages to your localhost is a lawful uncover and though one thing goes unsuitable, it doesn’t win an affect to your are residing internet on-line web page on-line. So on this tutorial, I’ll wade through the step-by-step configuration and will presumably disclose you the draw to add and switch your wordpress on-line web page on-line from a localhost to an online webhosting/are residing on-line web page on-line in two easy strategies.

Strategies

These are the 2 the draw to migrate wordpress from localhost to a are residing server, and I’ll give attention to each of them:

Migrate wordpress Blueprint from localhost to are residing server utilizing a plugin

Manually migrating wordpress Blueprint from localhost to are residing server

The primary formulation is unassuming and I’d point out it for each tech-savvy and non-tech-savvy customers as a result of it’s autonomous of your internet webhosting suppliers. On the other hand, should you’re a Cloudways particular person, you’ll protected that the handbook formulation is an easy draw to migrate your wordpress on-line web page on-line as successfully.

Prerequisites: To switch your wordpress on-line web page on-line from localhost to 1 different server, you want to be apparent:

You’ve got acquired gotten a neighborhood server setup to your laptop. (Throughout the occasion you don’t, watch our handbook on the draw to arrange wordpress on localhost utilizing XAMPP)

You’ve got acquired gotten a lawful internet internet webhosting perception that helps wordpress. (Proper right here’s our checklist of the ultimate uncover wordpress internet webhosting corporations you could presumably seemingly additionally rob from)

Whereas there are a selection of giant decisions, our steered wordpress internet webhosting is Cloudways, and we win partnered with them to boost our readers an peculiar Three Months Off coupon code.

Assign: I’m utilizing the Cloudways internet webhosting platform for demonstrating the migration job for this tutorial.

Let’s originate up!

Methodology 1 Migrate wordpress Blueprint from Localhost to Are residing Server The utilization of a Plugin

  Proper listed here are your entire steps I’ll train all of the blueprint during which by this job to uncover navigation easier for you:

Step 1: Arrange and setup the Duplicator plugin to your Localhost wordpress Step 2: Establishing a package Step 3: Switch the Installer.php and Archive(zip) file by an FTP Client like FileZilla Step 4: Mosey the Installer setup on the are residing server Step 5: Trying out the are residing on-line web page on-line

Whereas I’m utilizing the Duplicator plugin, listed beneath are one different migration plugins you could presumably seemingly additionally make the most of for the job:

Duplicator (Freemium)

All-in-One WP Migration (Free)

BackupBuddy (Prime cost)

UpdraftPlus wordpress Backup Plugin (UpdraftPlus Migrator) (Freemium)

WP Migrate DB (Prime cost)

Migrate Guru (Prime cost)

VaultPress (Prime cost)

Step 1: Arrange and Setup Duplicator Plugin on Your Localhost wordpress

First, you want to arrange and prompt the Duplicator plugin to your localhost wordpress on-line web page on-line (i.e., on XAMPP). For that, rush to ADD NEW plugin and sort Duplicator in search and click on on on on Arrange Now, and Activate it.

  Step 2: Establishing a Equipment

You’ll have a look at the Duplicator menu on the left panel within the wordpress Dashboard. Click on on Duplicator > Applications > Develop New

As soon as the package is created, merely click on on on the Subsequent Button.

  Now click on on on the Fabricate button.

This could train a number of seconds to assemble the package. The package creates a zipper file of your entire positioning’s plugins, themes, reveal materials, database, and wordpress recordsdata

It is doubtless you will seemingly now obtain the 2 recordsdata by clicking on One-Click on Bag as proven within the picture above.

Step 3: Switch the Installer.php and Archive(zip) File to your Cloudways Are residing Server Via an FTP Client FileZilla

First, begin a model new utility on Cloudways

After launching the making use of, you want to obtain an FTP shopper like FileZilla. It is doubtless you will seemingly obtain it from FileZilla-Mission.org.

To affix FileZilla, you want to produce it the grasp credentials of your Server (Host IP, Consumer, Password, and Port).

For FileZilla: Host = Public IP of Cloudways Username = Username of Cloudways Password = The password of Cloudways Port = 22

Assign: For refined operating, please rob out port 22

Yow will uncover all these tiny print in your Grasp Credentials beneath Server Administration within the Cloudways platform as proven within the picture beneath.

After you’ve enter the grasp credentials within the respective fields, click on on on Quickconnect.

Subsequent, rush to the Purposes folder, rob out your DB Establish folder (database title that’s in your Software program program uncover admission to tiny print), rob out the public_html folder and delete the WP-CONFIG.PHP file by FileZilla FTP, and add the Installer and Archive recordsdata out of your desktop to this folder.

Assign: Prior to you add the archive recordsdata, be apparent you delete the wp-config.php file out of your utility (positioned within the public_html folder).

Step 4: Mosey the Installer Setup On the Are residing Server

Now that you just’ve finished the importing job within the steps above, you want to flee your on-line web page on-line URL.

Subsequent, you could presumably watch the wordpress putting in setup:

Acceptable train away the “wp-admin/setup-config.php” from https://ift.tt/35lOTbo URL and alter it with “Installer.php” https://ift.tt/30Vc3SB.

Substitute it with “Installer.php” https://ift.tt/30Vc3SB.

Throughout the occasion you enter the YourSiteName/installer.php URL, the next setup configuration will appear to your conceal conceal:

Acceptable click on on on the Subsequent button!

Assign: The Duplicator plugin works most interesting with empty databases. As a result of this fact, prior to transferring forward, you want to train away all outdated data. For this, rush to Software program program Administration and click on on on on Launch Database Supervisor.

Now, you want to try on Tables to make a desire your entire tables within the database and click on on on the Fall button to train away the chosen tables as proven within the picture beneath.

Now transferring attend to the Duplicator deployment Setup, you could presumably seemingly additionally proceed with the configuration of duplicator by inserting MySQL Get right of entry to Credentials within the 2nd step.

Host = Localhost Database = “Establish of your DB” Consumer = “Username of your DB” Password = “Password of your DB”

Now right click on on on NEXT to arrange the setup.

Step 5: Trying out the Are residing Blueprint

Now you want to try it on a are residing on-line web page on-line so right click on on on Admin Login, and the wp-login web page will pop-up.

Subsequent, you want to produce the Localhost wordpress Blueprint admin credentials, i.e., the XAMPP credentials (now not cloudways uncover admission to credentials).

To protect on the steady facet, deactivate all plugins should you migrate the wordpress on-line web page on-line from a localhost to the Are residing server. To reactivate the deactivated plugins, navigate to Plugins > Put in Plugin, rob out Bulk Actions and click on on on on the Activate button as proven within the picture beneath.

As you could presumably seemingly additionally watch, it’s now not refined to switch a wordpress on-line web page on-line from a localhost to a are residing server. Now you moreover mght know that the Duplicator plugin is a extremely implausible system and makes it easier to migrate your wordpress on-line web page on-line to a are residing server.

  Methodology 2 Manually Migrate wordpress Blueprint from localhost to Are residing server

  On this blueprint, I’m going to reveal you easy strategies to switch your wordpress on-line web page on-line manually from the localhost to are residing on-line web page on-line/internet webhosting.

Whereas it’s already linked above, proper right here’s the draw to begin a model new utility on a model new server on Cloudways.

Step 1: Export Localhost wordpress Database

First, you want to export your Database file you created on XAMPP for wordpress. Clutch your localhost wordpress DB and click on on on on Export > Like a flash Export Methodology > Excessive-tail, and likewise you’ll watch a “.SQL” file in your obtain folder.

Step 2: Delete Database Tables Out of your Internet hosting DB

The subsequent flow into within the job is to begin your Database Supervisor by clicking on the Launch Database Supervisor button.

Subsequent, you want to delete your entire tables by checking Tables, and clicking on the Fall button.

Step 3: Import Localhost wordpress Database on Are residing Blueprint:

Now, the next within the job is to import that “.SQL” database file to your internet webhosting DB. For this, you want to click on on on Import > Get pleasure from Recordsdata, then rob out .SQL file out of your folder and click on on on on the Launch button, and click on on on on Perform.

Step 4: Alternate the Blueprint URL, Home, and Weblog title:

Subsequent, you want to swap your Blueprint URL, Home, and Weblog title for are residing server. Subsequent, analysis for wp_options desk in your database.

Assign: Throughout the occasion you had modified your prefix at arrange then it’s going to even be _options.

Now, click on on on wp_options or _options, and click on on on on Clutch Recordsdata.

Proper right here you right want to swap Three names:

Blueprint URL = “your Blueprint URL” Home = “Your Blueprint URL” Weblog title = “Your Blueprint URL with out HTTPS://”

Proper right here is “your Blueprint URL”, that you just want to repeat.

Click on on edit for each highlighted risk names talked about above and insert the above-mentioned values within the option_value topic. Your native arrange URL will seemingly analysis one thing like: http://localhost/check out. Then, right click on on on Put and likewise you’re carried out.

Congratulations! You’ve got acquired gotten efficiently migrated the wordpress on-line web page on-line from localhost to a are residing on-line web page on-line.

Conclusion

Now that you recognize each processes of taking your wordpress internet on-line web page on-line from Localhost to a are residing server, I’m apparent you mediate it’s an easy job. For the first step, it’s the Duplicator plugin that makes this job so easy. Secondly, it’s the Cloudways’ intuitive platform which makes the handbook job easy as successfully. So now your internet on-line web page on-line is interesting, enact you win a fab space title to hurry alongside facet it? If now not, proper right here’s an editorial on the draw to rob a ideally suited space title to your on-line web page on-line.

Everytime you win your space title and internet on-line web page on-line up and operating, you’re all on-line web page on-line to uncover a tag on-line!

The put up Migrate wordpress from Localhost to Server – A Step-by-Step Recordsdata regarded first on WPblog.

from WordPress https://ift.tt/2VrRhcr via IFTTT

[ryadel]

Wordpress: SQL query to add or remove categories on multiple posts

In this article I will try to explain how to perform a series of operations on the Wordpress categories associated with articles using SQL queries that act directly on the database. The sample queries have been written for a MySQL database: however, they should work - with a few minor changes - on any other Wordpress-compatible DB Engine as well.

Preliminary tasks

Needless to say, since we're going to perform some permanent operations to our Wordpress DB, it is definitely advisable to run a complete backup of all the tables that we are going to modify (or of the entire database), so that we'll be able to recover from SQL errors and/or unwanted changes. If you need a tool to accomplish this, we strongly suggest you to use either the official MySQL Workbench or SQLyog, a simple and free solution that allows you to backup individual tables and / or entire databases through a practical user interface and with a few clicks.

both of these tools are available in both Windows and Linux versions and will also allows you to make the queries that we're going to illustrate in the following paragraphs using a handy and convenient GUI interface, thus eliminating the need to use the command-line. However, if you like the command-line, you can perform the backup using the mysqldump console tool: if you need info about that tool you can check out the official guide.

Posts, Categories and Relationships

Before taking a look at the queries, it is advisable to spend a couple minutes to understand how Wordpress organizes the various posts and categories within its database. As you probably already know, Wordpress does use a relational database model (RDBMS): more specifically, posts and categories are linked together using a many-to-many relationship; this means that each post can be associated with multiple categories and, conversely, each category can be related with multiple posts. The posts are stored in the wp_posts table: this means that, to retrieve a specific post, you need to execute the following SQL query: SELECT * FROM wp_posts WHERE ID = @ID; It goes without saying that the @ID placeholder must be replaced with the post ID we want to retrieve. Categories works in a slightly different (and more complex) way: they are stored in the wp_terms table, together with other "terms" of different types (taxonomies);  they can be identified as categories because they do have the category taxonomy, but such information is stored in a different table (wp_term_taxonomy). This means that, if we want to retrieve all the categories, we'll need to execute the a SQL query with a JOIN statement in the following way: SELECT * FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'category'; Let's now address the many-to-many relationship between posts and categories: such info is stored in the wp_term_relationships table, which contains the object_id field (the post_id) and the term_taxonomy_id (the term_id of the category). This means that, in order to retrieve all the categories of a specific post, we can execute the following SQL query: SELECT * FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id JOIN wp_terms t ON t.term_id = tt.term_id WHERE tr.object_id = @PostID AND tt.taxonomy = 'category' Replacing the @PostID placeholder with our post's post_id. Now we know everything we need to perform some useful INSERT and/or DELETE queries to add and/or remove some categories to our posts.

Adding a new Category to all Posts

Let's assume that we have just created a "News" category, and we want to add it to all the articles: although we could definitely do that using GUI by logging to the Wordpress administration panel, such technique would be very slow (and error prone) if our Wordpress site had a lot of articles. Here is how we can solve the problem with a single SQL query: INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT ID, @CategoryID, 0 FROM wp_posts p WHERE p.post_type = 'post' AND p.ID NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); The above query works in a rather simple way: it retrieves all the posts from the wp_posts table (minus those who are already associated with the @CategoryID) and creates an entry in the wp_term_relationships table for each one of them. Needless to say, the @CategoryID must be replaced with the actual ID of the category we want to add.

Adding a new Category to all Posts with an existing Category

Let's now see how can we add an additional category to all post that are already associated with another, already existing category. Such task can be performed with the following SQL query: INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT object_id, @CategoryID, 0 FROM wp_term_relationships tr2 WHERE tr2.term_taxonomy_id = @OldCategoryID AND object_id NOT IN (SELECT object_id FROM wp_term_relationships tr3 WHERE tr3.term_taxonomy_id = @CategoryID); As we can see, we'll have to replace the @CategoryID with the ID of the additional category we want to add, and the @OldCategoryID placeholder with the existing category ID. The above query follow the same approach of the previous one, with the following key difference: the IDs of the posts that we need to add the new category to are not retrieved by querying the wp_post table; they are fetched from the wp_term_relationships instead. We are allowed to do that because we know they must be there, since we only want to retrieve those who already have a relationship (with the existing category); from these records we can retrieve the object_id, which is precisely what we need to create the new relationship with the additional category.

Conclusions

That's it for the time being: we definitely hope that this tutorial will help those who are looking for a convenient way to perform some bulk tasks with the posts and/or categories of their Wordpress site!   Read the full article