Let SQL Server Write Code for You

Introduction

Many times I happen to find myself in a situation where, as a DBA, I need to write some long and cumbersome code (usually for maintenance purposes).

For example:

Write a script to copy a bunch of tables from one database to another.
Import the tables from one database to another database.
Rebuild a bunch of indexes.
Update a bunch of statistics.
Write scripts for querying a lot of tables.
Write scripts to backup or restore a lot of databases.
And so on and so forth.

A lot of DBAs would spend hours (and even days) sitting in front of their computer and write a stupendous amount of lines of code… Then take care of a lot of copy-paste and typing errors… Test the code and find errors… Fix the code… Run it again… And so on. All the while having to navigate within a huge forest of code.

I wouldn’t think that it takes a special kind of mind to stand up and say “just hold on a minute! Why am I working so hard with this mundane task?? Why can’t SQL Server do all of this for me??” Because in fact… It can!

If you were working with SQL Server for more than a couple months, most probably you’ve heard about ‘Dynamic SQL’, right? I personally covered several topics about it in this blog of mine (namely, SQL Injection among other things).

Also, I hope you heard about the huge list of ‘system tables’ and ‘catalog views’ that SQL Server has to offer, right? And finally, you should have also heard that you can concatenate a list of values using queries, right?

If you haven’t yet, then now is your chance. Because we’re about to bring all of these elements together to make SQL Server do your mundane work for you! [cue maniacal laughter now]

Simple Concept Example: Auto-Generate Insertion Procedures

Here’s a basic scenario to demonstrate the concept for what I’m talking about.

The task goes like this:

You have a list of tables, and you need to generate a simple insertion procedure per each of the tables. Each stored procedure should receive as parameters the values for each of the table columns, except the IDENTITY column.

Alright, so first, let’s take one such table as an example and see how such a procedure should look like. This is the table we’ll use for our example:

Transact-SQL

CREATE TABLE [dbo].[Invoices]( [InvoiceID] int IDENTITY(1,1) PRIMARY KEY, [Title] nvarchar(50), [OrderDate] date, [Qty] int, [Total] money )

CREATE TABLE [dbo].[Invoices](

[InvoiceID] int IDENTITY(1,1) PRIMARY KEY,

[Title] nvarchar(50),

[OrderDate] date,

[Qty] int,

[Total] money

)

An insertion procedure for such a table would look like this:

Transact-SQL

CREATE PROCEDURE [InvoicesInsert] @Title nvarchar(50), @OrderDate date, @Qty int, @Total money AS INSERT INTO [dbo].[Invoices] ([Title], [OrderDate], [Qty], [Total]) VALUES(@Title, @OrderDate, @Qty, @Total)

CREATE PROCEDURE [InvoicesInsert]

@Title nvarchar(50),

@OrderDate date,

@Qty int,

@Total money

INSERT INTO [dbo].[Invoices] ([Title], [OrderDate], [Qty], [Total])

VALUES(@Title, @OrderDate, @Qty, @Total)

All right, now let’s try and break it down and isolate the per-table dynamic parts:

Transact-SQL

CREATE PROCEDURE [{TableName}Insert] {ColumnParametersListWithTypes} AS INSERT INTO [{TableName}] ({ColumnsList}) VALUES({ColumnParametersList})

CREATE PROCEDURE [{TableName}Insert]

{ColumnParametersListWithTypes}

INSERT INTO [{TableName}] ({ColumnsList})

VALUES({ColumnParametersList})

Yeah, this looks much simpler. We’ll call this our ‘template’. Now let’s review each dynamic part and see how we can generate them:

{TableName} This one should be a no-brainer, seeing as we’ll be receiving this as a pre-set value.
{ColumnsList} This one should be fairly simple to get. We can use the sys.columns catalog view to get the list of columns per each table. We’ll just need to concatenate them all with commas between them and we’re set. Also, the is_identity column in the sys.columns catalog view will help us determine which of the columns has an IDENTITY property.
{ColumnParametersList} This one is very similar to {ColumnsList}, except we just need to add the @ symbol before each column.
{ColumnParametersListWithTypes} This one is a little trickier, because it needs to include the relevant data type of each column, and not just the name. No fear, though! The sys.types catalog view, and some CASE WHEN scripting will help us here.

