May 11, 2011 at 10:02 pm
Hi Michael,
When I tried with a test data on SQL Server 2008 inserts were successful.
Perhaps, I'd like to try with your table definition & some test data if you can provide the same. Would you be able to share your TABLES definition script? That should be really helpful.
May 12, 2011 at 3:08 am
Michael.D (5/11/2011)
I am not even referencing the column Pri_Org_Code4 in the query.
You've almost answered your own question there. You haven't referenced the column, so when you insert a row into that table (unless you have defined a default value for that column) SQL Server will attempt to set it to NULL.
SELECT Pri_Org_Code4 FROM CurAccounts WHERE Pri_Org_Code4 = 'NULL'
That isn't the way to check for a NULL value in a column, try this instead.
SELECT Pri_Org_Code4 FROM CurAccounts WHERE Pri_Org_Code4 IS NULL
May 12, 2011 at 8:30 pm
Guys,
It suddenly dawned on me that perhaps I'm not going about this the right way (Ian's answer made me think of this). I don't want to update an entire row. Just one column. So rather than using INSERT, does it make more sense that I should be using UPDATE to "insert" the value of a single column from one table into a single column in another?
I'm trying to do this without using CURSOR but in reality, this database is relatively small (about 50,000 records) so I'm not worried about performance (and it's only for practice to learn T-SQL). I've got a back-up. If I trash it, I can restore and start over.
I think I'll move my questions to the "Newbie" section. I only responded to this post because it was so similar to my particular problem.
I thank you, all who responded.
-Michael.
May 12, 2011 at 8:37 pm
Sorry, I meant to say "I've been going about this the wrong way.
Which brings me to an off-topic question. If you want to go back and edit a post, how do you re-submit the edit with your changes? I see no options to "save" or "post" when you edit your previous post.
-Michael.
May 13, 2011 at 1:30 am
I'm a bit confused where you swap between talking about an INSERT and then an UPDATE.
Can you post your exact requirements here, because, as the Cursor Police on this forum will tell you, there is nearly always a set based way of updating without resorting to a cursor.
If you want to change the contents of just one column in an existing row (or rows), based on the contents of another table then you need to use this sort of construct...
UPDATE CurAccounts SET Member2_First = CurProfile.Spouse
FROM CurProfile
WHERE CurAccounts.id = CurProfile.id
If you post the table definitions, some sample data with your requirements and expected results, you are almost 100% guaranteed to get a tested solution from someone on this forum.
May 13, 2011 at 5:59 pm
Ian,
That worked perfectly. I was very close to the answer but was making it overly complicated.
I kept trying to use INNER JOIN on the tables instead of using a simple WHERE, as in your example,
and that resulted in updating every row with the same (first) value from the second table.
But was I right that UPDATE was the correct choice? INSERT would have required a cursor to work, no?
So, back to practicing simple queries and the Newbie Forum!
I very much appreciate everyone's help.
-Michael.
August 4, 2011 at 11:42 pm
Hi Ankit,
U can Change Column datatype to NULL to NOT NULL.
ie.
Create Table #tbl(
AccountId varchar(16) COLLATE DATABASE_DEFAULT NOT NULL,
RatesheetId int,
TariffId int NOT NULL
)
I think its works.
It is not exact method, but you can try it.
March 13, 2015 at 8:27 am
Whatever connector your website is using to connect to the database is setting some defaults that are causing your temp table columns to default to NOT NULL.
You can reproduce your issue in SQL Management Studio by adding this before you sproc call:
SET ANSI_NULL_DFLT_OFF ON;
To fix your sproc without explicitly setting your columns to be nullable, you can add this to the top of your sproc:
SET ANSI_NULL_DFLT_ON ON;
Here's what they do:
ANSI_NULL_DFLT_ON
Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
ANSI_NULL_DFLT_OFF
Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
You can read about the settings here:
https://msdn.microsoft.com/en-us/library/ms190763.aspx
Regards, Jim C
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply