CODEWITHSUNDEEP
sql-server-2008sqldatatypes
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

How to verify datatype before entering into the table

I have four columns in a table

Id Int
Name varchar(2)
Address varchar (4)
Active bit

and the source table with the same columns but have the varchar data type.

Id varchar(100)
Name varchar(100)
Address varchar (100)
Active varchar(100)

I have to transfer data from source table to destination table, but while transfering I need to check if the row I have has the correct data type for the destination. If not I need to transfer that complete row to some error table. ex.:

ID Name Address Active
1  A     A       1
C  B     B       0
3  AAA   C       1 
4  D     D       0
5  K     K       102

if above represnt the source table and only rows 1 and 4 are eligible to tranfer to the destination table other rows will be moved to error table (may be with valid description, if possible)

Upvotes: 1

Views: 3237

Answers (1)

valex
valex

Reputation: 24144

Something like this 

insert into destination
select * from source 
where (isnumeric(ID)=1 
        and 
        (ID not like '%[^0-9]%')
        and 
        RIGHT('00000000000'+ID,10) <= '2147483647'
      ) 
      and
      len(name)<=2
      and
      len(Address)<=4
      and
      active in ('0','1')

So to insert into ERRORS table use NOT in WHERE 

insert into ERRORS
select * from source 
where 
   NOT
     (
      (isnumeric(ID)=1 
        and 
        (ID not like '%[^0-9]%')
        and 
        RIGHT('00000000000'+ID,10) <= '2147483647'
      ) 
      and
      len(name)<=2
      and
      len(Address)<=4
      and
      active in ('0','1')
    )

SQLFiddle demo

Upvotes: 3

Related Questions