Right, so let’s go right in and see how we can implement this:

Transact-SQL

DECLARE @ColumnsList NVARCHAR(MAX); DECLARE @ColumnParametersList NVARCHAR(MAX); DECLARE @ColumnParametersListWithTypes NVARCHAR(MAX); SELECT @ColumnsList = ISNULL(@ColumnsList + N', ', N'') + QUOTENAME(c.name), @ColumnParametersList = ISNULL(@ColumnParametersList + N', ', N'') + '@' + REPLACE(c.name, ' ', ''), @ColumnParametersListWithTypes = ISNULL(@ColumnParametersListWithTypes + N', ', N'') + '@' + REPLACE(c.name, ' ', '') + N' ' + CASE WHEN t.name IN ('char','varchar','nchar','nvarchar','varbinary','binary') THEN t.name + '('+ CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), CASE WHEN t.name IN ('nchar','nvarchar') THEN c.max_length/2 ELSE c.max_length END ) END + ')' WHEN t.name IN ('decimal','numeric') THEN t.name + '(' + CONVERT(VARCHAR(4),c.precision) + ',' + CONVERT(VARCHAR(4),c.Scale) + ')' ELSE t.name END FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id where c.object_id = object_id(@TableName) AND c.is_identity = 0 ORDER BY c.column_id PRINT @ColumnsList PRINT @ColumnParametersList PRINT @ColumnParametersListWithTypes

DECLARE @ColumnsList NVARCHAR(MAX);

DECLARE @ColumnParametersList NVARCHAR(MAX);

DECLARE @ColumnParametersListWithTypes NVARCHAR(MAX);

SELECT

@ColumnsList = ISNULL(@ColumnsList + N', ', N'') + QUOTENAME(c.name),

@ColumnParametersList = ISNULL(@ColumnParametersList + N', ', N'') + '@' + REPLACE(c.name, ' ', ''),

@ColumnParametersListWithTypes = ISNULL(@ColumnParametersListWithTypes + N', ', N'') + '@' + REPLACE(c.name, ' ', '')

+ N' ' + CASE WHEN t.name IN

('char','varchar','nchar','nvarchar','varbinary','binary')

THEN t.name + '('+

CASE WHEN c.max_length=-1 THEN 'MAX'

ELSE CONVERT(VARCHAR(4),

CASE WHEN t.name IN ('nchar','nvarchar')

THEN c.max_length/2 ELSE c.max_length END )

END + ')'

WHEN t.name IN ('decimal','numeric')

THEN t.name + '('

+ CONVERT(VARCHAR(4),c.precision) + ','

+ CONVERT(VARCHAR(4),c.Scale)

+ ')'

ELSE t.name END

FROM sys.columns AS c

INNER JOIN sys.types AS t

ON c.user_type_id = t.user_type_id

where

c.object_id = object_id(@TableName)

AND c.is_identity = 0

ORDER BY c.column_id

PRINT @ColumnsList

PRINT @ColumnParametersList

PRINT @ColumnParametersListWithTypes

This script uses concatenation and queries from a couple catalog views, to create 3 lists of values.

If we put ‘Invoices’ in our @TableName variable, we’ll get the following output:

Transact-SQL

[Title], [OrderDate], [Qty], [Total] @Title, @OrderDate, @Qty, @Total @Title nvarchar(50), @OrderDate date, @Qty int, @Total money

[Title], [OrderDate], [Qty], [Total]

@Title, @OrderDate, @Qty, @Total

@Title nvarchar(50), @OrderDate date, @Qty int, @Total money

Pretty neat, huh?

Right, so now let’s bring it all together by building the relevant TSQL commands:

Transact-SQL

