Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Monday, 15 February 2016
( SQL )

 

Returning an int

CREATE PROCEDURE [dbo].[CreateAuthor]
    @FirstName nvarchar(255),
    @LastName nvarchar(255),
    @AuthorID int output
AS
BEGIN   
    INSERT INTO Author(FirstName, LastName)
    VALUES (@FirstName, @LastName)

    SET @AuthorID = SCOPE_IDENTITY()
END

Returning entire record

CREATE PROCEDURE [dbo].[CreateAuthor]
    @FirstName nvarchar(255),
    @LastName nvarchar(255)
AS
BEGIN
    -- table variable
    DECLARE @MyTableVar TABLE (AuthorID int, FirstName nvarchar(255), LastName nvarchar(255));

    INSERT INTO Author(FirstName, LastName)
    OUTPUT INSERTED.AuthorID, INSERTED.FirstName, INSERTED.LastName INTO @MyTableVar
    VALUES (@FirstName, @LastName)

    SELECT AuthorID, FirstName, LastName FROM @MyTableVar
END

| | # 
# Friday, 23 January 2015
( SQL | VB.NET )
Public Shared Sub InsertUser(ByVal userName As String) Using cnn As New SqlConnection(ConnectionStrings.GetConnection()) Using cmd As SqlCommand = New SqlCommand("StoredProc_InsertUser", cnn) cnn.Open() cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@Username", userName) cmd.ExecuteNonQuery() End Using End Using End Sub

‘Best’ practice for DBAccess code..
| | # 
# Wednesday, 10 December 2014
( SQL | SQLServer )

https://efreversepoco.codeplex.com/

Reverse engineers an existing database and generates EntityFramework Code First POCO classes, DbContext and Configuration mappings. Works for SQL Server and SQL Server Compact 4.0

| | # 
# Monday, 06 October 2014
( SQL | SQLServer )

http://ssmstoolspack.com/

Needs a licence

image
Nice query history window.  Also connection colouring so know which server!

| | # 
# Wednesday, 26 February 2014
( Miniprofiler | SQL )

 

// 0) SqlConnection - Ensure connections are always closed, open the connection inside a using
using (var conn = new SqlConnection(@"server=.\SQLEXPRESS;database=TestDB;Trusted_Connection=True;"))
// Good practice to make sure dispose is called on SqlCommand.. also perf improvement
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT Name FROM Countries";
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var text = reader["Name"];
            //result += text + ", ";
        }
    }
}

Can’t use this for profiling as need to be able to hook in:
// 1) ProfiledDbCommand - SQL and SP with parameter
var sqlConnection2 = new SqlConnection(@"server=.\SQLEXPRESS;database=TestDB;Trusted_Connection=True;");
using (DbConnection connection2 = new ProfiledDbConnection(sqlConnection2, MiniProfiler.Current))
using (DbCommand command = new SqlCommand())
{
    // Create the command.
    ProfiledDbCommand prcommand = new ProfiledDbCommand(command, connection2, null);
    prcommand.CommandType = CommandType.StoredProcedure;
    prcommand.CommandText = "dbo.GetCountries";
    prcommand.Parameters.Add(new SqlParameter("@countryID", 3));

    //prcommand.CommandType = CommandType.Text;
    //prcommand.CommandText = "SELECT Name FROM Countries";

    prcommand.Connection = connection2;
    // Open the connection.
    connection2.Open();

    // Retrieve the data.
    DbDataReader reader = prcommand.ExecuteReader();
    while (reader.Read())
    {
        var text = reader["Name"];
        result += text + ", ";
    }
}

A simple query using DbConnection which is then profiled in MP

image

