January 9, 2009 at 10:25 am
We have a large number of tables with a large number of scripts used to populate them. We want to add some sort of DateTime column to record the load time of the data.
Most of our scripts load data via something like this:
insert into table1
select * from table2
where something = somethingelse
We want to add a column (call it LoadTime) as the last column of table1 with a default value of GETDATE(). But if we do that then the script above generates an error because nothing was specified for the LoadTime column.
Any suggestions as to how to make this work? I know I could do something like
insert into table1
select *, GETDATE() from table2
where something = something else
but we REALLY don't want to rewrite all the scripts. We were hoping that by adding the default it would just automatically populate that column without having to specify it.
Any suggestions would be appreciated.
- Cindy
January 9, 2009 at 10:38 am
January 9, 2009 at 10:53 am
January 9, 2009 at 11:10 am
We would certainly prefer to not use triggers, even if they worked.
I guess, if we have to rewrite the scripts we'll rewrite them. The two ways I've worked out involve either using GETDATE() as per my previous post or changing the sql to specify the column names. Of the two solutions by far the easiest is to just go around to each script and add ", GETDATE()" into the select.
But does anyone have any alternative suggestions?
January 9, 2009 at 11:13 am
January 9, 2009 at 11:14 am
This is a data warehouse database that feeds an analysis cube. The data loads are often millions of rows, and we don't want to use triggers.
January 9, 2009 at 11:21 am
Your error has nothing to do with the DEFAULT setting. It's more haing to do with the fact that your INSERT statements are now imbalanced. As in - the INSERT portion has N+1 elements, but only N elements are being supplied by the SELECT part of the query. Default or no - that will NEVER get past the parser.
You're going to have to rewrite the statements. As a point of reference - you really want to shy away from "lazy" INSERT statements where you do not specify the column names. They are not good practice, because they could easily lead to some rather ugly behavior if your columns don't happen to have been created in the same order on the 2 tables.
If you had that particular syntax in place already, you wouldn't have to rewrite anything right now. The DEFAULT value would operate the way you expect it to and fill in the value.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2009 at 11:30 am
I'm with Matt on this one. Implicit inserts (no list of column names) are easy, but they cause more problems in the long run than they're worth.
I've seen situations where they kept on "working" when the source table had one column removed and another one added, with different data, but where an implicit conversion still allowed that into the target table. So, in the target table, one day suddenly the column changed from having one thing in it to having another thing in it, and nobody even knew it. (The added column was datetime, the removed column was varchar.)
Imagine what happens if you remove the third column of a table, add another one at the end, and end up inserting everything one column to the left of where it's supposed to be! It can happen. The only way you might know is if there's an incompatible data type that raises an error!
And, since dragging the Columns tab onto the query editor gives you a complete list of the columns, with commas, in sequence, it really doesn't save you any significant time when you do the implicit insert.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 11:32 am
January 9, 2009 at 11:38 am
Alrighty, then.
Thanks for the help. Guess we'll use GETDATE().:crazy:
- Cindy
January 9, 2009 at 11:47 am
January 9, 2009 at 12:33 pm
All in good time.
Eventually we probably will move these into SSIS or, if that won't work for us, rewrite all the scripts to a higher coding standard. For now, given the large number of scripts and the very large number of columns in each table (many of these are fact tables) we don't want to do that right now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply