… for testing purposes, whatever.
I’m using SQL Server 2016:
Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.80 (X64)
Dec 12 2015 13:18:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 10240: )
Whenever I do an example, I’ll be looking to illustrate it using the AdventureWorks database, as that is pretty rich, and it is useful to know we are dealing with roughly the same data, constraints, etc.
The 1-liner for creating a copy of a table:
select * into target_table from source_table;
select * into t_person from [AdventureWorks2014].[Person].[Person];
Don’t expect any of the primary keys, constraints etc. to be pulled across – they won’t. And that is quite reasonable – surely e.g. a foreign key would have to be thought out before just copying it.
Use case: given the products in the Product table (all examples use the AdventureWorks database), for those products whose reorder point is <= 5, for each product in that set create a root folder, and under each products root folder, create an Invoices folder, and an Orders folder. CLR could be used to actually create the folders, but as CLR is banned in many companies for security reasons, we can just use this output to create a static file of e.g. PowerShell commands. This example just gives the folders, rather than the full command line:
;with fileSystem as (
select 'c:\ProductFiles\' as rootFolder
, product as (
select p.ProductID, f.rootFolder + p.Name as productFileLocation
from [adventureworks2014].[production].[product] p
cross apply fileSystem f
where p.ReorderPoint <= 5
select ProductID, productFileLocation from product
select ProductID, productFileLocation + '\Orders' from product
select ProductID, productFileLocation + '\Invoices' from product
order by ProductID
Sample of the result: