Dynamically Create tables Based on Config Table Data TSQL

I have a table MasterTable defined as follows:

[Table Name],[Field Name]

Both columns are of varchar type.

The first column list a table name and the second a column name. There is a one to many relationship between col 1 and col 2:

Table 1 - Column 1

Table 1 - Column 2

Table 1 - Column 3

Table 2 - Column 1

Table 3 - Column 1

Table 3 - Column 2

For a given table we can have many columns.

The table MasterTable  can have multiple one or more values in the [Table Name] field as well as one or more associated columns in the [Field Name] field.

I need to be able to dynamically create a copy of  tables and associated columns based on the data available in the MaterTable table and make .

Here is the script for dynamically create a copy of  tables and associated columns based on the data available in the MaterTable table

USE [SQL2012]
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'tableconfig'
  )
BEGIN
 DROP TABLE tableconfig;
END
GO

CREATE TABLE TableConfig (
 TableName VARCHAR(100)
 ,ColumnName VARCHAR(100)
 ,ColumnDataType VARCHAR(100)
 )
GO

INSERT INTO TableConfig
VALUES (
 'Table 1'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 1'
 ,'Column 2'
 ,'Varchar(100)'
 )
 ,(
 'Table 1'
 ,'Column 3'
 ,'Varchar(100)'
 )
 ,(
 'Table 2'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 3'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 3'
 ,'Column 2'
 ,'Varchar(100)'
 )
GO

/*SELECT * FROM TableConfig
GO
*/
DECLARE @sqlquery NVARCHAR(4000) = ''
DECLARE @startRow INT = 1;
DECLARE @endRow INT = 1;
DECLARE @startcolumn INT = 1;
DECLARE @endcolumn INT = 1;
DECLARE @tableToCreate VARCHAR(100) = '';
DECLARE @columnName VARCHAR(100) = '';
DECLARE @DataType VARCHAR(100) = '';

WITH MyTables
AS (
 SELECT DISTINCT TableName
 FROM TableConfig
 )
SELECT @endRow = count(*)
FROM MyTables;

/*PRINT @endRow*/
WHILE (@startRow <= @endRow)
BEGIN
 WITH AllTables
 AS (
  SELECT ROW_NUMBER() OVER (
    ORDER BY tablename
    ) AS Rownum
   ,TableName
  FROM TableConfig
  GROUP BY TableName
  )
 SELECT @tableToCreate = TableName
 FROM Alltables
 WHERE Rownum = @startRow

 SET @sqlquery = '';
 SET @sqlquery = @sqlquery + 'IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = ' + '''' + @tableToCreate + '''' + '
  )
 BEGIN
  DROP TABLE ' + '[' + @tableToCreate + ']' + ';' + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE TABLE ' + '[' + @tableToCreate + ']' + '('
 /* PRINT @tableToCreate;
 PRINT @endcolumn
*/
 SET @startcolumn = 1;

 SELECT @endcolumn = count(*)
 FROM TableConfig
 WHERE TableName = @tableToCreate;

 WHILE (@startcolumn <= @endcolumn)
 BEGIN
  WITH Allcolumns
  AS (
   SELECT ROW_NUMBER() OVER (
     ORDER BY columnname
     ) AS Rownum
    ,ColumnName
    ,ColumnDataType
   FROM TableConfig
   WHERE TableName = @tableToCreate
   )
  SELECT @columnName = ColumnName
   ,@DataType = ColumnDataType
  FROM Allcolumns
  WHERE Rownum = @startcolumn

  /* PRINT @columnName
  PRINT @DataType
*/
  SET @sqlquery = @sqlquery + '' + '[' + @columnName + ']' + ' ' + @DataType

  IF @startcolumn < @endcolumn
   SET @sqlquery = @sqlquery + ',';
  SET @startcolumn = @startcolumn + 1;
 END

 /*New Line */
 SET @sqlquery = @sqlquery + ');' + CHAR(13) + CHAR(10) + 'GO';

 PRINT @sqlquery

 SET @startRow = @startRow + 1;
END

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'tableconfig'
  )
BEGIN
 DROP TABLE tableconfig;
END
GO

Output

------------------------

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 1'
  )
