Lesson 1

When you create a new database, it doesn't contain any data. In most cases, an application needs some data already in place before it's usable at all, and even after that initial load, a running system often needs periodic data loads to stay current, or periodic extracts to archive old data out and keep performance from degrading. Loading data in and extracting data out is one of the core jobs of a SQL Server database administrator, and this module covers the current tools SQL Server 2025 provides for doing both.

• Learning Objectives
  By the time you complete this module, you should be able to:
  1. Load data with the INSERT statement
  2. Load data with the SELECT INTO statement
  3. Use the BULK INSERT statement
  4. Use the bcp command-line utility
  5. Identify the key features of SQL Server Integration Services (SSIS)
  6. Use SSIS, through SQL Server Data Tools, to build a package
  7. Schedule and run an SSIS package
  8. Identify the key features of the SQL Server Import and Export Wizard
  9. Run the SQL Server Import and Export Wizard

SQL Server 2025 gives you several ways to move data in and out of a database, and which one fits depends mostly on volume, how much transformation the data needs along the way, and whether the job runs once or on a recurring schedule. None of these tools replaces the others entirely; they overlap, and a real administrator typically ends up comfortable with several of them rather than picking one and using it exclusively.

For straightforward, one-time or small-scale moves, plain T-SQL does the job. INSERT handles individual rows or small result sets, the same statement you've already used throughout this series, just now in the specific context of populating a newly created table rather than modifying an existing one. SELECT INTO goes a step further: it copies an entire query result into a brand-new table in a single statement, creating that table's structure automatically from the shape of the query itself, which makes it a quick way to spin off a working copy of some existing data without writing a separate CREATE TABLE statement first.

For large volumes of data coming from flat files, BULK INSERT and its command-line counterpart bcp are built for speed rather than flexibility. BULK INSERT is a T-SQL statement that loads data from a file directly into a table, something along the lines of BULK INSERT dbo.SalesOrders FROM 'C:\Data\orders.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2);, telling SQL Server where the file is, how its columns are separated, and whether to skip a header row. The bcp utility does the same basic job from outside SQL Server entirely, run from a command prompt rather than a query window, which matters once the load needs to run from a script or a scheduled task rather than from inside SSMS. Both support minimal logging under the right conditions, generally an empty target table or a database in the simple or bulk-logged recovery model, and both move far more data per second than row-by-row INSERT statements would for anything beyond a modest row count. Running either one requires more than ordinary table permissions, too: the login performing the bulk load needs either membership in the bulkadmin fixed server role or the ADMINISTER BULK OPERATIONS server permission, on top of the usual INSERT permission on the target table, a detail that connects directly back to the permissions work covered earlier in this series.

For anything that needs real transformation along the way, cleaning, aggregating, or reshaping data as it moves rather than just copying it, SQL Server Integration Services (SSIS) is the full-featured ETL tool built for the job. You design an SSIS package through SQL Server Data Tools, the Visual Studio workload this course's first module already introduced, laying out a workflow of individual steps rather than writing one long script. A package is organized into two layers: a control flow, the overall sequence of tasks, send an email, run a script, execute another package, and one or more data flows nested inside it, each one defining an actual source, any transformations applied in between, and a destination. A package built this way can extract from several different kinds of sources at once, transform each stream as needed, and load the results into one or more destination tables, and once it's built, it can be scheduled and run automatically through SQL Server Agent, the same tool this course has already covered for other administration tasks. SSIS is genuinely the right tool once a load needs that level of structure; for a single flat file going into a single table with no transformation at all, it's usually more setup than the job calls for, and BULK INSERT or bcp gets there faster.

It's worth being precise about one older piece of terminology that still comes up, since older material sometimes treats it as a thing you need to learn in its own right. Data Transformation Services, or DTS, was SQL Server's original ETL technology, dating back to SQL Server 2000, and SSIS replaced it long ago, back in the SQL Server 2005 release. The current SQL Server Import and Export Wizard still ships as DTSWizard.exe, and Microsoft's own documentation for SQL Server 2025 confirms it, along with the wizard it launches, remains fully supported, not deprecated or discontinued, across every edition from Express up through Enterprise. That old executable name is simply a holdover from the technology it used to launch; the wizard itself is a modern, actively documented tool for quick, mostly GUI-driven data transfers between a source and a destination, walking you through picking a source, a destination, and how the columns on each side should map to each other. It can save the transfer it builds as an actual SSIS package if you want to run it again later, rather than walking through the wizard's screens a second time, which is really the practical link between the two tools: the wizard is a quick way to get a simple transfer running, or a quick way to generate a starting SSIS package you'd then refine further in SQL Server Data Tools. There's no separate "DTS Designer" and no standalone "DTS package" format to learn in SQL Server 2025; that older model has been SSIS, accessed through SQL Server Data Tools, for a very long time now, and treating the wizard's old executable name as evidence that DTS itself is still a current, separate technology is a common but genuinely outdated misreading of what's actually installed. In practice, you'll usually launch the wizard from inside SSMS rather than hunting for the executable directly: right-click a database in Object Explorer and choose Tasks, then either Import Data or Export Data, depending on which direction you're moving data. It's available from the Start menu and the command line too, but the SSMS route is the one you'll use most often, since you're typically already connected to the database you're loading or extracting from anyway.

