February 27, 2011 at 11:48 pm
Hi Guys,
I have a table with no Primary Key. I have a column that is null. But I want to set it to 'NR' after it has been inserted. I've only seen examples doing this on tables with a Primary Key. How can I achieve this with my table. please help
February 28, 2011 at 9:10 pm
Obviously, you're going to get a whole lot of people that will tell you that you absolutely must have some way to uniquely identify each row, PK or not.
That notwithstanding, you can add a default to the column in question.
You really do need to find some way to uniquely identify each row, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2011 at 10:54 pm
Hi Jeff,
thanks for your help. The reason I don't have a primary key is because I just dump data from another database into ours so we can then import that data. I guess I will add the default value for now. 😉
March 1, 2011 at 6:41 am
Understood and thanks for the feedback.
Does the original DB have a PK or unique index on the data? If so, you could always target that column or columns as a "uniquifier" moving forward for other processes.
As a side bar, if it is just a "data dump" as you suggest, a default may not actually work because the data being moved does have a value assigned to it even if that value is NULL. Defaults only work when the column isn't actually inserted to.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2011 at 1:15 am
hi again,
even the original db has no pk:( what other options do I have?
March 4, 2011 at 3:17 pm
niteshrajgopal (3/2/2011)
hi again,even the original db has no pk:( what other options do I have?
1. Add IDENTITY column to the target table?
2. Forget trigger and update your column after whole table is populated?
--Vadim R.
March 4, 2011 at 3:25 pm
niteshrajgopal (2/27/2011)
Hi Guys,I have a table with no Primary Key. I have a column that is null. But I want to set it to 'NR' after it has been inserted. I've only seen examples doing this on tables with a Primary Key. How can I achieve this with my table. please help
Either I'm confused or I think we're overthinking this...
Can't you simply run a statement like this after your insert?
UPDATE table
SET ColA = 'NR'
WHERE ColA IS NULL
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2011 at 3:30 pm
^^ That is exactly what I thought but OP for some reason wanted to do it in the trigger.
--Vadim R.
March 4, 2011 at 3:55 pm
rVadim (3/4/2011)
^^ That is exactly what I thought but OP for some reason wanted to do it in the trigger.
Yeah, I noticed that after I posted (I had the window open a while), but I still think it's over-thinking it, especially for bulk processing on a staging table. If he was only looking for the 'new' rows, I could see it... and I'd recommend switching from an AFTER trigger in that case to an INSTEAD OF trigger, and wrapping the inbound column in a CASE statement (create your own default).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply