Generate insert statements

The scripts section of SQLCentral has real wonderful scripts ! πŸ˜‰ One of them is indeed so useful for our MS SQL Server … You could find many variants of the same if you google it …

but this one is really awesome i feel …

Call the stored procedure to generate insert statement for any given table.Β  Or generate inserts for all tables using something like:

exec sp_MSforeachtable @command1="exec [spd_Build_Insert_Into_Stmt_With_Identity] '?','?','[LINKEDSERVERNAME].DATABASENAME.'"

/**********************************
Originally written by:β€ˆβ€ˆβ€ˆβ€ˆJesse McLain
Updated by:β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆDarren Gordon
***********************************/

Here goes the script :

alter PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]
@Source_Table varchar(200),β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- req'd; the name of the source table
@Target_Table varchar(200) = '',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- req'd; the name of the target table
@FromPrefix varchar(500)='',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- String that will be added to the from statement, this lets you use linked server data sources
@Print_Truncate char(1) = 'Y',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- optional; 'Y' to print truncate statement
@Print_Identity_Insert_Stmts char(1) = 'Y',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- optional; 'Y' to include the SET IDENTITY_INSERT
-- statements in the output
@Show_Matchless_Columns char(1) = 'N',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- optional; 'Y' to include the columns from each
-- table that have no match in the other table (if
-- the tables have slightly different structures);
-- these columns will be commented-out in output
@AddGo char(1) = 'Y'β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- Add a GO to the end which will allow you to view progress when a batch is running
AS
IF @Source_Table = 'help'
BEGIN
PRINT 'PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]'
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆ@Source_Table varchar(200),β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- required; the name of the source table '
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆ@Target_Table varchar(200) = '''',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- required; the name of the target table '
PRINT ' @FromPrefix varchar(500)='''',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- String that will be added to the from statement, this lets you use linked server data sources '
PRINT ' @Print_Truncate char(1) = ''Y'',β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- optional; ''Y'' to print truncate statement '
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆ@Print_Identity_Insert_Stmts char(1) = ''Y'',β€ˆβ€ˆβ€ˆβ€ˆ-- optional; ''Y'' to include the SET IDENTITY_INSERT '
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- statements in the output'
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆ@Show_Matchless_Columns char(1) = ''N''β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- optional; ''Y'' to include the columns from each '
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- table that have no match in the other table (if '
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- the tables have slightly different structures);'
PRINT 'β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- these columns will be commented-out in output'
PRINT ' @AddGo char(1) = ''Y''β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- Add a GO to the end which will allow you to view progress when a batch is running'

RETURN
END

DECLARE @sql_into varchar(8000)
DECLARE @sql_from varchar(8000)
DECLARE @sql varchar(8000)

DECLARE @crlf char(2)
SET @crlf = CHAR(13) + CHAR(10)

DECLARE @indent char(1)
SET @indent = CHAR(9)

DECLARE @ColName1 varchar(250)
DECLARE @ColOrder1 smallint
DECLARE @ColType1 varchar(50)
DECLARE @ColLength1 smallint
DECLARE @ColPrec1 smallint
DECLARE @ColScale1 smallint
DECLARE @IsNullable1 tinyint
DECLARE @ColName2 varchar(250)
DECLARE @ColOrder2 smallint
DECLARE @ColType2 varchar(50)
DECLARE @ColLength2 smallint
DECLARE @ColPrec2 smallint
DECLARE @ColScale2 smallint
DECLARE @IsNullable2 tinyint
DECLARE @OrderBy decimal(9,1)

DECLARE Table_Struct_Compare_Cursor CURSOR FOR
SELECT
ColName1,
ColOrder1,
ColType1,
ColLength1,
ColPrec1,
ColScale1,
IsNullable1,
ColName2,
ColOrder2,
ColType2,
ColLength2,
ColPrec2,
ColScale2,
IsNullable2,
OrderBy = ISNULL(CONVERT(decimal(9,1), ColOrder1), CONVERT(decimal(9,1), ColOrder2) + 0.5)
FROM
(SELECT DISTINCT
ColName1 = C.Name,
ColOrder1 = C.ColOrder,
ColType1 = T1.Name,
ColLength1 = C.Length,
ColPrec1 = C.XPrec,
ColScale1 = C.XScale,
IsNullable1 = C.IsNullable
FROM SysColumns C
JOIN SysObjects O1 ON O1.Id = C.Id
JOIN SysTypes T1 ON T1.XType = C.XType
WHERE O1.Name = REPLACE(REPLACE(REPLACE(@Target_Table,'[dbo].',''),'[',''),']','') and T1.Name <> 'sysname') AS C1
FULL OUTER JOIN
(SELECT DISTINCT
ColName2 = C.Name,
ColOrder2 = C.ColOrder,
ColType2 = T2.Name,
ColLength2 = C.Length,
ColPrec2 = C.XPrec,
ColScale2 = C.XScale,
IsNullable2 = C.IsNullable
FROM SysColumns C
JOIN SysObjects O2 ON O2.Id = C.Id
JOIN SysTypes T2 ON T2.XType = C.XType
WHERE O2.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']','') and T2.Name <> 'sysname') AS C2
ON C2.ColName2 = C1.ColName1
ORDER BY OrderBy, ColOrder1, ColOrder2