BEGIN
 DROP TABLE [Table 1];
END
GO

CREATE TABLE [Table 1] (
 [Column 1] VARCHAR(100)
 ,[Column 2] VARCHAR(100)
 ,[Column 3] VARCHAR(100)
 );
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 2'
  )
BEGIN
 DROP TABLE [Table 2];
END
GO

CREATE TABLE [Table 2] ([Column 1] VARCHAR(100));
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 3'
  )
BEGIN
 DROP TABLE [Table 3];
END
GO

CREATE TABLE [Table 3] (
 [Column 1] VARCHAR(100)
 ,[Column 2] VARCHAR(100)
 );
GO

--------------------------------

Thanks,

Prajesh

Any comments or feedback will be highly appreciated.

I have to write a SQL statement to create a trigger called "ValidOrder" checking the value of a new order inserted into the OrderDetails table is greated that $100. If the value is less that $100, the entry needs to be rolled back.

CREATE TABLE OrderDetails 

(

OrderId INT identity(1, 1), 

OrderName VARCHAR(50), 

OrderValue MONEY

)
GO

CREATE TRIGGER dbo.ValidOrder ON dbo.OrderDetails
AFTER INSERT, UPDATE
AS
BEGIN
 SET NOCOUNT ON;

 BEGIN TRANSACTION

 DECLARE @OrderValue FLOAT

 SELECT @OrderValue = OrderValue
 FROM inserted

 IF @OrderValue > 100
 BEGIN
  COMMIT TRANSACTION

  PRINT 'Record inserted'
 END
 ELSE
 BEGIN
  ROLLBACK TRANSACTION

  PRINT 'Record rolled back as order value is less than $100'
 END
END
GO

INSERT INTO OrderDetails (OrderName, OrderValue)
VALUES ('Order1', 55)

/*
Record rolled back as order value is less than $100
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
INSERT INTO OrderDetails (OrderName, OrderValue)
VALUES ('Order1', 500)
 /*
Record inserted

(1 row(s) affected)
*/

Transfer SQL Server Object Task in SSIS

The Transfer SQL Server Objects task transfers one or more types of database objects in a SQL Server database between instances of SQL Server

The Transfer SQL Server Objects task supports a SQL Server source and destination.

I will add the examples soon..

Transfer SQL Server Object Task

Get the Central European Time in SQL Server

