Set all values in new record to default value to avoid NULL

Terrace said:
>>I have a table with over 200 fields.

Which often is indicative of design trouble :)

Why so many columns?

Could be a denormalized reporting table!

BrandonGalderisi said:
>>Could be a denormalized reporting table!

Could be, and if so I shall be silent :)

In your design table u need to put default value to all fields u need to have default value and then uncheck "allow nulls" so that will force SQL to insert your default value if u not send any.

So for your decimal field u put default value 0, for your string fields put "", for date fields u can put getdate() sa default field, but u must uncheck allow nulls for all of them to work.

i forgot to mention other way to achive this ... if u wont remove allow nulls checkbox then in your query when u insertin should be like this

insert into your_table
(stringField1, stringField2, decimaField1, dateField1, dateField2 ....)
values
('test', default, default, '2008-01-01', default .... )

so putting default into values will insert your default value u put when u designed your table.

It is not NECESSARY to uncheck allow nulls in order to force a NULL value to be used.  As I stated earlier, proven by the below example, setting the field to not allow null will NOT force the default to be used if NULL is specified.  Yes, you can use default in the insert list, or not specify the column in the field list or insert list.

set nocount on
create table #a (i int identity,a int null default 1, b int default 0 not null)
go
insert into #a(a,b) values(1,0)
insert into #a(a,b) values(1,null)
insert into #a(b) values(0)
insert into #a(a) values(0)
insert into #a(a) values(default)
go
select * From #a
drop table #a

Select allOpen in new window

Brandon,
sry but i think u are wrong.

Your column a doesnt have not null values set and in your example u didnt try to insert null in that column so try adding this to your code

insert into #a(a) values(null)

and u will get null value in your table wich Terrence doesnt want to. So forceing not allow nulls u cannot insert null value, and your example show that inserting <null> in column a will work.

Cvijo123:
You are entitled to your opinion, but if you were to run the sample code I provided to prove just this point you would realize that I am 100% correct.

When running the above query, attempting to insert 1,null into a,b (the second insert) the following error is generated:

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.#a__________________________________________________________________________________________________________________00000000005E'; column does not allow nulls. INSERT fails.


The result when adding the following: insert into #a(a) values(null)

1      1      0
3      1      0
4      0      0
5      1      0
6      NULL      0

NULL is inserted and NOT the default.

Perhaps before you pass judgement and question a comment added by someone, you should run the test code that they provide.

Sry Bandon was my mistake,

I did run your code but on APEX SQL Edit clinet tool not on Query Analyzer so i didnt see error since Apex didnt raise any and when i posted in QA it show error.

And i didnt judge, my post starts with "i think".

"Your column a doesnt have not null values set and in your example u didnt try to insert null in that column so try adding this to your code"

But if you looked at it, I WAS doing that with column B.

Thank you all for your time and help.
I'll play with your suggestions now.

Currently my fields in table all have Allow Null checked, but don't have any default values set. That's why if nothing is declared to be inserted into those 190 fields, they all get NULL value.

I was trying to see if there is any approach where I don't need to Uncheck Allow Null and set default value for each field, to fix this Null apperance issue.

Will read your answers more carefully and play with your suggestions right now.
Thanks

the best thing to do is add the default and ensure that the field is not specified in the insert list.

you will have to manually update all columns after adding the default if you do not intend on making it non-nullable.

trying to get the result with as least changes as possible.

As I said, my columns/fields in table as various data types:
char, numeric (decimal), date, bit, etc

Is there a way to do this:
 1. leave all columns with Allow Null checked
 2. leave all columns without default value set (like it is now)

an then create a query that will somehow based on data type of columns/fields, add some basic/common default values. (not NULL) when new row/record is added.

For example, if I use this query that is creating new record and inserting value for only 1 field:

sb.Append("INSERT INTO tblTest")
        sb.Append("(loan_no)")
        sb.Append("VALUES(@loan_no)")

command.Parameters.Add("@loan_no", SqlDbType.NVarChar).Value = txtLoanNo.Text

is there a way to set the rest of 200+ fields to some basic database default value based on data type of those fields. (again without any other changes, mentioned in 1. and 2.)

Or I definetely must set default values to each field manually in design table(once) and then just ensure that those fields keep being not specified in Insert list?

Trying to see if I have any other option.

Also, if a record is already added to the table where 10 fields have certain values, and other 190 are NULLs - can I run some query now to set those NULLs to some basic defaults? (I guess same answer from above will apply here)

Thanks

"trying to get the result with as least changes as possible."


I don't believe I am proposing this :(

This code will generate a trigger to update your columns to a default if null on insert.  There may need to be some tweaks because it's sql 2000, but it should be as good as it gets with no changes.good

drop table abc
go
 