-- init vars:
SET @sql_into = 'INSERT INTO ' + @Target_Table + ' ('
SET @sql_from = 'SELECT '
SET @sql = ''

OPEN Table_Struct_Compare_Cursor

FETCH NEXT FROM Table_Struct_Compare_Cursor INTO
@ColName1,
@ColOrder1,
@ColType1,
@ColLength1,
@ColPrec1,
@ColScale1,
@IsNullable1,
@ColName2,
@ColOrder2,
@ColType2,
@ColLength2,
@ColPrec2,
@ColScale2,
@IsNullable2,
@OrderBy

WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColName1 IS NULL
BEGIN
IF @Show_Matchless_Columns = 'Y'
BEGIN
--SET @sql_into = @sql_into + @crlf + '--' + @indent + ','
SET @sql_into = @sql_into + @crlf + '--' + @indent + ','
SET @sql_from = @sql_from + @crlf + '--' + @indent + '<' + @Source_Table + '.[' + @ColName2 + ']>,'
END
END
ELSE IF @ColName2 IS NULL
BEGIN
IF @Show_Matchless_Columns = 'Y'
BEGIN
SET @sql_into = @sql_into + @crlf + '--' + @indent + '<' + @Target_Table + '.[' + @ColName1 + ']>,'
--SET @sql_from = @sql_from + @crlf + '--' + @indent + 'ColName1 + '>,'
SET @sql_from = @sql_from + @crlf + '--' + @indent + 'ColName1 + ']>,'
END
END
ELSE IF @ColType1 <> @ColType2
BEGIN
SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'
SET @sql_from = @sql_from + @crlf + @indent + 'CONVERT(' + '[' + @ColType1 + '], [' + @ColName2 + ']),'
END
ELSE
BEGIN
SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'
SET @sql_from = @sql_from + @crlf + @indent + '[' + @ColName2 + '],'
END

FETCH NEXT FROM Table_Struct_Compare_Cursor INTO
@ColName1,
@ColOrder1,
@ColType1,
@ColLength1,
@ColPrec1,
@ColScale1,
@IsNullable1,
@ColName2,
@ColOrder2,
@ColType2,
@ColLength2,
@ColPrec2,
@ColScale2,
@IsNullable2,
@OrderBy
END

CLOSE Table_Struct_Compare_Cursor
DEALLOCATE Table_Struct_Compare_Cursor

SET @sql_into = LEFT(@sql_into, LEN(RTRIM(@sql_into)) - 1) + ')' + @crlfβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- remove trailing comma, add closing paren

SET @sql_from = LEFT(@sql_from, LEN(@sql_from) - 1) + @crlfβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ-- remove trailing comma
SET @sql_from = @sql_from + 'FROM ' + @FromPrefix + @Source_Table + ';'+ @crlf

