iopexcellent.blogg.se

1. #COPY TABLES FROM ADVENTUREWORKS2012 TO ADVENTUREWORKSDW2012 HOW TO#
2. #COPY TABLES FROM ADVENTUREWORKS2012 TO ADVENTUREWORKSDW2012 CODE#

Below is a partial screenshot of the completed product.Īs always, thanks for looking! SQL Tips! Set your default database in SSMS (Installment 1) The script will now be created and generated in a new query window. Now scroll down to “Types of data to script” and change it from schema only to Schema and Data.On the same screen select the “Advanced” button found on the right side.This option is found at the bottom of the screen. Please select “Save to new query window”. On the “Scripting Options screen” I’m just going to load the script into a new query window.For Choose Objects screen –> Click “Select specific database objects”.Inside the Generate Scripts wizard click next on the introduction screen.Right click on the database where your table is stored, I’m using AdventureWorksDW2012.Open SSMS and connect to the server where your table is located at.Along with the DDL script I will also show you how you can generate the script to populate the table.

#COPY TABLES FROM ADVENTUREWORKS2012 TO ADVENTUREWORKSDW2012 HOW TO#

In this scenario I am going to show you how to generate the DDL to create the DimCurrency table from the AdventureWorksDW2012 database. It would be much easier if I could share the table definition and the SQL statement to populate that table, Agree? Great!

For example imagine I have created and populated a few tables for a blog post. Sometimes I find myself needing to not only generate the DDL for a table but also the data that is stored in that table.

#COPY TABLES FROM ADVENTUREWORKS2012 TO ADVENTUREWORKSDW2012 CODE#

The size of the tables can be scaled by running the INSERT code block multiple times to increase the size of the Sales.SalesOrderHeader table by roughly 225MB, and to increase the size of the Sales.SalesOrderDetailEnlarged table by roughly 400MB per execution after the index rebuilds are run.Ĭreate Enlarged AdventureWorks Tables.sql (8.Welcome back to this series on SQL Tips. These larger tables can be used to produce parallel execution plans, plans that have large execution memory grant requirements, plans that perform sort and hash spill operations through tempdb, and many other uses in SQL Server. The attached script has been tested against the AdventureWorks2008R2 and AdventureWorks2012 databases specifically, but as long as the table schema is the same it may be applied to any version of AdventureWorks. Below is a script that can enlarge these tables into tables named Sales.SalesOrderHeaderEnlarged and Sales.SalesOrderDetailEnlarged in the specific AdventureWorks database being targeted. The tables that I chose to enlarge for demonstration purposes were the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables which can be used to demonstrate a number of different query plan and performance issues in SQL Server. The concept of creating a larger version of AdventureWorks is not new, Adam Machanic ( Blog| Twitter) blogged last year about enlarging specific tables for demonstrations, and at the same time I found that I also needed to create tables that were larger datasets for my own purposes. The AdventureWorks set of sample databases are commonly used for presentation demos and showing how to use SQL Server, but one of the biggest challenges in using these databases is their small size which is geared towards ease of distribution, more than demonstrating all of the functionality of SQL Server.