quote:

---

Originally posted by russell

this is the wrong approach altogether. strong type everything. if the problem is disparate data sources, create a schema for clients to conform to...and publish it and enforce it

---

quote:

---

CREATE PROCEDURE sp_GetColumnType
@ColumnName varchar(50),
@Table varchar(50),
@Type varchar(10) OUTPUT
WITH RECOMPILE
AS

DECLARE @SQL varchar(max)

SET @SQL =
'CREATE PROCEDURE #sp_GetColType
@Result varchar(10) OUTPUT
AS
BEGIN TRY
SELECT CAST('+ @ColumnName +' AS datetime) FROM '+ @Table + '
SELECT @Result = ''DateTime''
END TRY
BEGIN CATCH
BEGIN TRY
SELECT CAST('+ @ColumnName +' AS int) FROM '+ @Table + '
SELECT @Result = ''Int''
END TRY
BEGIN CATCH
BEGIN TRY
SELECT CAST('+ @ColumnName +' AS float) FROM ' + @Table + '
SELECT @Result = ''Float''
END TRY
BEGIN CATCH
SELECT @Result = ''VarChar''
END CATCH
END CATCH
END CATCH
RETURN'

SET NOCOUNT ON
EXEC(@SQL)

EXEC #sp_GetColType @Result = @Type OUTPUT

DROP PROC #sp_GetColType

SET NOCOUNT OFF

RETURN

---

select convert(datetime,mycol1) as MyDateCol1, convert(int,mycol2) as MyIntCol2,...
into MYNewSqlTable
from
....

quote:

---

Originally posted by TG

I think you guys are missing the question. Correct me if I'm wrong, malachi151, but you will eventually transform all this data to strongly typed permanent tables, right? But you want to develop a process that will "discover" what those types should be based on the data in staging tables? I think it makes sense actually to stage the data into non-typed tables so that the import is very likely to succeed. Deal with dupes, referential integrity, business rules (and even types) after you have the data in sql. The alternative would be to perform those functions prior to getting the data in sql using a different technology.

Be One with the Optimizer
TG

---

quote:

---

Originally posted by TG

couple observations:

What you've just described sounds different than what the SP posted above does. Sounds like you want to check the data (column by column, table by table, row by row) for any values that either won't convert to the target type or where you may loose some precision, or be truncated. so if your target type was say decimal(10,2) but one of your values was 2.345 then you want to see that value to decide if you need to change your precision or just allow it to be rounded to 2 decimals.

Where as the SP above would just return it's best guess at a datatype based on all the values in the table. Then if your resulting type was different than the target table type you would investigate further? As written, that SP's output will only be based off of the last value it encountered. In other words in a 5 million row table, @result will be set 5 million times and only the result from the last value evaluated will be returned - so that doesn't sound like a good solution.

Be One with the Optimizer
TG

---

create table junk (i varchar(500))

insert junk
select '2' union all
select '2' union all
select '2' union all
select '2' union all
select '2009-12-31' union all
select '2' union all
select '2' 


declare @t varchar(10)
exec sp_GetColumnType
@ColumnName = 'i',
@Table = 'junk',
@Type = @t OUTPUT
select @t

drop table junk

OUTPUT:
-----------------------


-----------
2
2
2
2


----------------------
2
2
2
2


----------
VarChar

[<TableName>]
ColumnName     datetimeCount IntCount      moneyCount    varcharCount
Col1                      0         0         500000               10
Col2                 500010         0              0                0
Col2                      0         0              0           500010

use tempdb
if object_id('sp_GetColumnType') > 0
       drop proc sp_GetColumnType
go
CREATE PROCEDURE sp_GetColumnType
       @ColumnName varchar(50),
       @Table varchar(50),
       @Type varchar(10) OUTPUT
AS

DECLARE @SQL nvarchar(max)
       ,@p nvarchar(max)
       ,@Result varchar(10)

BEGIN TRY
       set @sql = N'select @result = ''datetime'' from ' + @table + ' where convert(datetime, ' + @columnName + ') = convert(datetime, ' + @columnName + ')'
       set @p = N'@result varchar(10) output'
       exec sp_executesql @sql, @p, @result = @result output
END TRY
BEGIN CATCH
       BEGIN TRY
              set @sql = N'select @result = ''int'' from ' + @table + ' where convert(int, ' + @columnName + ') = convert(int, ' + @columnName + ')'
              set @p = N'@result varchar(10) output'
              exec sp_executesql @sql, @p, @result = @result output
       END TRY
       BEGIN CATCH
              BEGIN TRY
                     set @sql = N'select @result = ''float'' from ' + @table + ' where convert(float, ' + @columnName + ') = convert(float, ' + @columnName + ')'
                     set @p = N'@result varchar(10) output'
                     exec sp_executesql @sql, @p, @result = @result output
              END TRY
              BEGIN CATCH
                     set @result = 'varchar'
              END CATCH
       END CATCH
END CATCH

set @type = @result

go

create table junk (i varchar(500))
set nocount on
insert junk
select '2' union all
select '2' union all
select '2' union all
select '2' union all
select '2009-12-31' union all
select '2' union all
select '2' 
set nocount off

declare @t varchar(10)
exec sp_GetColumnType
@ColumnName = 'i',
@Table = 'junk',
@Type = @t OUTPUT
select @t [Type]

drop table junk
drop proc sp_GetColumnType

OUTPUT:
Type
----------
varchar

create table junk (i varchar(500))
set nocount on
insert junk
select '2' union all
select '2' union all
select '2' union all
select '2' union all
select '2009-12-31' union all
select '2' union all
select '2' 
set nocount off

go
set nocount on
declare @table varchar(50)
set @table = 'junk'

create table #t (columnName varchar(50), datetimecount int, intcount int, varcharCount int)

declare @col varchar(50)
       ,@i int
       ,@Count int
       ,@sql  nvarchar(2000)
       ,@p    nvarchar(2000)

select @i = min(ordinal_position) from information_schema.columns where table_name = @table
while @i is not null
begin
       select @col = column_name from  information_schema.columns where table_name = @table and ordinal_position = @i

       -----------------------------------------------------------------------------------------------------
       --get datetime count       
       set @sql = N'select @c = count(*) from ' + @table + ' where isDate([' + @col + ']) = 1'
       set @p = N'@c int output'
       exec sp_executesql @sql, @p, @c = @count output
       
       insert #t (ColumnName, datetimecount)
       values (@col, @count)

       -----------------------------------------------------------------------------------------------------
       --get int count
       select @sql = N'select @c = count(*) from ' + @table + ' where [' + @col + '] not like ''%[^0-9]%'''
              ,@p = N'@c int output'
       exec sp_executesql @sql, @p, @c = @count output

       update #t set intcount = @count where columnName = @col

       -----------------------------------------------------------------------------------------------------
       --get varchar count
       select @sql = N'select @c = count(*) from ' + @table + ' where [' + @col + '] like ''%[a-z]%'''
              ,@p = N'@c int output'
       exec sp_executesql @sql, @p, @c = @count output

       update #t set varcharcount = @count where columnName = @col
       -----------------------------------------------------------------------------------------------------

       select @i = min(ordinal_position) from information_schema.columns where table_name = @table and ordinal_position > @i
end

select @table [Table]
select * from #t
drop table #t

go
drop table junk

OUTPUT:
Table
--------------------------------------------------
junk

columnName                                         datetimecount intcount    varcharCount
-------------------------------------------------- ------------- ----------- ------------
i                                                  1             6           0