Getting the Central European Time (http://en.wikipedia.org/wiki/Central_European_Time) is requirement some of the time when we build the Product for Clients spreaded in European Countries.

Here it the function I created to which return the same and it manage the day light saving internally.

CREATE FUNCTION ufs_Datetime_GetCETTime
RETURNS DATETIME
AS
BEGIN
 DECLARE @DstStart DATETIME
 DECLARE @DstEnd DATETIME
 DECLARE @CETDateTime DATETIME
 DECLARE @UTCDateTime DATETIME

 SET @UTCDateTime = GETUTCDATE()

 SELECT @DstStart = DATEADD(hour, 1, DATEADD(day, DATEDIFF(day, 0, '31/Mar' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) - (DATEDIFF(day, 6, '31/Mar' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) % 7), 0))
  ,@DstEnd = DATEADD(hour, 1, DATEADD(day, DATEDIFF(day, 0, '31/Oct' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) - (DATEDIFF(day, 6, '31/Oct' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) % 7), 0))

 SELECT @CETDateTime = CASE 
   WHEN @UTCD ateTime & lt;= @DstEnd
    AND @UTCDateTime & gt;= @DstStart
    THEN DATEADD(hour, + 2, @UTCDateTime)
   ELSE DATEADD(hour, + 1, @UTCDateTime)
   END

 RETURN @CETDateTime
END

Comments awaited.. Thanks, Prajesh

Column Length Distribution profile in SSIS Profiler

How does Column Length Distribution profile in SSIS Profiler work in SSIS ? Column Length Distribution profile get the length of all the columns to be profiled, and get the max and min out of it.
 E.g the below example

WITH cte
AS (
 SELECT LEN(NULL) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(100) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(101) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(102) AS [Column Length]
 
 UNION ALL
 
 SELECT LEN(103) AS [Column Length]
 )
SELECT max([Column Length]) AS [Max Column Length]
 ,MIN([Column Length]) AS [MIN Column Length]
FROM cte
OUTPUT OF the above query will be [Max Column Length] = 3
 ,[MIN Column Length] = 3

 So if the column value in your table is NULL, the lenght for NULL is always NULL.

Compress the Large XML Files using 7zip C# API

Hi All, Compressing the files really helps if the file size is Huge (>1 GB) and compression ration is high. 7zip C# code API helps to achieve the target

You need 2 external files to create a project/solution to zip files using this SevenZip C# Code SevenZipSharp.dll ,
get this from http://sevenzipsharp.codeplex.com/ the above file is a .net assembly and cab be directly referenced in C# code 7-zip.dll, get it from http://www.7-zip.org/ 

This is not a .net assembly but a COM Use the below code to build the solution, this code is compressing around 6.5 GB files to 1 MB in 2 minutes.

Code:

class Program { static void Main(string[] args) { Console.WriteLine("Enter the 7zip dll location :"); string zipfilenametozip = Console.ReadLine(); SevenZip.SevenZipBase.SetLibraryPath(zipfilenametozip); MemoryStream ms = new MemoryStream(); MemoryStream compressedStream = new MemoryStream(); SevenZipSevenZipCompressor compressor = new SevenZipSevenZipCompressor(); compressor.CompressionMethod = SevenZip.CompressionMethod.Lzma2; compressor.CompressionLevel = SevenZip.CompressionLevel.Low; compressor.CompressStream(ms, compressedStream); compressedStream.Position = 0; Console.WriteLine("Enter the uncompressed File Name with location to Compress (Zip) :"); string filenametozip = Console.ReadLine(); Console.WriteLine("Enter the File Name with location for the Compressed zip File :"); string filenamezipped = Console.ReadLine(); Console.WriteLine("Zipping started for file Name : " + filenametozip + " at " + DateTime.Now.ToString()); string[] myfiles = { filenametozip }; compressor.CompressFiles(filenamezipped, myfiles); Console.WriteLine("Zipping completed : " + filenamezipped + " at " + DateTime.Now.ToString()); } } 

Please comment if you face any issues in using this code... Thanks, Prajesh Jha

Bulk update of image data in MSSQL

Someone asked me how to update a column in MSSQL table with bulk insertion of compressed images data from a folder. How to achieve it and here is detailed code and steps to achieve the same

 /* create a folder C:\Images\ with all the compressed images */

USE GO

CREATE TABLE dbo.tblImages (
 id INT identity(1, 1) PRIMARY KEY
 ,FlowerName VARCHAR(100) NULL
 ,FlowerImage IMAGE
 ) GO

INSERT INTO dbo.tblImages (
 FlowerName
 ,FlowerImage
 )
SELECT 'Flower1'
 ,BulkColumn
FROM Openrowset(BULK 'C:\Images\1.jpg', Single_Blob) AS img GO

INSERT INTO dbo.tblImages (
 FlowerName
 ,FlowerImage
 )
SELECT 'Flower2'
 ,BulkColumn
FROM Openrowset(BULK 'C:\Images\2.jpg', Single_Blob) AS img GO

INSERT INTO dbo.tblImages (
 FlowerName
 ,FlowerImage
 )
SELECT 'Flower3'
 ,BulkColumn
FROM Openrowset(BULK 'C:\Images\2.jpg', Single_Blob) AS img GO

You can use a loop to do this automatically in SSIS or other ETL tool or Stored Proc. 

Also you can use some free SQL Server addins like SSMSBoost add-in - productivity tools for SSMS 2008 / 2012 (Sql Server Management Studio)and that will help you to see the image stored as binary in SSMS itself just to test 

http://www.ssmsboost.com/Content/images/Feature/ssms-add-in-results-grid-visualizers.png 

Any question Let me know

Microsoft ODBC Driver for SQL Server on Linux

The SQL Server ODBC driver enables you to access SQL Server from applications running on Linux and UNIX platforms

Below link can help you to download the Microsoft ODBC Driver for SQL Server on Linux

http://www.easysoft.com/products/data_access/odbc-sql-server-driver/index.html