Can also profile DataTables (fixed see http://stackoverflow.com/questions/22171922/miniprofiler-profileddbdataadapter )

var sqlConnection = new SqlConnection(@"server=.\SQLEXPRESS;database=TestDB;Trusted_Connection=True;");
var table = new DataTable();
//var dataAdapter = new SqlDataAdapter("GetCountries", sqlConnection);
var dataAdapter = new SqlDataAdapter("select * from countries", sqlConnection);
//dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
//dataAdapter.SelectCommand.Parameters.AddWithValue("@countryID", 2);
dataAdapter.Fill(table);

No profiling on datatables nor datasets either…

also not disposing – bad

An Argument for not using Usings

http://stackoverflow.com/questions/248961/c-sharp-using-statement-catch-error

try
{
    conn = new SqlConnection(Settings.Default.qlsdat_extensionsConnectionString)
    cmd = new SqlCommand(reportDataSource, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Year", SqlDbType.Char, 4).Value = year;
    cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = start;
    cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = end;

        conn.Open(); //opens connection

    DataSet dset = new DataSet();
    new SqlDataAdapter(cmd).Fill(dset);
    this.gridDataSource.DataSource = dset.Tables[0];
}
catch(Exception ex)
{
    Logger.Log(ex);
    throw;
}
finally
{
    if(conn != null)
        conn.Dispose();

        if(cmd != null)
        cmd.Dispose();
}

 

image
0.0s to do the SQL query

image
Profiling from within another class and running the same query, flagging as duplicate.

| | # 
# Thursday, 20 February 2014
( c# | SQL )

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

Useful to see where the app pool connections are coming from:

--exec sp_who
exec sp_who2

http://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server

The issue I had was code not wrapped in a using..so it wasn’t calling a connection.close().

| | # 
# Monday, 17 February 2014
( BCP | SQL )

http://www.sswug.org/articles/viewarticle.aspx?id=19531

-T Trust Windows authentication to connect to the database.
-E  Keep identities
-c character
-n native
-b batch size
-e error log

Preferred using native format

Stated using this as getting errors on import with character separtors

select 'bcp ' + db_name() + '..' + name + ' out c:\temp\data\bcp\' + name + '.dat -n -SMyServerName\SQL2012 -T -e ' + name + ‘errors.txt’

select 'bcp ' + db_name() + '..' + name + ' in e:\temp\bcp\' + name + '.dat -n -S.\SQL2012 -T -E -e ' + name + 'Error.txt'

**if you get weird errors, check in ViM that the – characters are correct or just replace if copy and paste from here

out.bat > output.txt

**check the first one goes okay.  I put in an echo ‘hello’ on the 2nd line

Export using double pipe character as separator

select 'bcp ' + db_name() + '..' + name + ' out c:\temp\data\bcp\' + name + '.txt /c -t\^|^| -SMyServerName\SQL2012 -T /b500'
from sysobjects
where type = 'U'
AND name != 'tbl_TableDontWant'
AND name != 'dtproperties'
order by name

Query –> Results to File –> bcpexportall.bat

Notice the escaped double pipe for a separator.  Finding a good separator takes time.

Import

select 'bcp ' + db_name() + '..' + name + ' in e:\temp\bcp\' + name + '.txt /c -t\^|^| -S.\SQL2012 –T -E /b500 -e ' + name + 'Error.txt'
from sysobjects
where type = 'U'
AND name != 'tbl_TableDontWant'
AND name != 'dtproperties'
order by name

image
The import script

| | # 
# Thursday, 13 February 2014
( SQL )
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 
http://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database
| | # 
# Wednesday, 12 February 2014
( SQL )

Runs over entire db and generates insert scripts for each table.  Note that would need to figure out correct order.

Adapted from https://docs.google.com/file/d/0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx/edit?hl=en_GB#

Results to file perhaps http://stackoverflow.com/questions/1504462/output-sql-messages-to-a-log-file

 

SET NOCOUNT ON
DECLARE @lcl_name VARCHAR(100)
DECLARE cur_name CURSOR FOR
SELECT name
FROM sysobjects s
WHERE type = 'U'
AND name != 'tbl_CMS_Content'
ORDER BY s.name

OPEN cur_name
FETCH NEXT FROM cur_name INTO @lcl_name
    WHILE @@Fetch_status = 0
        BEGIN
        exec #spu_GenerateInsert @table =@lcl_name, @generateGo=0, @restriction='', @producesingleinsert=0, @debug=0, @GenerateOneLinePerColumn=0, @GenerateIdentityColumn=0

        FETCH NEXT FROM cur_name INTO @lcl_name
        END
CLOSE cur_name
DEALLOCATE cur_name
SET NOCOUNT OFF
GO


CREATE PROCEDURE #spu_GenerateInsert
     @table varchar(128), -- used to specify the table to generate data for
     @generateGo bit = 0, -- used to allow GO statements to separate the insert statements
     @restriction varchar(1000) = '', -- used to allow the data set to be restricted, no need for the where clause but can use syntax as 'columna = 1'
     @producesingleinsert bit = 0, -- used to switch the ability to produce multiple insert statements (default) or one statement using UNION SELECT
     @debug bit = 0, -- used to allow debugging to be turned on to the stored procedure - in case of queries
     @GenerateOneLinePerColumn bit = 0, -- used to display the columns and data on separate lines
     @GenerateIdentityColumn bit = 1 -- used to prevent the identity columns from being scripted
AS
/*******************************************************************************
Original Author:    Keith E Kratochvil

This version:        Jane Dallaway

Updates at:            http://jane.dallaway.com/downloads/SQL/spu_generateInsert.sql

Documentation at:    http://jane.dallaway.com/blog/labels/spu_generateinsert.html

Date Created:        March 16, 2000

Description:        This procedure takes the data from a table and turns it into
                    an insert statement.

CAUTION!!! If you run this on a large table be prepared to wait a while!
 
Modified:   add a comment here (date, who, comment)
 
Date        Name  Description
~~~~~~~     ~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
07/11/03    Jane  Added a @generateGO parameter to allow selection
07/11/03    Jane  This procedure has an issue with NULLs....
07/11/03    Jane  Deal with Identities
07/11/03    Jane  Wrap all tablenames and columns with [ and ]
15/12/03    Jane  For DateTimes covert as style 1 - to allow for mm/dd/yyy
16/12/03    Jane  Get rid of rowcount
18/12/03    Jane  Prevent single field tables having that field displayed twice
06/07/04    Jane  Large money values get separeted with a comma - so specify convert
                  with 0 rather than 1        
31/05/05    Jane  Added default for GenerateGo and added ability to generate
                  Inserts for selected records based on the restriction
03/01/08    Jane  Updated to cope with text and ntext.  It converts to VARCHAR(8000) to allow quote escaping
                  Also copes with Nulls much better now.
07/01/08    Jane  Now handles guids
07/01/08    Jane  Forced collation to use database_default as was causing an error in some circumstances
15/01/08    Jane  Dave reported issue with image column types as a comment on my blog
                  http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c9038036788352783369
                  So, ensured that all column types either work, or replace with NULL and add warning
23/01/08    Jane  Ignore calculated columns.  Can't migrate the data, so remove from the INSERT
                  Implemented a suggestion from Jon Green - 4R Systems Inc left on my blog post at
                  http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c2055936172890486440
                  to allow a choice of separate insert statements or a single statement using the UNION SELECT syntax.
                  New parameter @producesingleinsert used.  When this is set to 0 it produces separate INSERT statements.
                  When set to 1 it produces a single statment using UNION SELECT
20/08/08 Jane     New parameter @GenerateOneLinePerColumn added as suggested by Christian via comment on my blog post at
                  http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html?showComment=1219174920000#c6992035307857457643
                  Also made all string variables varchar(max).  This means it no longer works on SQL 2000 - but to make it do so is just a case of
                  putting all varchar(max) to varchar(8000)
07/01/09 Jane     New parameter @GenerateIdentityColumns added as suggestion by James Bradshaw to enable identity columns to not be scripted
                  and therefore rely on the database to populate the identities from scratch
16/02/09 Jane     Continuation of work started on 20/08/08 - all remaining VARCHAR(8000)s removed and replaced with VARCHAR(max).  So, no longer
                  an issue with text columns unless it is SQL Server 2000, in which case this stored procedure will need to have been updated. 
16/04/09 Jane     Add checking to see if the table specified actually exists - user error on my case when using the procedure
17/04/09 Jane     Changed @tabledata to be nvarchar rather than varchar to allow for unicode                 
                  Added checking for data exceeding 8000 bytes and split the data based on CHAR(13) if this situation is seen. 
                  This won't resolve it in all cases, and if it doesn't then a warning is displayed at the bottom of the generated code
08/06/09 Jane     All strings are now output as N'<data>' rather than '<data>' to cope with extended character sets


Known Issues:    
     1) BLOBs can't be output

Usage:
     exec spu_GenerateInsert @table ='users', @generateGo=0, @restriction='columna = x', @producesingleinsert=0, @debug=0, @GenerateOneLinePerColumn=0, @GenerateIdentityColumn=0
               

Version:
     This version has been tested on SQL Server 2005.  To make this work on SQL Server 2000, replace all instances of VARCHAR(max) with VARCHAR(8000).  This will limit
     the ability of export of text and XML columns to 8000 characters
 
*******************************************************************************/
 
--Variable declarations
DECLARE @InsertStmt varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Fields varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @SelList varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Data varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @FldCounter int
DECLARE @TableData nvarchar(max) -- change this to be (8000) for SQL Server 2000
 
-- added by Jane - 07/11/03
DECLARE @bitIdentity BIT
 
-- added by Jane 03/01/08
DECLARE @bitHasEncounteredText BIT
SET @bitHasEncounteredText = 0
 
-- added by Jane 15/01/08
DECLARE @bitHasEncounteredImage BIT
SET @bitHasEncounteredImage = 0
DECLARE @bitHasEncounteredBinary BIT
SET @bitHasEncounteredBinary = 0
DECLARE @bitHasEncounteredXML BIT
SET @bitHasEncounteredXML = 0
 
-- added by Jane - 23/01/08
DECLARE @bitInsertStatementPrinted BIT
SET @bitInsertStatementPrinted = 0

-- added by Jane - 17/04/09 - To try and cope with bits of data which are greater than 8000 bytes
DECLARE @bitDataExceedMaxPrintLength BIT
SET @bitDataExceedMaxPrintLength = 0
DECLARE @cintMaximumSupportedPrintByteCount INT
SET @cintMaximumSupportedPrintByteCount = 8000 -- based on SQL Server 2005
DECLARE @statementToOutput NVARCHAR(max) -- change this to be (8000) for SQL Server 2000
DECLARE @NextCR INT -- used to split the output up into smaller chunks - look for carriage returns/line feeds and break into separate print statements
DECLARE @statementsToOutput TABLE (Id INT IDENTITY(1,1), singleStatement NVARCHAR (max) ) -- change this to be (8000) for SQL Server 2000
DECLARE @id INT
DECLARE @singleStatement NVARCHAR(max) -- change this to be (8000) for SQL Server 2000
DECLARE @loop INT

-- added by Jane - 16/12/03
SET NOCOUNT OFF

-- added by Jane - 16/04/09 - check for table existance
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table)
BEGIN

     -- added by Jane - 16/04/2009 - Show details of table being generated, and date/time
     --PRINT REPLICATE('-', 37 + LEN(@table) + LEN(CONVERT(VARCHAR,GETDATE(),120)))         
     --PRINT '-- Script generated for table ' + @table + ' on ' + CONVERT(VARCHAR,GETDATE(),120) + ' --'
     --PRINT REPLICATE('-', 37 + LEN(@table) + LEN(CONVERT(VARCHAR,GETDATE(),120)))
 
     -- added by Jane - 07/11/03
     SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_Name =@table
     
     -- added by Jane - 03/01/08
     --PRINT '-- ** Start of Inserts ** --'
     --PRINT ''
     
     -- added by Jane - 07/11/03
     -- AND @GenerateIdentityColumn = 1 added by Jane - 07/01/09
     IF @bitIdentity = 1 AND @GenerateIdentityColumn = 1
     BEGIN
           PRINT 'SET IDENTITY_INSERT [' + @table + '] ON '
     END
     
     --initialize some of the variables
     -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
     SELECT @InsertStmt = 'INSERT INTO [' + @Table + '] '+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END,
           @Fields = '',
           @Data = '',
           @SelList = 'SELECT ',
           @FldCounter = 0
     
     --create a cursor that loops through the fields in the table
     --and retrieves the column names and determines the delimiter type that the
     --field needs
     DECLARE CR_Table CURSOR FAST_FORWARD FOR
          
           SELECT COLUMN_NAME,
                  'IsChar' = CASE
                  WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0
                  WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1
                  WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
                  WHEN DATA_TYPE in ('text', 'ntext') THEN 3
                  WHEN DATA_TYPE in ('image') THEN 4 -- added by Jane - 15/01/08
                  WHEN DATA_TYPE in ('binary', 'varbinary') THEN 5 -- added by Jane - 15/01/08
                  WHEN DATA_TYPE in ('sql_variant') THEN 6 -- added by Jane - 15/01/08 - Force to be converted as varchars
                  WHEN DATA_TYPE in ('xml') THEN 7 -- added by Jane - 15/01/08
                  ELSE 9
           END
           FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
           INNER JOIN syscolumns sc WITH (NOLOCK)
           ON c.COLUMN_NAME = sc.name
           INNER JOIN sysobjects so WITH (NOLOCK)
           ON sc.id = so.id
           AND so.name = c.TABLE_NAME
           WHERE table_name = @table
           AND DATA_TYPE <>'timestamp'
           AND sc.IsComputed = 0     
           AND 1 =
               CASE @GenerateIdentityColumn
               WHEN 1 -- When we want the identity columns to be generated, then always include the column
               THEN 1
               ELSE
                    CASE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') -- Check if the column has the property IsIdentity
                    WHEN 1 -- If it does
                    THEN 0 -- don't include this column
                    ELSE 1 -- otherwise include this column
                    END
               END
           ORDER BY ORDINAL_POSITION
           FOR READ ONLY
           OPEN CR_Table
     
           FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
     
     WHILE (@@fetch_status <> -1)
     BEGIN
     
           IF @IsChar = 3
                  SET @bitHasEncounteredText = 1          
     
           -- added by Jane - 15/01/08
           IF @IsChar = 4
                  SET @bitHasEncounteredImage = 1
           IF @IsChar = 5
                  SET @bitHasEncounteredBinary = 1
     
           IF (@@fetch_status <> -2)
           BEGIN
     
                  -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
                  -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
                  -- Special case for first field
                  IF @FldCounter = 0
                  BEGIN
                          SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                          -- Updated by Jane - 08/06/09 - prefix string with N to cope with extended character sets                         
                          SELECT @SelList =  CASE @IsChar
                                 WHEN 1 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''') + '''''''' ,''NULL'')  ' + ' COLLATE database_default + '
                                 WHEN 2 THEN @SelList +  ' ISNULL(''N'''''' + CONVERT(varchar(20),[' + @ColName + ']) + '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 3 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 4 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                                 WHEN 5 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                                 WHEN 6 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 7 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                                 END
                          SELECT @FldCounter = @FldCounter + 1
                          SET @SelList = @Sellist
                          FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
                  END
     
                  -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
                  -- Updated by Jane - 03/01/08 to cope with NULL replacements          
                  -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
                  -- Updated by Jane - 18/12/03 to prevent single field tables having that field displayed twice
                  -- Updated by Jane - 20/08/08 to incorporate the @GenerateOneLinePerColumn parameter suggested by Christian
                  IF @@fetch_status <> -1
                  BEGIN
                          SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                          -- Updated by Jane - 08/06/09 - prefix string with N to cope with extended character sets
                          SELECT @SelList =  CASE @IsChar
                                 WHEN 1 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL(''N'''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''' ) +  '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 2 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + '  +  'ISNULL(''N'''''' + CONVERT(varchar(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 3 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''' )+  '''''''',''NULL'')  ' + ' COLLATE database_default + '
                                 WHEN 4 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                                 WHEN 5 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                                 WHEN 6 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 7 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 ELSE @SelList  + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' + ' ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                          END 
                  END
           END 
     
           FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
     END
     
     CLOSE CR_Table
     DEALLOCATE CR_Table
     
     SELECT @Fields =  SUBSTRING(@Fields, 1,(len(@Fields)-1))
     
     SELECT @SelList =  SUBSTRING(@SelList, 1,(len(@SelList)-1))
     SELECT @SelList = @SelList + ' FROM ' + @table
     
     IF LEN(@restriction) > 0
     BEGIN
           SELECT @SelList = @SelList + ' WHERE ' + @restriction
     END
     
     -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
     SELECT @InsertStmt = @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN REPLACE(@Fields,', ',',' + CHAR(13)) ELSE @Fields END + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')'
     
     --for debugging purposes...
     IF @Debug = 1
     BEGIN
           PRINT '*** DEBUG INFORMATION - THIS IS THE SELECT STATEMENT BEING RUN *** '
           PRINT @sellist
           PRINT '*** END DEBUG ***'
     END
     
     -- added by Jane - 16/12/03
     SET NOCOUNT ON
     
     --now we need to create and load the temp table that will hold the data
     --that we are going to generate into an insert statement
     
     CREATE TABLE #TheData (TableData VARCHAR(max)) -- change this to be (8000) for SQL Server 2000
     INSERT INTO #TheData (TableData) EXEC (@SelList)
     
     --Cursor through the data to generate the INSERT statement / VALUES/SELECT/UNION SELECT clause
     DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
     READ ONLY
     OPEN CR_Data
     FETCH NEXT FROM CR_Data INTO @TableData
     
           WHILE (@@fetch_status <> -1)
           BEGIN
                 IF (@@fetch_status <> -2)
                 BEGIN                                           

                       -- Updated by Jane 17/04/09 instead of printing at this point, store the output in a @statementToOutput variable.  This allows us to try and split it to
                       -- to print within the 8000 byte limit imposed by the PRINT function           
                       -- Updated by Jane 23/01/08 after suggestion posted to blog at http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c2055936172890486440
                       IF (@producesingleinsert = 1 )
                              IF (@bitInsertStatementPrinted = 0)
                              BEGIN
                                    SET @statementToOutput = @InsertStmt + char(13) + 'SELECT ' + @TableData                                   
                                    SET @bitInsertStatementPrinted = 1
                              END
                              ELSE
                              BEGIN
                                    SET @statementToOutput = 'UNION SELECT ' + @TableData
                              END
                       ELSE
                       BEGIN
                              -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
                              SET @statementToOutput =  @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + 'VALUES ' + + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + @TableData + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')' + CHAR(13)
                       END
     
                       -- Added by Jane - 17/04/09 - check for length of @statementToOutput
                       -- if it exceeds the maximum length, then lets attempt to split it on CRs as these will be done via separate PRINT statements
                       IF DATALENGTH(@statementToOutput) > @cintMaximumSupportedPrintByteCount                                                                                                                    
                       BEGIN                                                     
                       
                           -- Break the @statementToOutput based on CHAR(13) and put separate data values into @statementsToOutput table

                           -- Get rid of double line breaks
                           -- Replace CHAR(10) with CHAR(13)
                           SET @statementToOutput = REPLACE(@statementToOutput,CHAR(10),CHAR(13))
                           -- Replace CHAR(13)+CHAR(13) with CHAR(13)
                           SET @statementToOutput = REPLACE(@statementToOutput,CHAR(13)+CHAR(13),CHAR(13))

                           SET @NextCR = Charindex(CHAR(13),@statementToOutput)
                           WHILE @NextCR  > 0
                           BEGIN
                              
                               INSERT INTO @statementsToOutput VALUES(LEFT(@statementToOutput,@NextCR - 1))

                               SET @statementToOutput = Right(@statementToOutput,Len(@statementToOutput) - @NextCR)
                               SET @NextCR = Charindex(CHAR(13),@statementToOutput)
                           END                                                     
                           INSERT INTO @statementsToOutput VALUES(@statementToOutput)                                                                                  

                           -- Output the statements line by line
                           SET @loop = 1
                           SET @id = -1

                           WHILE @loop = 1
                           BEGIN
                               SELECT TOP 1 @id = Id, @singleStatement = singlestatement
                               FROM @statementsToOutput
                               WHERE id > @id
                               ORDER BY Id

                               SET @loop = @@ROWCOUNT

                               IF @loop = 0
                                    BREAK

                               -- No guarantee that we still don't exceed the limits, but should have more of a chance of avoiding them
                               IF DATALENGTH(@singleStatement) > @cintMaximumSupportedPrintByteCount
                               BEGIN
                                    SET @bitDataExceedMaxPrintLength = 1
                                    SELECT @singleStatement                          
                               END

                               PRINT @singleStatement
                           END                                                      
                       END   
                       ELSE
                       BEGIN                   
                           -- Don't need to worry, we haven't exceeded the 8000 byte limit so just print it
                           PRINT @statementToOutput
                       END

                       IF @generateGo = 1
                       BEGIN
                              PRINT 'GO'
                       END
                 END
                 FETCH NEXT FROM CR_Data INTO @TableData
           END
     CLOSE CR_Data
     DEALLOCATE CR_Data
     
     -- added by Jane - 07/11/03
     -- AND @GenerateIdentityColumn = 1 added by Jane - 07/01/09
     IF @bitIdentity = 1 AND @GenerateIdentityColumn = 1
     BEGIN
           PRINT 'SET IDENTITY_INSERT [' + @table + '] OFF '
     END
     
     -- added by Jane - 03/01/08
     --PRINT '-- ** End of Inserts ** --'
     
     IF @bitHasEncounteredImage = 1
     BEGIN
           PRINT '-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Images are not supported by this script at this time.  ** --'
     END
     
     -- added by Jane - 15/01/08
     IF @bitHasEncounteredBinary = 1
     BEGIN
           PRINT '-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Binary and VarBinary are not supported by this script at this time.  ** --'
     END
     
     -- 16/02/09 - These checks are only required if the database is SQL Server 2000
     DECLARE @Version VARCHAR(100)
     SELECT @Version = @@VERSION
     IF PATINDEX('%8.00%',@Version) > 0
     BEGIN
          IF @bitHasEncounteredXML = 1
          BEGIN
                PRINT '-- ** WARNING: This will convert any ''xml'' data to be ''varchar(8000)'' ** --'
          END
          
          -- added by Jane - 03/01/08
          IF @bitHasEncounteredText = 1
          BEGIN
                PRINT '-- ** WARNING: This will convert any ''text'' or ''ntext'' data to be ''varchar(8000)'' ** --'
          END
     END

     IF @bitDataExceedMaxPrintLength = 1
     BEGIN
          PRINT '-- ** WARNING: The data length for at least one row exceeds '+ CONVERT(VARCHAR(6),@cintMaximumSupportedPrintByteCount) + ' bytes.  The PRINT command is limited to ' + CONVERT(VARCHAR(6),@cintMaximumSupportedPrintByteCount) + ' bytes (for more information see http://msdn.microsoft.com/en-us/library/ms176047.aspx).  Do not trust this data. ** --'
     END