PRINT 'select ''Started Populating: ' + @Target_Table + '''+ CONVERT(varchar,GETDATE(),108) ;'
IF @AddGo = 'Y'
PRINT 'GO '
IF @Print_Truncate = 'Y'
PRINT 'TRUNCATE TABLE ' + @Target_Table

--If the table does not have an identity column, then do not print the set identity_insert statement which would cause an error
if NOT exists (select 1 from sys.columns sc inner join sys.objects so on so.Object_Id = sc.Object_Id where sc.is_identity = 1 and so.Type = 'u' and so.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']',''))
set @Print_Identity_Insert_Stmts = 'N'

IF @Print_Identity_Insert_Stmts = 'Y'
PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' ON;'
PRINT @sql_into
PRINT @sql_from
IF @Print_Identity_Insert_Stmts = 'Y'
PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' OFF;'

PRINT ''
PRINT ''

This script could always come handy in times on urgent need ! Happy coding !

Posted in Database | Tagged | Leave a comment

Visual Studio 2012

That’s great news !

The Release Candidate of Visual Studio 2012, Team Foundation Server 2012, and .NET Framework 4.5 is now available.With it, you can create Metro style apps as well as desktop, web, phone, and game-console apps !

Download the release candidate here.

This one is packed with features ! The list goes on and on …

A snapshot of the features is in the image below :

What's New in Visual Studio 2012 RC

What’s New in Visual Studio 2012 RC

To know more about the features and start exploring .. check out the MSDN here.

Happy Development !

Posted in .Net, General | Tagged , | Leave a comment

Windows 8 Tools, SDK And Documentation

The Windows 8 release preview is out!

You could watch for the new apps including Bing Travel plus gaming and Music Xbox apps that integrate with Zune passes.

Microsoft is already rolling out the Windows Upgrade Offer in 131 markets, which will give consumers easy options to help them upgrade to Windows 8 when it becomes generally available.

You couldΒ  download the tools and SDK or explore the documentation.

Posted in General | Tagged | Leave a comment

Filetable – SQL Server 2012

With the new SQL server 2012 , Microsoft has packed a whole new bunch of features ! One among them is the ‘FileTable’ . The name it self suggest it all ! As per MSDN :

SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

But there are some PER-REQUISITES for using this FileTable :

  • FILESTREAM should be enabled at the Instance level
  • FILESTREAM FILEGROUP should be provided at the database level
  • Enable Nontransaction access at the database level
  • Specify directory for FileTable at the database level

Below mentioned steps would enable you to start using FILETABLE:

Now,Β  enable FILESTREAM on the instance level. Execute the following command and see if the FILESTREAM feature is already enabled.

select value,value_in_use from sys.configurations where name like 'filestream access level'

If the value is 0, then FILESTREAM support is disabled for that instance. If the value is 1 then FILESTREAM is available for TSQL access. If the value is 2, then FILESTREAM access is enabled both for Transact SQL access and Win32 streaming access.

Create folder E:\MyFiles.

Now we need to enable the next three per-requisites, its easy to do it in a script instead of creating a database and altering the properties. So, let us create the database with FILESTREAM File group and enable it in Non Transaction level. Also we could provide the folder location for the FILESTREAM folder.

/****** Object:  Database [MyDB]    Script Date: 5/23/2012 4:25:56 PM ******/
CREATE DATABASE [MyDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MyDB_Data', FILENAME = N'E:\Data\MyDB_Data.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [MyFilestreamFG] CONTAINS FILESTREAM  DEFAULT
( NAME = N'MyFileStreamData', FILENAME = N'e:\myfiles\Data' , MAXSIZE = UNLIMITED)
 LOG ON
( NAME = N'MyDB_Log', FILENAME = N'E:\Data\MyDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

Now, we shall create the table as Filetable,Β  by using the following Transact SQL statement.

Use MyDB
go

CREATE TABLE MyDataFiles AS FileTable
WITH
(
    FileTable_Directory = 'MyDataFiles',
    FileTable_Collate_Filename = database_default
);
GO

This will cretae a folder MyDataFIles under the default filestream location :

\\hostname\sqlinstance\FileTable\MyDataFiles

You can query the filestream options defined for every database using the following TSQL query.

select db_name(Database_id) as DBName,* from sys.database_filestream_options

These are the things you could experiment with initially :

Now let us try to query the table that we created as File Table, using the following query.

Use MyDB
go
select * from MyDataFiles

Let’s copy some files to the MyDataFiles folder under the FILESTREAM share. (* the path i had specified above)

Now let’s try to query the table that we created as File Table using the following query.

select stream_id, name,file_type, last_access_timeΒ  from MyDataFiles

This query will list all the files that you have copied to the folder. If you want to update these physical files, you could do even that. For instance renaming of a file could be done like this :

UPDATE
MyDataFiles SET
name = 'RenameTextFile.txt' WHERE
stream_id = '4F464AB6-18A5-E111-BDE0-00505694001D'

If youΒ  delete a row from the table, it will delete the actual physical file as well. Execute the following TSQL command and check it out:

delete MyDataFiles where name='data.csv'

Thus an entire new concept / feature has been introduced in SQL Server 2012 to deal with files and directories.

You could explore more from MSDN articles here. Happy exploring !

Posted in Database | Tagged , | Leave a comment

Understanding Sequences and their use – SQL Server 2012

As per MSDN ,

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

A sequence can be created using the CREATE SEQUENCEΒ  statement.

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [  ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

An example could be found on these statements here in an older post.

When to use sequences :

  • The application requires a number before the insert into the table is made.
  • The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
  • The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
  • The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
  • An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
  • You need to change the specification of the sequence, such as the increment value.
Posted in Database | Tagged , | Leave a comment