Generating INSERT statements for a table

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

-- run as script:

declare

@tableName varchar(100),

@schemaName varchar(100) = 'dbo',
 @extraWhere varchar(500)

select @tableName = 'TestTypes'
-- select @extraWhere = ' ID > 12 '

-- run as stored procedure:

/*

CREATE PROCEDURE InsertStatementsGeneratorForTable

@tableName varchar(100),

@schemaName varchar(100) = 'dbo'

as

*/

DECLARE @string nvarchar(max)  --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(max) --data types returned for respective columns
SET @string= N'INSERT INTO ' + @schemaName + '.' + @tableName+ N'('

SET @stringData = ''

DECLARE @colName nvarchar(50)

--Open a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
-------------- Start Select --------------------------
SELECT column_name,data_type FROM information_schema.columns 
WHERE table_name = @tableName
AND  TABLE_SCHEMA = @schemaName
-------------- End Select ----------------------------
OPEN cursCol
FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
 begin
 print N'Table ' + @schemaName + '.' + @tableName+ N' not found, processing skipped.'

 close curscol
 deallocate curscol
 return
END

WHILE @@FETCH_STATUS=0
BEGIN
 IF @dataType in ('varchar','char','nchar','nvarchar')
 BEGIN
  SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName + ', '''''''', '''''''''''')'+'+'''''+''''',''NULL'')+'',''+'
 END
 ELSE
 IF @dataType in ('text','ntext') --if the datatype is text or something else 
 BEGIN
  SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
 END
 ELSE
 IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
 BEGIN
  SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
 END
 ELSE 
 IF @dataType='datetime'
 BEGIN
  SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
 END
 ELSE 
 IF @dataType='image' 
 BEGIN
  SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
 END
 ELSE --presuming the data type is int,bit,numeric,decimal 
 BEGIN
  SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
 END

 SET @string=@string+@colName+','

 FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(max)

SET @query = 'SELECT ''' + 
    substring(@string,0,len(@string)) + 
    ') VALUES(''+ ' + 
    substring(@stringData,0,len(@stringData)-2) +
    '''+'')'' FROM ' + 
    @schemaName + '.' + 
    @tableName
IF @extraWhere IS NOT NULL

                SET @query = @query + N' WHERE ' + @extraWhere

exec sp_executesql @query
--select @query

print @stringData
print @string
print @query

CLOSE cursCol
DEALLOCATE cursCol

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

The source code was taken from the link below, and edited by me.

http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx

Another option - script via SSMS:
Right click on the DB name--> Tasks --> Generate Scripts --> in the advanced settings:

LOGINPROPERTY

SELECT LOGINPROPERTY ( 'LOGIN_NAME' , 'PROPERTY_NAME' )

LOGINPROPERTY  returns information about login policy settings.

Property should be one of those:

• BadPasswordCount
• BadPasswordTime
• DaysUntilExpiration
• DefaultDatabase
• DefaultLanguage
• HistoryLength
• IsExpired
• IsLocked
• IsMustChange
• LockoutTime
• PasswordHash
• PasswordLastSetTim

http://msdn.microsoft.com/en-us/library/ms345412.aspx

Cannot create index on view because the view is not schema bound

Error:

"Cannot create index on view '*' because the view is not schema bound"

Solution:

Check definitions of SCHEMABINDING:

http://copypastenet.blogspot.com/2011/05/with-schemabinding-objects-cannot-be.html

WITH SCHEMABINDING - objects cannot be modified in a way that would affect definition of objects that are referenced by schema bound

When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the definition of objects that are referenced by schema bound.

WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.

SCHEMABINDING in view:

CREATE VIEW [dbo].[VIEW_NAME] WITH SCHEMABINDING AS ....

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

http://msdn.microsoft.com/en-us/library/ms187956.aspx

SCHEMABINDING in functions:

CREATE FUNCTION [FUNCTION_NAME] (@INPUT ...) RETURNS ... WITH SCHEMABINDING

Specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

Improving query plans with the SCHEMABINDING option on T-SQL UDFs

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx

ORIGINAL_DB_NAME()

select ORIGINAL_DB_NAME ()

Returns the database name that is specified by the user.

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

Some basic and important Execution Plan rules

• Read from Right To Left and Top to Bottom.
• Read it from right to left, as data flow comes from right to left side.
• If you see nested loop, the upper side is the outer query, the lower side is inner query, inner query is executed once for each outer query.
• Follow Arrows until next branch from below.
• The thickness of the arrow reflects the amount of data being passed.
• execution cost of inner side for nested loop is per execution of outer side.
• If you see hash join, it means that hash table is built for upper (outer) query, then for each rows in lower (inner) query, query processor will look into the hash table to find match value.
• The number that displayed as a precentage is the relative cost to the query for the operator.

• Table Scan - scan all the rows of the table in order to select the results set.
• Index Scan - scan all the rows of the indexed table in order to select the results set. We will scan indexed table when all of the table rows, or the most of them will be returned.
• Index Scan - Seek the table, using indexes, in order to select the results set.
• In the most of the cases, seek is better than scan.

http://msdn.microsoft.com/en-us/library/ms175913.aspx

http://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/

SET NOCOUNT

SET NOCOUNT { ON | OFF } 

• When SET NOCOUNT is ON, the count is not returned. 
• When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

http://msdn.microsoft.com/en-us/library/ms189837.aspx

ANSI_WARNINGS

SET ANSI_WARNINGS { ON | OFF }

• When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. 
• When set to OFF, no warning is issued.

• When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. 
• When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned.

Notes:

1. SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views.
2. ANSI_WARNINGS should be set to ON for executing distributed queries.

http://msdn.microsoft.com/en-us/library/ms190368.aspx

ANSI_NULLS

SET ANSI_NULLS { ON | OFF }

ANSI_NULLS is a definition that deal with the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

• When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
• When SET ANSI_NULLS is OFF, a SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

ANSI_NULLS definition was created in order to follow the ISO / SQL-92 rules.

http://msdn.microsoft.com/en-us/library/ms188048.aspx

failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Error message:

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Solution:

Change definition of QUOTED_IDENTIFIER (ON<-->OFF).

Read about QUOTED_IDENTIFIER in:

http://copypastenet.blogspot.com/2011/05/quotedidentifier.html

QUOTED_IDENTIFIER

SET QUOTED_IDENTIFIER { ON | OFF }

QUOTED_IDENTIFIER is a definition that deal with quotation mark delimiting identifiers.

• When SET QUOTED_IDENTIFIER is ON (default), identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. 
• When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

Acording to the T-SQL ruels, literals must be delimited by single quotation marks:

Select "something"  -- error

Select 'something'  -- ok

, like when QUOTED_IDENTIFIER is ON.

But, in order to follow the ISO / SQL-92 rules regarding quotation mark delimiting identifiers and literal strings, we can set QUOTED_IDENTIFIER to OFF, and then:

Select "something"  -- ok

Select 'something'  -- ok

In the most of the cases, we will work according to the T-SQL ruels (QUOTED_IDENTIFIER ON).

We will set it to OFF when we will work agginst other systems, something that QUOTED_IDENTIFIER ON does not support or when we will replace existing systems to SQL Server, etc.

http://msdn.microsoft.com/en-us/library/ms174393.aspx

Unicode casting language errors

There are some errors that we get when casting unicode string to not-unicode, or vice versa.

Examples:

• Casting from nvarchar to varchar, char to nchar, etc.
• Filter unicode text (string in any not-English language) in not-unicode columns.

In most of the cases, to fix those errors it's enough to add N before the string (in order to make it unicode) or to fix in the stored code the casting (if in the code a parameter is define as varchar when the relevant column is nvarchar - fix the parameter declaration).

TYPE_NAME

TYPE_NAME(type_id)

Returns the unqualified type name of a specified type ID.

http://msdn.microsoft.com/en-us/library/ms189750.aspx

CHARINDEX()

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

• expression1 = the character expression that contains the sequence to be found. (varchar)
• expression2 = the character expression to be searched. (varchar)
• start_location = Is an expression at which the search starts. (integer/bigint, optional)

CHARINDEX searches expression2 for expression1 and returns its starting position if found. 

If expression1 is not found within expression2, CHARINDEX returns 0.

The search starts at start_location (if specified).

CHARINDEX is used to check if one string exists in another string.

Notes:

1. expression1 is limited to 8000 characters.
2. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.
3. If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
4. If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

http://msdn.microsoft.com/en-us/library/ms186323.aspx