END
ELSE
BEGIN

     -- added by Jane - 16/04/2009 - Warn user that table doesn't exist
     PRINT REPLICATE('-', 43 + LEN(@table))    
     PRINT '-- Table ' + @table + ' doesn''t exist on this database --'
     PRINT REPLICATE('-', 43 + LEN(@table))

END
RETURN (0)
 
GO

DROP Proc #spu_GenerateInsert
GO

| | # 
# Tuesday, 11 February 2014
( SQL )

From https://docs.google.com/file/d/0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx/edit?hl=en_GB#

--declare @table varchar(128) = 'tbl_CMS_Content' -- used to specify the table to generate data for
declare @table varchar(128) = 'tbl_RequestData_System' -- used to specify the table to generate data for

declare @generateGo bit = 0 -- used to allow GO statements to separate the insert statements
declare @restriction varchar(1000) = '' -- used to allow the data set to be restricted, no need for the where clause but can use syntax as 'columna = 1'
declare @producesingleinsert bit = 0 -- used to switch the ability to produce multiple insert statements (default) or one statement using UNION SELECT
declare @debug bit = 0 -- used to allow debugging to be turned on to the stored procedure - in case of queries
declare @GenerateOneLinePerColumn bit = 0 -- used to display the columns and data on separate lines
declare @GenerateIdentityColumn bit = 1 -- used to prevent the identity columns from being scripted
 
