Solved

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

Posted on 2008-10-07
15
262 Views
Last Modified: 2012-05-05
I have a table with over 200 fields.
Fields are characters, decimals, bits, dates, etc

When I am adding new record to a table, I am inserting data for 10 fields. Other 190 fields get NULL value after new record is added/saved.

I wanted to set other 190 fields to their defualt blank values.
for example: if data is character I want to to get empty string value " ".
all decimals to get 0 value
all bits to get 0 value
dates to be blank dates, etc

What is a query / code that I can use to achieve this?

Thanks
0
Comment
Question by:Terrace
  • 7
  • 4
  • 2
  • +1
15 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22663898
Do you have default constraints specified on those columns?  

If so, then not including them in your insert field list will force the default to be used.  If you specify the field and a value (NULL is thought of as a value in this case), then it will be used.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22663933
Terrace said:
>>I have a table with over 200 fields.

Which often is indicative of design trouble :)

Why so many columns?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22664021
Could be a denormalized reporting table!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22664051
BrandonGalderisi said:
>>Could be a denormalized reporting table!

Could be, and if so I shall be silent :)
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22667126
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.
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22667139
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22668746
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

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:Cvijo123
ID: 22668822
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22668942
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.
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22669077
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".
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22670110
"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.
0
 

Author Comment

by:Terrace
ID: 22670347
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671030
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.
0
 

Author Comment

by:Terrace
ID: 22674065
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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22674372
"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)

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now