A quick-reference table is worth having before the lessons ahead get into each tool individually:

Tool	Best Suited For
INSERT	A handful of rows, or values you already know and are typing directly
SELECT INTO	Copying an entire query result into a brand-new table in one step
BULK INSERT	Large flat-file loads run from inside a T-SQL script
bcp	Large flat-file loads run from a command line or an external script
SSIS	Multi-source loads that need real transformation, on a repeatable schedule
Import and Export Wizard	A quick, mostly GUI-driven one-time transfer, or a starting point for an SSIS package
Linked servers / OPENROWSET	Querying another data source directly, without staging it as a file first

A couple of other options are worth knowing exist, even though they're not this module's main focus. Linked servers and OPENROWSET let you query another data source, another SQL Server instance, another database platform entirely, or a file, as if it were a local table, which is useful when you need to pull data from somewhere else into a transformation or load without staging it as a separate file first. They solve a narrower problem than SSIS does, but for a single query that needs to reach across to another system, they're often simpler than building a full package for it.

The DP-300 exam, covered earlier in this course as the certification that actually aligns with this series, includes a "plan and implement a migration strategy" subsection under its "plan and implement data platform resources" skill area, covering things like evaluating offline versus online migration approaches and implementing a migration into Azure. The exam's own study guide doesn't name specific tools like bcp, BULK INSERT, or SSIS directly, but an offline migration strategy in practice is usually built from exactly this kind of toolset: exporting data out of a source system with one of these tools, moving it, and loading it into the destination with another. Learning these tools well for this course's own sake puts you most of the way toward understanding what a real offline migration looks like mechanically, even though the exam itself tests the surrounding planning and strategy decisions rather than the specific syntax covered in this module.

A fair amount of the data you load and extract in this role won't stay in the same tightly normalized shape the previous courses in this series spent so much time on. Reporting and analytical systems, often called OLAP systems, favor denormalized models instead: redundant storage that trades some wasted space for fast reads and minimal joins at query time. Because these systems are usually updated by a scheduled ETL process rather than many users writing to them concurrently, the redundancy and reduced concurrency control that would be a real problem in a transactional database aren't a meaningful cost here. Indexes on these systems are typically built with a high fill factor, favoring compact, fast-to-read structures over leaving room for frequent inserts, and index maintenance is commonly handled as part of the ETL process itself, disabling indexes before a large load and rebuilding them afterward, rather than as separate, ongoing upkeep the way it would be on a transactional table taking inserts around the clock.

The first part of any ETL process is extraction, and the data feeding it can come from almost anywhere. Most data warehousing projects consolidate data from several different source systems, and each one may use its own data organization or format: other relational databases and flat files such as CSV or JSON are the most common sources you'll actually encounter, and occasionally older formats that predate the relational era entirely still turn up, mainframe structures like Information Management System (IMS), Virtual Storage Access Method (VSAM), or Indexed Sequential Access Method (ISAM) still appear in some long-running enterprise environments, even though they're an increasingly rare starting point for a new project today. SQL Server 2025's native JSON data type, covered in this course's very first lesson, makes JSON specifically a format worth knowing about here, since it's now something SQL Server can work with natively on the way in rather than only as a text blob to be parsed separately after the fact. Extraction isn't limited to pulling from a stored source, either; it can also mean fetching from an external source such as a web service, or streaming extracted data straight through to the destination on the fly when no intermediate storage is needed at all.

Whatever the source, the goal of the extraction phase is to convert everything into one consistent format suitable for the transformation step that follows, and parsing that incoming data is where it first gets checked against an expected pattern or structure; data that doesn't fit may be rejected entirely, rejected in part, or flagged for a person to look at later, depending on how the process is built. OLAP systems built on the result also tend to be lighter on enforced business rules than a transactional database, sometimes not even enforcing basic relational integrity constraints, on the theory that a data warehouse sourcing from many different systems, each with its own rules, works better staying largely rule-agnostic itself; any real data cleansing happens during the ETL process instead, so those rules don't need to be enforced a second time once the data lands.

Designing a full data warehouse around this kind of denormalized model is its own substantial topic, generally approached from either a Kimball or an Inmon perspective, usually starting with a relational OLAP, or ROLAP, structure and sometimes built out further into a multidimensional OLAP, or MOLAP, system in SQL Server Analysis Services once the underlying relational structure is in place. That's beyond what this module covers; the point here is simply to understand why so many of the loading and extracting tools introduced in this lesson are built around speed and volume rather than the integrity checks a transactional system relies on, since the data they're usually feeding is designed to be read quickly by many people later, not written to by many people at once.

It's worth being clear about what this module is not asking you to become an expert in. You won't be designing a star schema or deciding between Kimball and Inmon here; those are modeling decisions that belong to a data warehousing or business intelligence course of their own. What this module does ask of you is more mechanical, and more immediately useful day to day: given a source of data and a destination table, which of the tools covered in this lesson actually gets it there, correctly and efficiently, and how do you run that tool, whether that's typing a single BULK INSERT statement or stepping through the Import and Export Wizard's screens one at a time.

This module will show you multiple ways of loading data into a newly created database, as well as extracting data from an existing one, starting with the most direct option available. The next lesson covers loading data with the INSERT statement, the simplest and most direct of the tools introduced in this lesson.