--Variable declarations
DECLARE @InsertStmt varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Fields varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @SelList varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Data varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @FldCounter int
DECLARE @TableData nvarchar(max) -- change this to be (8000) for SQL Server 2000
 
-- added by Jane - 07/11/03
DECLARE @bitIdentity BIT
 
-- added by Jane 03/01/08
DECLARE @bitHasEncounteredText BIT
SET @bitHasEncounteredText = 0
 
-- added by Jane 15/01/08
DECLARE @bitHasEncounteredImage BIT
SET @bitHasEncounteredImage = 0
DECLARE @bitHasEncounteredBinary BIT
SET @bitHasEncounteredBinary = 0
DECLARE @bitHasEncounteredXML BIT
SET @bitHasEncounteredXML = 0
 
-- added by Jane - 23/01/08
DECLARE @bitInsertStatementPrinted BIT
SET @bitInsertStatementPrinted = 0

-- added by Jane - 17/04/09 - To try and cope with bits of data which are greater than 8000 bytes
DECLARE @bitDataExceedMaxPrintLength BIT
SET @bitDataExceedMaxPrintLength = 0
DECLARE @cintMaximumSupportedPrintByteCount INT
SET @cintMaximumSupportedPrintByteCount = 8000 -- based on SQL Server 2005
DECLARE @statementToOutput NVARCHAR(max) -- change this to be (8000) for SQL Server 2000
DECLARE @NextCR INT -- used to split the output up into smaller chunks - look for carriage returns/line feeds and break into separate print statements
DECLARE @statementsToOutput TABLE (Id INT IDENTITY(1,1), singleStatement NVARCHAR (max) ) -- change this to be (8000) for SQL Server 2000
DECLARE @id INT
DECLARE @singleStatement NVARCHAR(max) -- change this to be (8000) for SQL Server 2000
DECLARE @loop INT

-- added by Jane - 16/12/03
SET NOCOUNT OFF

