Convert SQL types in destination to SQL type in source

Another little script I needed for today. Feel free to expand on the CASE statement for your own types (which I will do should I need to run this again).



/*
 Will transform destination table's column types to match the source database's  
 (when column names match)
 codesnippet:83d07e82-3866-4877-849f-0ae06322b9fb
*/


declare @destination varchar(50)
declare @source varchar(50)

---- Configuration -----
set @destination = 'PlanImport'
set @source = 'Plan'
------------------------

declare @i int
declare @count int
declare @sql varchar(8000)
declare @columnName varchar(8000)
declare @columnType varchar(8000)
declare @columns table (id int identity(1,1), columnName varchar(50), columnType varchar(50) )

-- insert source columns that are in destination
insert into @columns
select source.[Name], 
 (CASE source_types.[Name]
 WHEN 'decimal' THEN
  source_types.[Name] + '(' + cast(source.[precision] as varchar(50)) + ',' + cast(source.[scale] as varchar(50)) + ')' 
 WHEN 'int' THEN
  source_types.[Name]
 WHEN 'varchar' THEN
  source_types.[Name] + '(' + cast(source.[max_length] as varchar(50)) + ')' 
 ELSE 
  source_types.[Name]
 END) as [Name]
from sys.columns as source
join sys.columns as destination on destination.[name] = source.[name]
join sys.types source_types on source_types.system_type_id = source.system_type_id
where source.object_id = object_id(@source) and destination.object_id = object_id(@destination) 

set @i = 1
set @count = (select max(id) from @columns)

while(@i <= @count)
begin
 set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i)
 set @columnType = (SELECT [columnType] FROM @columns WHERE [ID] = @i)
 
 --select @columnName
 set @sql = 'ALTER TABLE ' + @destination + ' ALTER COLUMN ' + @columnName + ' ' + @columnType

 execute(@sql)

 set @i = @i + 1
end

select 'Done!'


Comments

Post a Comment

Popular posts from this blog

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

I have to share this. I spent a good amount of time searching around Fluent NHibernate for a way to escape columns in the generated SQL while still using AutoMapping. My initial configuration looked like: Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005.ConnectionString( c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString))) .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf () .WithSetup(convention => { convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id"; convention.GetComponentColumnPrefix = type => type.Name + "Id"; } ) .BuildSessionFactory() This works great, but I had a property in my class (SomeType) that is called Group. This was causing the SqlException: Incorrect syntax near the keyword 'Group' (others would be like: Incorrect syntax near the keyword 'User' or Incorrect syntax near the keyword ...
Read more

Fluent NHibernate + Encrypting Values

I recently created two CLR User Defined Functions for SQL Server 2005 (written in C#) to decrypt & encrypt a given string (based on a hard-coded key). select dbo.EncryptString('Testing') -- Returns: Fjfds243qcm43fsdj2343== select dbo.DecryptString('Fjfds243qcm43fsdj2343==') -- Returns: Testing Pretty simple stuff. This will allow the user with enough access to pull encrypted data out of the database and provide the ability to restrict decryption to other SQL users. Now to implement this in code... Because it's awesome, Fluent NHibernate has a method called FormulasIs that assists in building out values using SQL. public class User { public virtual int Id { get; set; } public virtual DateTime DateOfBirth { get; set; } } public sealed class UserMap : ClassMap { public UserMap() { WithTable("dbo.[User]"); Id(x => x.Id, "[ID]"); Map(x => x.DateOfBirth, "DOB").FormulaIs("dbo.DecryptString(DOB)"); ...
Read more

MapReduce in C# using Task Parallel Library

Back in August I starting playing with a C# implementation of Google's MapReduce algorithm. The implementation was based on something Stephan Brenner did, although I completely refactored it. Today I added a little bit of logic to split up the actual execution of Map & Reduce in this implementation using the Task Parallel Library in .NET 4.0. Check out the source code for MapReduce in C# on GitHub. Below is an excerpt from the Tests on how to implement the library. Counting Words in Files public static List<KeyValuePair<string, int>> Map(FileInfo document, string text) { var items = text.Split('\n', ' ', '.', ',','\r'); return items.Select(item => new KeyValuePair<string, int>(item, 1)).ToList(); } public static List<int> Reduce(string word, List<int> wordCounts) { if (wordCounts == null) return null; var result = new List<int> { 0 }; foreach (var value in wordC...
Read more