create table abc
     (cola          int identity primary key clustered
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,wearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,bcuzwearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,whywearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,whybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,thisisgettingridiculouswhywearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol2          int not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol3          varchar(20) not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol4          datetime not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol5          int null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol6          datetime null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol7          varchar(20) null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol8          int not null default 0
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol9          datetime not null default getdate()
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol10         varchar(20) not null default ''
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol12          int not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol13          varchar(20) not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol14          datetime not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol15          int null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol16          datetime null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol17          varchar(20) null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol18          int not null default 0
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol19          datetime not null default getdate()
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol110         varchar(20) not null default ''
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol112          int not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol113          varchar(20) not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol114          datetime not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol115          int null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol116          datetime null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol117          varchar(20) null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol118          int not null default 0
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol119          datetime not null default getdate()
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1110         varchar(20) not null default ''
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1112          int not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1113          varchar(20) not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1114          datetime not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1115          int null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1116          datetime null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1117          varchar(20) null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1118          int not null default 0
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol1119          datetime not null default getdate()
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11110         varchar(20) not null default ''
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11112          int not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11113          varchar(20) not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11114          datetime not null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11115          int null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11116          datetime null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11117          varchar(20) null
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11118          int not null default 0
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol11119          datetime not null default getdate()
     ,thisisgettingridiculouswhybcuzwearegeneratinglongcolumnnamestofullthevariablesupcol111110         varchar(20) not null default ''
 
 
     )
 
go
declare    @BaseSQL      varchar(8000)
          ,@EndSQL       varchar(8000)
          ,@joinSQL      varchar(8000)
          ,@SQL1         varchar(8000)
          ,@SQL2         varchar(8000)
          ,@SQL3         varchar(8000)
          ,@SQL4         varchar(8000)
          ,@SQL5         varchar(8000)
          ,@SQL6         varchar(8000)
          ,@SQL7         varchar(8000)
          ,@tablename    sysname
 
set @tableName='abc'
set @BaseSQL = 'create trigger [ins_' + @tablename + '] on [' + @tablename + ']
for insert as
if @@rowcount=0 
   return 
update b
set ' 
set @SQL1 =''
set @SQL2 =''
set @SQL3 =''
set @SQL4 =''
set @SQL5 =''
set @SQL6 ='' 
set @SQL7 =''
 
select @joinSQL = isnull(@joinSQL + ' and ','') + 'b.[' + c.column_name + '] = i.[' + c.column_name + ']'
from information_schema.columns c
  join sys.syscolumns sc
    on c.column_name = sc.[name]
    and sc.id = object_id(@tablename)
    and colid in (select colid from sys.sysindexkeys where id = object_id(@tablename))
 
set @EndSQL = 'from inserted i join [' + @tablename + '] b on '
 
declare @column_name sysname, @data_type sysname
declare ColCur cursor for
select column_name,data_type from information_schema.columns
where table_name = @tablename
and is_nullable='yes'
and column_default is null
open colcur
fetch next from colcur into @column_name, @data_type
while @@fetch_Status=0
begin
 
          if len(@SQL1)<3000
          SET @SQL1 = @SQL1 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
     else if len(@SQL2)<3000
          SET @SQL2 = @SQL2 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
     else if len(@SQL3)<3000
          SET @SQL3 = @SQL3 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
     else if len(@SQL4)<3000
          SET @SQL4 = @SQL4 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
     else if len(@SQL5)<3000
          SET @SQL5 = @SQL5 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
     else if len(@SQL6)<3000
          SET @SQL6 = @SQL6 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
     else if len(@SQL7)<3000
          SET @SQL7 = @SQL7 + 
               '[' + @column_name + ']=isnull(b.[' + @column_name + '],' 
               + case when right(@data_type,4)='char'            then '''''' 
                      when right(@data_type,3)='int'             then '0' 
                      when right(@data_type,8)='datetime'        then 'getdate()' 
                      when @data_type in ('decimal','numeric')   then '0'
                      when @data_type = 'bit'                    then '0'
                 end + ')'+char(13)+','
 
 
fetch next from colcur into @column_name, @data_type
end
close colcur
deallocate colcur
 
     if len(@SQL7)>0  set @SQL7 = left(@SQL7,len(@SQL7)-1)
else if len(@SQL6)>0  set @SQL6 = left(@SQL6,len(@SQL6)-1)
else if len(@SQL5)>0  set @SQL5 = left(@SQL5,len(@SQL5)-1)
else if len(@SQL4)>0  set @SQL4 = left(@SQL4,len(@SQL4)-1)
else if len(@SQL3)>0  set @SQL3 = left(@SQL3,len(@SQL3)-1)
else if len(@SQL2)>0  set @SQL2 = left(@SQL2,len(@SQL2)-1)
else if len(@SQL1)>0  set @SQL1 = left(@SQL1,len(@SQL1)-1)
 
 
exec(@baseSQL + @SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQL5 + @SQL6 + @SQL7 + @EndSQL + @joinSQL)

Select allOpen in new window