DECLARE @DropCommand NVARCHAR(MAX) DECLARE @CreateCommand NVARCHAR(MAX) SET @DropCommand = N'IF OBJECT_ID(''' + QUOTENAME(@TableName + 'Insert') + N''') IS NOT NULL DROP PROCEDURE ' + QUOTENAME(@TableName + 'Insert') SET @CreateCommand = N'CREATE PROCEDURE ' + QUOTENAME(@TableName + 'Insert') + N' ' + @ColumnParametersListWithTypes + N' AS INSERT INTO ' + QUOTENAME(@TableName) + N' (' + @ColumnsList + N') VALUES (' + @ColumnParametersList + N')' PRINT @DropCommand PRINT 'GO' PRINT @CreateCommand PRINT 'GO'

DECLARE @DropCommand NVARCHAR(MAX)

DECLARE @CreateCommand NVARCHAR(MAX)

SET @DropCommand =

N'IF OBJECT_ID(''' + QUOTENAME(@TableName + 'Insert') + N''') IS NOT NULL

DROP PROCEDURE ' + QUOTENAME(@TableName + 'Insert')

SET @CreateCommand =

N'CREATE PROCEDURE ' + QUOTENAME(@TableName + 'Insert') + N'

' + @ColumnParametersListWithTypes + N'

INSERT INTO ' + QUOTENAME(@TableName) + N' (' + @ColumnsList + N')

VALUES (' + @ColumnParametersList + N')'

PRINT @DropCommand

PRINT 'GO'

PRINT @CreateCommand

PRINT 'GO'

And the output for our table would be:

Transact-SQL

IF OBJECT_ID('[InvoicesInsert]') IS NOT NULL DROP PROCEDURE [InvoicesInsert] GO CREATE PROCEDURE [InvoicesInsert] @Title nvarchar(50), @OrderDate date, @Qty int, @Total money AS INSERT INTO [Invoices] ([Title], [OrderDate], [Qty], [Total]) VALUES (@Title, @OrderDate, @Qty, @Total) GO

IF OBJECT_ID('[InvoicesInsert]') IS NOT NULL

DROP PROCEDURE [InvoicesInsert]

CREATE PROCEDURE [InvoicesInsert]

@Title nvarchar(50), @OrderDate date, @Qty int, @Total money

INSERT INTO [Invoices] ([Title], [OrderDate], [Qty], [Total])

VALUES (@Title, @OrderDate, @Qty, @Total)

Success! So now we just need to implement this logic in a cursor which traverses several tables, and we’re done. You can download the full sample script from here:

Note that you can use similar methods to generate UPDATE and DELETE procedures as well, just to name a few. The catalog views sys.indexes and sys.index_columns will be helpful for determining the primary key column(s) per each table.

Real-Life Case Study: Copy a List of Tables via Linked Server

So now let’s dive right in, and review a more complex example of a real-life scenario that I’ve recently encountered:

Company X has requested to build a script that would incrementally copy a list of tables from one SQL Server to another using a Linked Server connection. The incremental copy should be done using a numerical incremental primary column which exists in every table. Also, if a table doesn’t exist yet in the destination database, it should be automatically created.

There are several key elements of SQL Server which we’re going to leverage for our benefit here:

Dynamic SQL
System Catalog Views
Concatenation
Values Constructor

Here’s what I did:

First, I initialized the following cursor:

Transact-SQL

DECLARE @Table SYSNAME, @PKColumn SYSNAME, @PKColumnType SYSNAME; DECLARE Cur CURSOR FOR SELECT TableName , PKColumn = QUOTENAME(c.name) , PKColumnType = t.name + CASE WHEN t.name IN ('decimal','numeric') THEN '('+ CONVERT(VARCHAR(4),c.precision)+',' + CONVERT(VARCHAR(4),c.Scale)+')' ELSE '' END FROM (VALUES ('Table1'), ('Table2'), ('Table3'), ('Table4'), ('Table5') ) AS A(TableName) INNER JOIN sys.indexes AS i ON i.object_id = OBJECT_ID(TableName) INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON i.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id WHERE i.is_primary_key = 1 ORDER BY TableName

DECLARE @Table SYSNAME, @PKColumn SYSNAME, @PKColumnType SYSNAME;

DECLARE Cur CURSOR FOR

SELECT

TableName

, PKColumn = QUOTENAME(c.name)

, PKColumnType = t.name

+ CASE WHEN t.name IN ('decimal','numeric')

THEN '('+ CONVERT(VARCHAR(4),c.precision)+','

+ CONVERT(VARCHAR(4),c.Scale)+')'

ELSE '' END

FROM

(VALUES

('Table1'),

('Table2'),

('Table3'),

('Table4'),

('Table5')

) AS A(TableName)

INNER JOIN

sys.indexes AS i

i.object_id = OBJECT_ID(TableName)

INNER JOIN

sys.index_columns AS ic

i.OBJECT_ID = ic.OBJECT_ID

AND i.index_id = ic.index_id

INNER JOIN

sys.columns AS c

i.object_id = c.object_id

AND ic.column_id = c.column_id

INNER JOIN

sys.types AS t

t.system_type_id = c.system_type_id

WHERE

i.is_primary_key = 1

ORDER BY TableName

Note how this cursor makes use of the ‘Values Constructor’ by specifically preparing a list of values to be used as table names, and then joins it with various catalog views in order to find the name of the primary key column per each table. Also note that I don’t necessarily know the data type of each primary column in every table, it can be either Int or Bigint or Decimal or Numeric. If a column is Decimal/Numeric, its scale and precision would also need to be known (that’s the reason for the CASE statement above).

Here’s a sample result of such a cursor from my sandbox database:TableNamePKColumnPKColumnTypeFileStorage[ID]intInvoiceHeader[InvoiceId]intInvoiceRows[RowID]intInvoices[InvoiceID]intSimpleFileStorage[ID]int

Now that I have myself a list of table names, the names of the primary key column per each and even its type, I’m ready to start building the commands themselves.

First, I found some ready-made dynamic table creation script on the internet, cleaned it up a bit, and adjusted it for my own script (it’s the same one I used for generating the column types in the previous example):

Transact-SQL

OPEN Cur FETCH NEXT FROM Cur INTO @Table, @DoChunks, @PKColumn, @PKColumnType WHILE @@FETCH_STATUS = 0 BEGIN -- if table not exists, create it DECLARE @CMD NVARCHAR(MAX), @ColumnInsertionList NVARCHAR(MAX) SET @CMD = NULL; SET @ColumnInsertionList = NULL; SELECT @CMD = ISNULL(@CMD + N', ', N'') + QUOTENAME(c.name) + N' ' + CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN t.name + '('+ CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), CASE WHEN t.name IN ('nchar','nvarchar') THEN c.max_length/2 ELSE c.max_length END ) END +')' WHEN t.name IN ('decimal','numeric') THEN t.name + '('+ CONVERT(VARCHAR(4),c.precision)+',' + CONVERT(VARCHAR(4),c.Scale)+')' ELSE t.name END + CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END + CASE WHEN c.default_object_id <>0 THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END + CASE WHEN c.collation_name IS NULL THEN '' WHEN c.collation_name<> (SELECT collation_name FROM sys.databases WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END , @ColumnInsertionList = ISNULL(@ColumnInsertionList + N', ', N'') + QUOTENAME(c.name) FROM sys.columns AS c INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id WHERE object_id = OBJECT_ID(@Table) ORDER BY column_id ASC SET @CMD = N'USE MyTargetDB; IF OBJECT_ID(''' + @Table + N''') IS NULL CREATE TABLE ' + QUOTENAME(@Table) + N'( ' + @CMD + N' );' EXECUTE (@CMD) AT [MyTargetLinkedServer]

OPEN Cur

FETCH NEXT FROM Cur INTO @Table, @DoChunks, @PKColumn, @PKColumnType

WHILE @@FETCH_STATUS = 0

BEGIN

-- if table not exists, create it

DECLARE @CMD NVARCHAR(MAX), @ColumnInsertionList NVARCHAR(MAX)

SET @CMD = NULL;

SET @ColumnInsertionList = NULL;

SELECT @CMD = ISNULL(@CMD + N',

', N'') + QUOTENAME(c.name)

+ N' '

+ CASE

WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN t.name + '('+

CASE WHEN c.max_length=-1 THEN 'MAX'

ELSE CONVERT(VARCHAR(4),

CASE WHEN t.name IN ('nchar','nvarchar')

THEN c.max_length/2 ELSE c.max_length END )

END +')'

WHEN t.name IN ('decimal','numeric')

THEN t.name + '('+ CONVERT(VARCHAR(4),c.precision)+','

+ CONVERT(VARCHAR(4),c.Scale)+')'

ELSE t.name END

+ CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END

+ CASE WHEN c.default_object_id <>0

THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END

+ CASE WHEN c.collation_name IS NULL THEN ''

WHEN c.collation_name<>

(SELECT collation_name FROM sys.databases

WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS

THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END

, @ColumnInsertionList = ISNULL(@ColumnInsertionList + N', ', N'') + QUOTENAME(c.name)

FROM

sys.columns AS c

INNER JOIN

sys.types AS t

t.system_type_id = c.system_type_id

WHERE object_id = OBJECT_ID(@Table)

ORDER BY column_id ASC

SET @CMD = N'USE MyTargetDB;

IF OBJECT_ID(''' + @Table + N''') IS NULL

CREATE TABLE ' + QUOTENAME(@Table) + N'(

' + @CMD + N'

);'

EXECUTE (@CMD) AT [MyTargetLinkedServer]

Note several important things here:

The variable @CMD is created by concatenating values into it [ note the use of ISNULL(@CMD + N', ', N'') + QUOTENAME(c.name) ]
The variable @ColumnInsertionList is populated similarly to the previous example.
Note the use of system tables here again for getting the column configurations.
Note the use of EXECUTE (@CMD) AT [MyTargetLinkedServer], which is used for executing the dynamic SQL command on a linked server (called “MyTargetLinkedServer” here).

Here’s a sample output of such a script:

Transact-SQL

USE MyTargetDB; IF OBJECT_ID('InvoiceHeader') IS NULL CREATE TABLE [InvoiceHeader]( [InvoiceId] int NOT NULL, [SalesPerson] nvarchar(MAX) NULL, [SalesPerson] sysname NULL, [Job] nvarchar(MAX) NULL, [Job] sysname NULL, [ShippingMethod] nvarchar(MAX) NULL, [ShippingMethod] sysname NULL, [ShippingTerms] nvarchar(MAX) NULL, [ShippingTerms] sysname NULL, [DeliveryDate] datetime NULL, [PaymentTerms] nvarchar(MAX) NULL, [PaymentTerms] sysname NULL, [DueDate] datetime NULL, [InvoiceNumber] nvarchar(50) NULL, [InvoiceNumber] sysname NULL, [InvoiceDate] nvarchar(50) NULL, [InvoiceDate] sysname NULL, [CustomerID] nvarchar(50) NULL, [CustomerID] sysname NULL, [ExpireDate] nvarchar(50) NULL, [ExpireDate] sysname NULL );

USE MyTargetDB;

IF OBJECT_ID('InvoiceHeader') IS NULL

CREATE TABLE [InvoiceHeader](

[InvoiceId] int NOT NULL,

[SalesPerson] nvarchar(MAX) NULL,

[SalesPerson] sysname NULL,

[Job] nvarchar(MAX) NULL,

[Job] sysname NULL,

[ShippingMethod] nvarchar(MAX) NULL,

[ShippingMethod] sysname NULL,

[ShippingTerms] nvarchar(MAX) NULL,

[ShippingTerms] sysname NULL,

[DeliveryDate] datetime NULL,

[PaymentTerms] nvarchar(MAX) NULL,

[PaymentTerms] sysname NULL,

[DueDate] datetime NULL,

[InvoiceNumber] nvarchar(50) NULL,

[InvoiceNumber] sysname NULL,

[InvoiceDate] nvarchar(50) NULL,

[InvoiceDate] sysname NULL,

[CustomerID] nvarchar(50) NULL,

[CustomerID] sysname NULL,

[ExpireDate] nvarchar(50) NULL,

[ExpireDate] sysname NULL

);

See how SQL Server generated the creation script for me? And I can do this for as many tables as I want!

Next up, is building the incremental copy script:

Transact-SQL

-- Copy data into table by Chunks SET @CMD = N' DECLARE @ChunkStart ' + @PKColumnType + N' = 0 DECLARE @ChunkEnd ' + @PKColumnType + N' DECLARE @ChunkFinish ' + @PKColumnType + N' SELECT @ChunkStart = ISNULL(MAX(' + @PKColumn + N'), 0) FROM [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N' SELECT @ChunkFinish = MAX(' + @PKColumn + N'), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN(' + @PKColumn + N')-1 ELSE @ChunkStart END FROM ' + QUOTENAME(@Table) + N' WHILE @ChunkStart < @ChunkFinish BEGIN SET @ChunkEnd = @ChunkStart + @ChunkInterval; INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'(' + @ColumnInsertionList + N') SELECT ' + @ColumnInsertionList + N' FROM ' + QUOTENAME(@Table) + N' WHERE ' + @PKColumn + N' > @ChunkStart AND ' + @PKColumn + N' <= @ChunkEnd SELECT @ChunkStart = MIN(' + @PKColumn + N') FROM ' + QUOTENAME(@Table) + N' WHERE ' + @PKColumn + N' >= @ChunkEnd END ' DECLARE @Params NVARCHAR(MAX) = N'@ChunkInterval bigint' EXEC sp_executesql @CMD, @Params, 10000

-- Copy data into table by Chunks

SET @CMD = N'

DECLARE @ChunkStart ' + @PKColumnType + N' = 0

DECLARE @ChunkEnd ' + @PKColumnType + N'

DECLARE @ChunkFinish ' + @PKColumnType + N'

SELECT @ChunkStart = ISNULL(MAX(' + @PKColumn + N'), 0)

FROM [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'

SELECT @ChunkFinish = MAX(' + @PKColumn + N'), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN(' + @PKColumn + N')-1 ELSE @ChunkStart END

FROM ' + QUOTENAME(@Table) + N'

WHILE @ChunkStart < @ChunkFinish

BEGIN

SET @ChunkEnd = @ChunkStart + @ChunkInterval;

INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'(' + @ColumnInsertionList + N')

SELECT ' + @ColumnInsertionList + N'

FROM ' + QUOTENAME(@Table) + N'

WHERE

' + @PKColumn + N' > @ChunkStart

AND ' + @PKColumn + N' <= @ChunkEnd

SELECT @ChunkStart = MIN(' + @PKColumn + N')

FROM ' + QUOTENAME(@Table) + N'

WHERE ' + @PKColumn + N' >= @ChunkEnd

END

DECLARE @Params NVARCHAR(MAX) = N'@ChunkInterval bigint'

EXEC sp_executesql @CMD, @Params, 10000

This is a rather standard way of incrementally copying data from one table to another using an incremental primary key column.

Note that this dynamic script is built using the @ColumnInsertionList which we have prepared earlier using concatenation and system tables, and the @PKColumn variable which we got from the cursor.

Here’s a sample output of such a script:

Transact-SQL

DECLARE @ChunkStart int = 0 DECLARE @ChunkEnd int DECLARE @ChunkFinish int SELECT @ChunkStart = ISNULL(MAX([InvoiceId]), 0) FROM [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader] SELECT @ChunkFinish = MAX([InvoiceId]), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN([InvoiceId])-1 ELSE @ChunkStart END FROM [InvoiceHeader] WHILE @ChunkStart < @ChunkFinish BEGIN SET @ChunkEnd = @ChunkStart + @ChunkInterval; INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader]([InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate]) SELECT [InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate] FROM [InvoiceHeader] WHERE [InvoiceId] > @ChunkStart AND [InvoiceId] <= @ChunkEnd SELECT @ChunkStart = MIN([InvoiceId]) FROM [InvoiceHeader] WHERE [InvoiceId] >= @ChunkEnd END

DECLARE @ChunkStart int = 0

DECLARE @ChunkEnd int

DECLARE @ChunkFinish int

SELECT @ChunkStart = ISNULL(MAX([InvoiceId]), 0)

FROM [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader]

SELECT @ChunkFinish = MAX([InvoiceId]), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN([InvoiceId])-1 ELSE @ChunkStart END

FROM [InvoiceHeader]

WHILE @ChunkStart < @ChunkFinish

BEGIN

SET @ChunkEnd = @ChunkStart + @ChunkInterval;

INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader]([InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate])

SELECT [InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate]

FROM [InvoiceHeader]

WHERE

[InvoiceId] > @ChunkStart

AND [InvoiceId] <= @ChunkEnd

SELECT @ChunkStart = MIN([InvoiceId])

FROM [InvoiceHeader]

WHERE [InvoiceId] >= @ChunkEnd

END

Once again, SQL Server generated the full script for me and it will be automatically done per each table, and all I had to write is one template!

Next, we simply close the loop and clean up the cursor object:

Transact-SQL

FETCH NEXT FROM Cur INTO @Table, @PKColumn, @PKColumnType END CLOSE Cur DEALLOCATE Cur

FETCH NEXT FROM Cur INTO @Table, @PKColumn, @PKColumnType

END

CLOSE Cur

DEALLOCATE Cur

Nothing to explain here if you’re already familiar with cursors.

That’s it! You can download the full sample script here:

Conclusion

In this case study I wrote about 100 lines of code which generated for me about a thousand lines of code that I didn’t need to write and manage on my own. The benefit here is HUGE.

This is just a couple examples of something you can do to make SQL Server write tons of lines of code for you.

The possibilities are truly endless. You can prepare such ‘templates’ for yourself that generate and execute code, and use it and re-use it on several environments, almost without any changes to your scripts (because it’ll be automatically generated using the database metadata).

Microsoft SQL Server exposes for you a lot of different system tables (catalog views) which you can leverage for your own benefit. They are all documented at SQL Server Books Online.

Resources

Here are a bunch of resources for reading up on available system tables and catalog views:

System Tables: http://msdn.microsoft.com/en-us/library/ms179932.aspx
System Views: http://msdn.microsoft.com/en-us/library/ms177862.aspx
Catalog Views: http://msdn.microsoft.com/en-us/library/ms174365.aspx
Object Catalog Views: http://msdn.microsoft.com/en-us/library/ms189783.aspx
Dynamic Management Views (DMVs): http://msdn.microsoft.com/en-us/library/ms188754.aspx

And here are some resources on the various T-SQL methodologies that can help us in building dynamic SQL scripts:

Table Values Constructor: http://msdn.microsoft.com/en-us/library/dd776382.aspx
Dynamic SQL using sp_executesql: http://msdn.microsoft.com/en-us/library/ms188001.aspx
Dynamic SQL guide by Erland Sommarskog: http://www.sommarskog.se/dynamic_sql.html
Dynamic SQL execution on remote servers: http://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/
How to create concatenated value lists: http://blog.sqlauthority.com/2007/05/06/sql-server-creating-comma-separate-values-list-from-table-udf-sp/

And here are some of my other blog posts also talking about similar dynamic SQL methodologies:

Got comments? Questions? Write them in the comments section below!

#tsql #dynamicsql #spexecutesql #tips

Let SQL Server Write Code for You

Introduction

Simple Concept Example: Auto-Generate Insertion Procedures

Real-Life Case Study: Copy a List of Tables via Linked Server

Conclusion

Resources

Recent Posts

JOIN OUR MAILING LIST

CONTACT US

keep in Touch!