-- added by Jane - 16/04/09 - check for table existance
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table)
BEGIN

     -- added by Jane - 16/04/2009 - Show details of table being generated, and date/time
     PRINT REPLICATE('-', 37 + LEN(@table) + LEN(CONVERT(VARCHAR,GETDATE(),120)))         
     PRINT '-- Script generated for table ' + @table + ' on ' + CONVERT(VARCHAR,GETDATE(),120) + ' --'
     PRINT REPLICATE('-', 37 + LEN(@table) + LEN(CONVERT(VARCHAR,GETDATE(),120)))
 
     -- added by Jane - 07/11/03
     SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_Name =@table
     
     -- added by Jane - 03/01/08
     PRINT '-- ** Start of Inserts ** --'
     PRINT ''
     
     -- added by Jane - 07/11/03
     -- AND @GenerateIdentityColumn = 1 added by Jane - 07/01/09
     IF @bitIdentity = 1 AND @GenerateIdentityColumn = 1
     BEGIN
           PRINT 'SET IDENTITY_INSERT [' + @table + '] ON '
     END
     
     --initialize some of the variables
     -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
     SELECT @InsertStmt = 'INSERT INTO [' + @Table + '] '+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END,
           @Fields = '',
           @Data = '',
           @SelList = 'SELECT ',
           @FldCounter = 0
     
     --create a cursor that loops through the fields in the table
     --and retrieves the column names and determines the delimiter type that the
     --field needs
     DECLARE CR_Table CURSOR FAST_FORWARD FOR
          
           SELECT COLUMN_NAME,
                  'IsChar' = CASE
                  WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0
                  WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1
                  WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
                  WHEN DATA_TYPE in ('text', 'ntext') THEN 3
                  WHEN DATA_TYPE in ('image') THEN 4 -- added by Jane - 15/01/08
                  WHEN DATA_TYPE in ('binary', 'varbinary') THEN 5 -- added by Jane - 15/01/08
                  WHEN DATA_TYPE in ('sql_variant') THEN 6 -- added by Jane - 15/01/08 - Force to be converted as varchars
                  WHEN DATA_TYPE in ('xml') THEN 7 -- added by Jane - 15/01/08
                  ELSE 9
           END
           FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
           INNER JOIN syscolumns sc WITH (NOLOCK)
           ON c.COLUMN_NAME = sc.name
           INNER JOIN sysobjects so WITH (NOLOCK)
           ON sc.id = so.id
           AND so.name = c.TABLE_NAME
           WHERE table_name = @table
           AND DATA_TYPE <>'timestamp'
           AND sc.IsComputed = 0     
           AND 1 =
               CASE @GenerateIdentityColumn
               WHEN 1 -- When we want the identity columns to be generated, then always include the column
               THEN 1
               ELSE
                    CASE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') -- Check if the column has the property IsIdentity
                    WHEN 1 -- If it does
                    THEN 0 -- don't include this column
                    ELSE 1 -- otherwise include this column
                    END
               END
           ORDER BY ORDINAL_POSITION
           FOR READ ONLY
           OPEN CR_Table
     
           FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
     
     WHILE (@@fetch_status <> -1)
     BEGIN
     
           IF @IsChar = 3
                  SET @bitHasEncounteredText = 1          
     
           -- added by Jane - 15/01/08
           IF @IsChar = 4
                  SET @bitHasEncounteredImage = 1
           IF @IsChar = 5
                  SET @bitHasEncounteredBinary = 1
     
           IF (@@fetch_status <> -2)
           BEGIN
     
                  -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
                  -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
                  -- Special case for first field
                  IF @FldCounter = 0
                  BEGIN
                          SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                          -- Updated by Jane - 08/06/09 - prefix string with N to cope with extended character sets                         
                          SELECT @SelList =  CASE @IsChar
                                 WHEN 1 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''') + '''''''' ,''NULL'')  ' + ' COLLATE database_default + '
                                 WHEN 2 THEN @SelList +  ' ISNULL(''N'''''' + CONVERT(varchar(20),[' + @ColName + ']) + '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 3 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 4 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                                 WHEN 5 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                                 WHEN 6 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 7 THEN @SelList +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                                 END
                          SELECT @FldCounter = @FldCounter + 1
                          SET @SelList = @Sellist
                          FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
                  END
     
                  -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
                  -- Updated by Jane - 03/01/08 to cope with NULL replacements          
                  -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
                  -- Updated by Jane - 18/12/03 to prevent single field tables having that field displayed twice
                  -- Updated by Jane - 20/08/08 to incorporate the @GenerateOneLinePerColumn parameter suggested by Christian
                  IF @@fetch_status <> -1
                  BEGIN
                          SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                          -- Updated by Jane - 08/06/09 - prefix string with N to cope with extended character sets
                          SELECT @SelList =  CASE @IsChar
                                 WHEN 1 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL(''N'''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''' ) +  '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 2 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + '  +  'ISNULL(''N'''''' + CONVERT(varchar(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
                                 WHEN 3 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''' )+  '''''''',''NULL'')  ' + ' COLLATE database_default + '
                                 WHEN 4 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                                 WHEN 5 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                                 WHEN 6 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 WHEN 7 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL(''N'''''' + REPLACE(CONVERT(VARCHAR(max),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                                 ELSE @SelList  + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' + ' ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                          END 
                  END
           END 
     
           FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
     END
     
     CLOSE CR_Table
     DEALLOCATE CR_Table
     
     SELECT @Fields =  SUBSTRING(@Fields, 1,(len(@Fields)-1))
     
     SELECT @SelList =  SUBSTRING(@SelList, 1,(len(@SelList)-1))
     SELECT @SelList = @SelList + ' FROM ' + @table
     
     IF LEN(@restriction) > 0
     BEGIN
           SELECT @SelList = @SelList + ' WHERE ' + @restriction
     END
     
     -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
     SELECT @InsertStmt = @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN REPLACE(@Fields,', ',',' + CHAR(13)) ELSE @Fields END + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')'
     
     --for debugging purposes...
     IF @Debug = 1
     BEGIN
           PRINT '*** DEBUG INFORMATION - THIS IS THE SELECT STATEMENT BEING RUN *** '
           PRINT @sellist
           PRINT '*** END DEBUG ***'
     END
     
     -- added by Jane - 16/12/03
     SET NOCOUNT ON
     
     --now we need to create and load the temp table that will hold the data
     --that we are going to generate into an insert statement
     
     CREATE TABLE #TheData (TableData VARCHAR(max)) -- change this to be (8000) for SQL Server 2000
     INSERT INTO #TheData (TableData) EXEC (@SelList)
     
     --Cursor through the data to generate the INSERT statement / VALUES/SELECT/UNION SELECT clause
     DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
     READ ONLY
     OPEN CR_Data
     FETCH NEXT FROM CR_Data INTO @TableData
     
           WHILE (@@fetch_status <> -1)
           BEGIN
                 IF (@@fetch_status <> -2)
                 BEGIN                                           

                       -- Updated by Jane 17/04/09 instead of printing at this point, store the output in a @statementToOutput variable.  This allows us to try and split it to
                       -- to print within the 8000 byte limit imposed by the PRINT function           
                       -- Updated by Jane 23/01/08 after suggestion posted to blog at http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c2055936172890486440
                       IF (@producesingleinsert = 1 )
                              IF (@bitInsertStatementPrinted = 0)
                              BEGIN
                                    SET @statementToOutput = @InsertStmt + char(13) + 'SELECT ' + @TableData                                   
                                    SET @bitInsertStatementPrinted = 1
                              END
                              ELSE
                              BEGIN
                                    SET @statementToOutput = 'UNION SELECT ' + @TableData
                              END
                       ELSE
                       BEGIN
                              -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
                              SET @statementToOutput =  @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + 'VALUES ' + + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + @TableData + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')' + CHAR(13)
                       END
     
                       -- Added by Jane - 17/04/09 - check for length of @statementToOutput
                       -- if it exceeds the maximum length, then lets attempt to split it on CRs as these will be done via separate PRINT statements
                       IF DATALENGTH(@statementToOutput) > @cintMaximumSupportedPrintByteCount                                                                                                                    
                       BEGIN                                                     
                       
                           -- Break the @statementToOutput based on CHAR(13) and put separate data values into @statementsToOutput table

                           -- Get rid of double line breaks
                           -- Replace CHAR(10) with CHAR(13)
                           SET @statementToOutput = REPLACE(@statementToOutput,CHAR(10),CHAR(13))
                           -- Replace CHAR(13)+CHAR(13) with CHAR(13)
                           SET @statementToOutput = REPLACE(@statementToOutput,CHAR(13)+CHAR(13),CHAR(13))

                           SET @NextCR = Charindex(CHAR(13),@statementToOutput)
                           WHILE @NextCR  > 0
                           BEGIN
                              
                               INSERT INTO @statementsToOutput VALUES(LEFT(@statementToOutput,@NextCR - 1))

                               SET @statementToOutput = Right(@statementToOutput,Len(@statementToOutput) - @NextCR)
                               SET @NextCR = Charindex(CHAR(13),@statementToOutput)
                           END                                                     
                           INSERT INTO @statementsToOutput VALUES(@statementToOutput)                                                                                  

                           -- Output the statements line by line
                           SET @loop = 1
                           SET @id = -1

                           WHILE @loop = 1
                           BEGIN
                               SELECT TOP 1 @id = Id, @singleStatement = singlestatement
                               FROM @statementsToOutput
                               WHERE id > @id
                               ORDER BY Id

                               SET @loop = @@ROWCOUNT

                               IF @loop = 0
                                    BREAK

                               -- No guarantee that we still don't exceed the limits, but should have more of a chance of avoiding them
                               IF DATALENGTH(@singleStatement) > @cintMaximumSupportedPrintByteCount
                               BEGIN
                                    SET @bitDataExceedMaxPrintLength = 1
                                    SELECT @singleStatement                          
                               END

                               PRINT @singleStatement
                           END                                                      
                       END   
                       ELSE
                       BEGIN                   
                           -- Don't need to worry, we haven't exceeded the 8000 byte limit so just print it
                           PRINT @statementToOutput
                       END

                       IF @generateGo = 1
                       BEGIN
                              PRINT 'GO'
                       END
                 END
                 FETCH NEXT FROM CR_Data INTO @TableData
           END
     CLOSE CR_Data
     DEALLOCATE CR_Data
     
     -- added by Jane - 07/11/03
     -- AND @GenerateIdentityColumn = 1 added by Jane - 07/01/09
     IF @bitIdentity = 1 AND @GenerateIdentityColumn = 1
     BEGIN
           PRINT 'SET IDENTITY_INSERT [' + @table + '] OFF '
     END
     
     -- added by Jane - 03/01/08
     PRINT '-- ** End of Inserts ** --'
     
     IF @bitHasEncounteredImage = 1
     BEGIN
           PRINT '-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Images are not supported by this script at this time.  ** --'
     END
     
     -- added by Jane - 15/01/08
     IF @bitHasEncounteredBinary = 1
     BEGIN
           PRINT '-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Binary and VarBinary are not supported by this script at this time.  ** --'
     END
     
     -- 16/02/09 - These checks are only required if the database is SQL Server 2000
     DECLARE @Version VARCHAR(100)
     SELECT @Version = @@VERSION
     IF PATINDEX('%8.00%',@Version) > 0
     BEGIN
          IF @bitHasEncounteredXML = 1
          BEGIN
                PRINT '-- ** WARNING: This will convert any ''xml'' data to be ''varchar(8000)'' ** --'
          END
          
          -- added by Jane - 03/01/08
          IF @bitHasEncounteredText = 1
          BEGIN
                PRINT '-- ** WARNING: This will convert any ''text'' or ''ntext'' data to be ''varchar(8000)'' ** --'
          END
     END

     IF @bitDataExceedMaxPrintLength = 1
     BEGIN
          PRINT '-- ** WARNING: The data length for at least one row exceeds '+ CONVERT(VARCHAR(6),@cintMaximumSupportedPrintByteCount) + ' bytes.  The PRINT command is limited to ' + CONVERT(VARCHAR(6),@cintMaximumSupportedPrintByteCount) + ' bytes (for more information see http://msdn.microsoft.com/en-us/library/ms176047.aspx).  Do not trust this data. ** --'
     END
END
ELSE
BEGIN

     -- added by Jane - 16/04/2009 - Warn user that table doesn't exist
     PRINT REPLICATE('-', 43 + LEN(@table))    
     PRINT '-- Table ' + @table + ' doesn''t exist on this database --'
     PRINT REPLICATE('-', 43 + LEN(@table))

END
--RETURN (0)
 
GO
DROP TABLE #TheData
GO

| | # 
# Wednesday, 29 January 2014
( SQL )

http://community.dynamics.com/gp/b/ifks/archive/2011/07/20/sql-script-for-generating-insert-statements-from-table-data.aspx

/*
Use this script to create insert statements for each row in the specified table.
 
Instructions:
1. Set the database you want to script from as normal.
 
2. change the set @TableName = '<yourtablename>' line to be the
table you want to script out.
 
3. Run the script and copy all the text from the results below
the line with all the dashes (----).
 
Notes:
   If you get the error message "Invalid object name '<yourtablename>'."
   then you either forgot to set the correct database or you spelled
   your table name wrong
 
Credits:
  Bob Wiechman - Fix for smalldatetime support
  Richard Lesh - correct support of uniqueidentifiers, automatic
      setting of Identity off/on, add Where clause support, more detail in
        debug mode.
  Fuhai Li - correct support of null values;
             fix the coallation conflicts via using default coallation
             correct the identity insert on/off for non debug mode
             column name with bracket to make it robust for naming
  To do: test the null values for gui type
  To do: add on parameters to choose which fields would export via colorder such as 1,2,...   
*/
 
DECLARE @TableName SYSNAME
DECLARE @WhereClause VARCHAR(1024)
DECLARE @IdentityInsert INT
DECLARE @ColName SYSNAME
DECLARE @ColType TINYINT
DECLARE @ColStatus TINYINT
DECLARE @DebugMode BIT
DECLARE @ColList NVARCHAR(4000)
DECLARE @ValList NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NCHAR(10)
DECLARE @SQL3 NCHAR(1000)
 
--Your Table Name
SET @TableName = 'tbl_RequestData_FormOwner'
 
-- limit scope of inserts, this will be hard coded thing to narrow down the set
SET @WhereClause = ''
 
-- set to 1 if you only want a script
SET @DebugMode = 0
 
-- set to 1 if you want to force IDENTITY_INSERT statements
SET @IdentityInsert = 0
 
SET @ColList = ''
SET @ValList = ''
SET @SQL1 = 'SELECT REPLACE(''INSERT INTO ' + @TableName + ' ('
SET @SQL2 = ') VALUES ('
SET @SQL3 = ')'' COLLATE DATABASE_DEFAULT, ''''''NULL'''''', ''NULL'') FROM '
    + @TableName
 
IF @DebugMode = 1
   PRINT '-- StmtShell: ' + @sql1 + @sql2 + @sql3
 
DECLARE csrColumns CURSOR LOCAL fast_forward
FOR SELECT
c.name,
c.xtype,
c.STATUS
FROM
syscolumns c
INNER JOIN sysobjects o
ON o.id = c.id
WHERE
o.name = @TableName
AND o.xtype IN ('U', 'S')
ORDER BY
ColID
 
OPEN csrColumns
FETCH NEXT FROM csrColumns INTO @ColName, @ColType,
    @ColStatus
 
WHILE @@fetch_status = 0
      BEGIN
            SET @ColList = @ColList + ' ' + '[' + @ColName + ']'
            IF @ColType IN (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48,
                            165)
               SET @ValList = @ValList + ' ''+ISNULL(CONVERT(VARCHAR(200),'
                   + @ColName + '),''NULL'')+'''
            ELSE
               IF @ColType IN (175, 239, 231, 231, 167)
                  SET @ValList = @ValList + ' ''''''+ISNULL(' + @ColName
                      + ',''NULL'')+'''''''
               ELSE
                  IF @ColType IN (58, 61, 36)
                     SET @ValList = @ValList
                         + ' ''''''+ISNULL(CONVERT(VARCHAR(200),' + @ColName
                         + '),''NULL'')+'''''''
            IF @DebugMode = 1
               BEGIN
                     PRINT '-- @ValList: ' + @ValList
               END
            IF (@ColStatus & 0x80) = 0x80
               BEGIN
                     SET @IdentityInsert = 1
               END         
            FETCH NEXT FROM csrColumns INTO @ColName, @ColType,
            @ColStatus
      END
 
CLOSE csrColumns
DEALLOCATE csrColumns
 
SET @ColList = REPLACE(LTRIM(@ColList), ' ', ', ')
SET @ValList = REPLACE(LTRIM(@ValList), ' ', ', ')
 
IF @IdentityInsert = 1
   AND @DebugMode = 1
   PRINT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
IF @IdentityInsert = 1
   AND @DebugMode = 0
   SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
 
IF @DebugMode = 1
   PRINT @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause
ELSE
   EXEC
   (
   @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' '
   + @WhereClause
   )
 
IF @IdentityInsert = 1
   AND @DebugMode = 1
   PRINT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
IF @IdentityInsert = 1
   AND @DebugMode = 0
   SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'

| | # 
# Tuesday, 29 October 2013
( SQL )

type *.sql > allfiles.sql

| | # 
# Friday, 25 October 2013
# Wednesday, 02 October 2013
# Thursday, 29 August 2013
( SQL )

image
Transactions set to rollback

image
Transactions set to commit

| | # 
# Friday, 02 August 2013
( SQL )

This post stems from doing some SQL work using text processing http://stackoverflow.com/questions/17999675/c-sharp-performance-chunking-write-of-file-with-appendalltext
which although is fast, is more brittle.

The data I was working with came from the Script Grid Results of:

http://www.ssmstoolspack.com/

http://phelabaum.com/archive/2011/02/ssms-tools-pack-script-grid-results/

Many thanks Rohan for these tips:

  • Get data into a table in SQL at the earliest possible opportunity and lean on the power of SQL
  • You can right click on a table and ‘Edit 200 rows’ and then paste from excel if you are pasting the same number of columns in the same order, but performance of that is not good
  • BCP

BCP

A much better trick that works with textual and integer data OK so far (i.e. I didn’t have any numeric or date columns so your mileage may vary) is to copy and paste direct from excel into a BCP .dat file that has been generated with the –c (character data ) flag.

(see here for syntax, or enter ‘bcp /?’ at a command prompt for or help)

http://technet.microsoft.com/en-us/library/ms162802.aspx

So, very rough steps are:

1.    Create a table with the right shape (i.e. the number, data type, and order of columns that you have in the excel sheet)
2.    bcp [DB_Name].[Schema_Name].[Table_Name] out “[full file path to write the .dat file to]” –c –T –S [SQL_Instance_Name]
a.    i.e. this means dump the data out to a file in character data format to the given path
3.    open the .dat file in a text editor and clear out any data in there (it should be empty if you just created the table)
4.    copy paste data from excel (I always selected the exact cells I needed (without headers) just to be safe, I don’t know how critical this is)
5.    save the text edits
6.    use BCP again to send the same file back into the table, so something like (off the top of my head):
7.    bcp [DB_Name].[Schema_Name].[Table_Name] in “[full file path we write the .dat file to]” –c –T –S [SQL_Instance_Name]

You can probably avoid the BCP OUT step as the fie obviously starts off empty anyway. If you have problems you can put some rows into the table and BCP OUT to see what form the character data needs to be in to get the data into the table.

You can obviously use the Import/Export wizard too but this is a nice simple approach for a simple data load with simple data types. It’s extremely fast.

If command-line BCP is blocked in a PRD environment, a .dat file can also be imported with the BULK INSERT command from within SSMS if you can get the file into a suitable folder share.

http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx

Once stuff is in a table it’s so much easier to deal with.

| | # 
# Thursday, 01 August 2013
( c# | SQL )

        static void Main(string[] args)
        {
            string text = File.ReadAllText(@"e:\temp\query.sql");

            string text2 = "";

            //split by /r/n - ie line
            var allLines = text.Split('\n');
            for (int i = 0; i < allLines.Length-1; i++)
            //for (int i = 0; i < 3000; i++)
            {
                var line = allLines[i].Trim();

                var bits = line.Split('\'');

                var value1 = bits[1];
                var value2 = bits[3];
                var value3 = bits[5];

                var message ="INSERT [PreStaging].[Import_AccountEmployeeMapping] ([AccountName], [EmployeeID], [UER2_PlatformID]) VALUES (N" +
                            "'" + value1 + "', "
                            + value2 + ", "
                            + value3 + ")";

                text2 += message + Environment.NewLine;

                if (i % 1000 == 0) Console.WriteLine(i);
            }
           
            File.WriteAllText(@"e:\temp\query2.sql", text2);
        }

A C# console application.

| | # 
( Notepad++ | SQL )

Ctrl K – comment in ie each line --

Ctrl Shift K – uncomment SQL ie take out --

| | # 
# Wednesday, 17 July 2013
( SQL | SQLServer )

http://www.codeproject.com/Articles/543164/QueryplusOptimizationplusandplustheplusSQLplusServ

Very useful in debugging why sometimes a SP was running quickly and sometimes not.  This was an issue of calling a SP, and when calling with a NULL value in a parameter it didn’t perform well subsequently for non null values.  If call with a non null value to begin with, then both null and non null perform well.

DECLARE @dbId INTEGER
SELECT @dbId  = dbid FROM master.dbo.sysdatabases WHERE name = ‘myDatabase’
DBCC FLUSHPROCINDB (@dbId)
| | # 
# Tuesday, 16 July 2013
( SQL )

    SELECT U.UserID, U.FirstName, U.LastName, 2 AS Colour
    INTO #temp
    FROM
        ( SELECT U.UserID, U.FirstName, U.LastName
                ...
          UNION
          SELECT U.UserID, U.FirstName, U.LastName
                    ..
        ) AS U

Very useful to aggregate everything together then be able to select from it

| | # 
( SQL )

Have got the scenario where I’d like a SQL SP to return data like:

UserID   FirstName   LastName  Colour   RoleNames

1003      Dave             Mateer          2              RoleA, RoleB, RoleC

1004      Bob              Smith             2              RoleA, RoleC, RoleF, RoleX

So one way is using XML PATH in a subquery: http://www.rdacorp.com/2011/10/using-for-xml-path-to-combine-multiple-record-values-into-one-column/

-- Red only
    SELECT DISTINCT U.UserID, U.FirstName, U.LastName, 2 AS Colour, --ROL.Name,
                (
                    SELECT DISTINCT ROLSUB.Name + ', ' as [text()]
                    FROM Roles ROLSUB
                    INNER JOIN RoleUserLinks RULSUB ON RULSUB.RoleID = ROLSUB.RoleID
                    WHERE
                        RUL.UserID = RULSUB.UserID
                        AND RULSUB.LinkStatusID = 1
                    FOR XML PATH('')
                ) AS RoleNames

                  FROM Users U INNER JOIN Accounts AC ON U.UserID = AC.UserID AND AC.WorldBuilderStatusID = 1 AND U.WorldBuilderStatusID = 1 AND AC.AccountTypeID = 1 -- User account type       
                  INNER JOIN GroupAccountLinks GAL ON GAL.AccountID = AC.AccountID AND GAL.WorldBuilderLinkStatusID = 1 AND GAL.FilterStatusID = 0
                  INNER JOIN Groups G ON G.GroupID = GAL.GroupID AND G.WorldBuilderStatusID = 1
                  INNER JOIN ApplicationGroupLinks AGL ON G.GroupID = AGL.GroupID AND AGL.WorldBuilderLinkStatusID = 1 AND AGL.FilterStatusID IN (0, 4)
                  INNER JOIN ApplicationGroupEnvironments AGE ON AGE.ApplicationGroupID = AGL.ApplicationGroupID AND AGE.WorldBuilderLinkStatusID = 1 AND (AGE.DeploymentEnvironmentID = @environment OR @environment IS NULL)
                  INNER JOIN Applications A ON A.ApplicationID = AGL.ApplicationID AND A.WorldBuilderStatusID = 1
                  INNER JOIN ReviewItems RI ON RI.GroupAccountID = GAL.GroupAccountID AND RI.ReviewItemStatusID = 0
                  INNER JOIN Reviews R ON RI.ReviewID = R.ReviewID AND R.ApplicationID = A.ApplicationID AND R.ReviewStatusID = 1
                  LEFT OUTER JOIN RoleUserLinks RUL ON U.UserID = RUL.UserID
                  LEFT OUTER JOIN Roles ROL ON RUL.RoleID = ROL.RoleID
                  WHERE ResponsibleReviewerID = @responsibleReviewerID
                      AND (U.IsLeaver = 0 OR U.IsLeaver IS NULL)
                      AND (A.ApplicationID = @applicationID OR @applicationID IS NULL)
                      AND (RUL.RoleID = @roleID OR @roleID IS NULL)
                      AND (RUL.LinkStatusID = 1 OR @roleID IS NULL)

However a query which was taking 4 seconds is now taking 16seconds with the subquery.

This query takes 4secs (using worst case scenario data):

SELECT DISTINCT U.UserID, U.FirstName, U.LastName, 2 AS Colour, ROL.Name
                  FROM Users U INNER JOIN Accounts AC ON U.UserID = AC.UserID AND AC.WorldBuilderStatusID = 1 AND U.WorldBuilderStatusID = 1 AND AC.AccountTypeID = 1 -- User account type       
                  INNER JOIN GroupAccountLinks GAL ON GAL.AccountID = AC.AccountID AND GAL.WorldBuilderLinkStatusID = 1 AND GAL.FilterStatusID = 0
                  INNER JOIN Groups G ON G.GroupID = GAL.GroupID AND G.WorldBuilderStatusID = 1
                  INNER JOIN ApplicationGroupLinks AGL ON G.GroupID = AGL.GroupID AND AGL.WorldBuilderLinkStatusID = 1 AND AGL.FilterStatusID IN (0, 4)
                  INNER JOIN ApplicationGroupEnvironments AGE ON AGE.ApplicationGroupID = AGL.ApplicationGroupID AND AGE.WorldBuilderLinkStatusID = 1 AND (AGE.DeploymentEnvironmentID = @environment OR @environment IS NULL)
                  INNER JOIN Applications A ON A.ApplicationID = AGL.ApplicationID AND A.WorldBuilderStatusID = 1
                  INNER JOIN ReviewItems RI ON RI.GroupAccountID = GAL.GroupAccountID AND RI.ReviewItemStatusID = 0
                  INNER JOIN Reviews R ON RI.ReviewID = R.ReviewID AND R.ApplicationID = A.ApplicationID AND R.ReviewStatusID = 1
                  LEFT OUTER JOIN RoleUserLinks RUL ON U.UserID = RUL.UserID
                  LEFT OUTER JOIN Roles ROL ON RUL.RoleID = ROL.RoleID
                  WHERE ResponsibleReviewerID = @responsibleReviewerID
                      AND (U.IsLeaver = 0 OR U.IsLeaver IS NULL)
                      AND (A.ApplicationID = @applicationID OR @applicationID IS NULL)
                      AND (RUL.RoleID = @roleID OR @roleID IS NULL)
                      AND (RUL.LinkStatusID = 1 OR @roleID IS NULL)

And gives:

UserID   FirstName   LastName  Colour   RoleNames

1003      Dave             Mateer          2              RoleA

1003      Dave             Mateer          2              RoleB

1003      Dave             Mateer          2              RoleC

1004      Bob              Smith             2              RoleA etc..

Using a left outer join:

    SELECT DISTINCT U.UserID, U.FirstName, U.LastName, 2 AS Colour, RSUB.Name--, ROL.Name
                  FROM Users U INNER JOIN Accounts AC ON U.UserID = AC.UserID AND AC.WorldBuilderStatusID = 1 AND U.WorldBuilderStatusID = 1 AND AC.AccountTypeID = 1 -- User account type       
                  INNER JOIN GroupAccountLinks GAL ON GAL.AccountID = AC.AccountID AND GAL.WorldBuilderLinkStatusID = 1 AND GAL.FilterStatusID = 0
                  INNER JOIN Groups G ON G.GroupID = GAL.GroupID AND G.WorldBuilderStatusID = 1
                  INNER JOIN ApplicationGroupLinks AGL ON G.GroupID = AGL.GroupID AND AGL.WorldBuilderLinkStatusID = 1 AND AGL.FilterStatusID IN (0, 4)
                  INNER JOIN ApplicationGroupEnvironments AGE ON AGE.ApplicationGroupID = AGL.ApplicationGroupID AND AGE.WorldBuilderLinkStatusID = 1 AND (AGE.DeploymentEnvironmentID = @environment OR @environment IS NULL)
                  INNER JOIN Applications A ON A.ApplicationID = AGL.ApplicationID AND A.WorldBuilderStatusID = 1
                  INNER JOIN ReviewItems RI ON RI.GroupAccountID = GAL.GroupAccountID AND RI.ReviewItemStatusID = 0
                  INNER JOIN Reviews R ON RI.ReviewID = R.ReviewID AND R.ApplicationID = A.ApplicationID AND R.ReviewStatusID = 1
                  LEFT OUTER JOIN RoleUserLinks RUL ON U.UserID = RUL.UserID
                  LEFT OUTER JOIN Roles ROL ON RUL.RoleID = ROL.RoleID

                  -- virtual table RSUB
                  LEFT OUTER JOIN (SELECT DISTINCT ROLSUB.Name, RULSUB.UserID
                                   FROM Roles ROLSUB
                                   INNER JOIN RoleUserLinks RULSUB ON RULSUB.RoleID = ROLSUB.RoleID
                                   --FOR XML PATH('')
                                   ) RSUB
                                ON U.UserID = RSUB.UserID
                                    

                  WHERE ResponsibleReviewerID = @responsibleReviewerID
                      AND (U.IsLeaver = 0 OR U.IsLeaver IS NULL)
                      AND (A.ApplicationID = @applicationID OR @applicationID IS NULL)
                      AND (RUL.RoleID = @roleID OR @roleID IS NULL)
                      AND (RUL.LinkStatusID = 1 OR @roleID IS NULL)

Not quite right and slow perf.

Temp Table

Very fast and seems to display the correct data.


DECLARE
    @responsibleReviewerID bigint,
    @applicationID bigint = NULL,
    @environment bigint = NULL,
    @userUIStatus bigint = NULL, -- Null is Display Red and Green, 1 is Green only, 2 is Red only
    @roleID bigint = NULL

SET @responsibleReviewerID=1135;
SET @applicationID=NULL;
SET @environment=NULL;
SET    @userUIStatus=2;
SET    @roleID=NULL;

-- Red only
SELECT DISTINCT U.UserID, U.FirstName, U.LastName, 2 AS Colour
        INTO #temp
                FROM Users U INNER JOIN Accounts AC ON U.UserID = AC.UserID AND AC.WorldBuilderStatusID = 1 AND U.WorldBuilderStatusID = 1 AND AC.AccountTypeID = 1 -- User account type       
                INNER JOIN GroupAccountLinks GAL ON GAL.AccountID = AC.AccountID AND GAL.WorldBuilderLinkStatusID = 1 AND GAL.FilterStatusID = 0
                INNER JOIN Groups G ON G.GroupID = GAL.GroupID AND G.WorldBuilderStatusID = 1
                INNER JOIN ApplicationGroupLinks AGL ON G.GroupID = AGL.GroupID AND AGL.WorldBuilderLinkStatusID = 1 AND AGL.FilterStatusID IN (0, 4)
                INNER JOIN ApplicationGroupEnvironments AGE ON AGE.ApplicationGroupID = AGL.ApplicationGroupID AND AGE.WorldBuilderLinkStatusID = 1 AND (AGE.DeploymentEnvironmentID = @environment OR @environment IS NULL)
                INNER JOIN Applications A ON A.ApplicationID = AGL.ApplicationID AND A.WorldBuilderStatusID = 1
                INNER JOIN ReviewItems RI ON RI.GroupAccountID = GAL.GroupAccountID AND RI.ReviewItemStatusID = 0
                INNER JOIN Reviews R ON RI.ReviewID = R.ReviewID AND R.ApplicationID = A.ApplicationID AND R.ReviewStatusID = 1
                LEFT OUTER JOIN RoleUserLinks RUL ON U.UserID = RUL.UserID
                LEFT OUTER JOIN Roles ROL ON RUL.RoleID = ROL.RoleID
                WHERE ResponsibleReviewerID = @responsibleReviewerID
                    AND (U.IsLeaver = 0 OR U.IsLeaver IS NULL)
                    AND (A.ApplicationID = @applicationID OR @applicationID IS NULL)
                    AND (RUL.RoleID = @roleID OR @roleID IS NULL)
                    AND (RUL.LinkStatusID = 1 OR @roleID IS NULL)

SELECT DISTINCT UserID, FirstName, LastName, Colour,
                (
                    SELECT DISTINCT ROLSUB.Name + ', ' as [text()]
                    FROM Roles ROLSUB
                    INNER JOIN RoleUserLinks RULSUB ON RULSUB.RoleID = ROLSUB.RoleID
                    WHERE
                        #temp.UserID = RULSUB.UserID
                        AND RULSUB.LinkStatusID = 1
                    FOR XML PATH('')
                ) AS RoleNames
FROM #temp
DROP TABLE #temp
| | # 
# Wednesday, 08 May 2013
( Index | SQL | SQLServer )

Part of tuning I want to update the indexes of a production DB to one with better indexes that has been worked on in dev.

Drop all Non Clustered indexes on the database we want to update

select 'DROP index ' + i.name + ' on ' + so.name
from sys.indexes i
inner join sys.objects so ON i.object_id = so.object_id
where i.type_desc = 'nonclustered'
AND so.type = 'u'

However I can’t do this as:

An explicit DROP INDEX is not allowed on index 'xxx'.  It is being used for UNIQUE KEY constraint enforcement.

looks like I’ll need to drop the uniqueness eg

alter table tbl_Payroll_SubType drop constraint IX_tbl_Payroll_SubType
Can see UC’s here: (against the actual db – not master)

SELECT *
FROM sys.objects
WHERE type = 'UQ' ;

What Are Clustered Indexes?

http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

..Telling the database to store close values actually close to one another on the disk.

image

If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.

P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story.

Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.

Non Clustered Index

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Unique Non Clustered Index and Key

When want to enforce uniqueness eg:

CostCentreID

CostCentreCode ** on this column must be unique

we put on a nonclustered index which is unique called: UC_CostCentres_CostCentreCode

this appears in Keys too.

| | # 
# Tuesday, 07 May 2013
( Index | Performance | SQL | SQLServer )

http://www.brentozar.com/blitz/

Good at analysing indexes.

| | # 
# Friday, 11 January 2013
( SQL )

http://www.dpriver.com/pp/sqlformat.htm

Very handy for understanding SQL

| | # 
# Tuesday, 18 October 2011
( Massive | SQL )

Clarifying how to get different types in and out of Massive elegantly:

image

test database of MassiveTypeTest

image

showing correct types coming out of Massive ie DateTime, Int32 and String.  NZ locale.

Getting Correct Types/Data into Massive

image

CreateFrom just puts the formcolleciton into an ExpandoObject, of strings. Making sure that column names are in db.

image

using prototype I can put in default values from the db.  This is very handy in Create.  As then can easily send back a failed create and display the correct data without having to check for nulls initally:

image

Best practice seems to be to insert as: yyyy-MM-dd

//convert nz to sql date
            //nz is dd/mm/yyyy   sql is yyyy-mm-dd
            string x = itemToCreate.DateCreated;
            string[] bits = x.Split('/');
            var day = bits[0];
            var month = bits[1];
            var year = bits[2];
            var sqlDate = year + "-" + month + "-" + day;
            itemToCreate.DateCreated = sqlDate;

 

image

or

image

if we set getdate() in default for the datecolumn in sql.  Then when prototype runs, it makes a new shortdatestring for us.

Summary

Data coming out of Massive

The type is figured out by the sqldatareader

  • Query – calls RecordToExpando..then yield returns an IEnumerable of expandoObjects
  • RecordToExpando – uses sqldatareader to put the type into the expando

Data going in to Massive:

anything.. Massive just makes SQL from the value that are passed in the expando.ToString()

  • CreateFrom checks to see if column name is in db, then makes an expandoObject
  • Insert(expandoObject) calls CreateInsertCommand which just loops over the expando as a dictionary
| | #