Hi all,
We are looking into possible adding temporary logins to a list of customers who do not currently have a login but do have an email address with us - this is to help them access new digital content benefits of their existing subscriptions. I'm trying out some different methods in SSMS and keep running into the error: "Cannot insert the value NULL into column 'login_no', table 'impresario.dbo.T_CUST_LOGIN'; column does not allow nulls. INSERT fails." I did not include the column login_no in my insert statement because it is the primary key so I assumed it would auto-increment for new rows, but that does not seem to be the case. Anyone have advice on how to deal with that login_no column?
I've attached the current version of my script that I'm working with if that's helpful - I'm definitely a SQL beginner so any tips are appreciated!
Thanks,
Sarah Covie
--Create cursor to run through eaddress and insert logins DECLARE @customer_no as int DECLARE Customer_Cursor CURSOR FOR SELECT a.customer_no FROM [impresario].[dbo].[T_EADDRESS] a INNER JOIN T_LIST_CONTENTS l ON l.customer_no = a.customer_no WHERE l.list_no = 3748; OPEN Customer_Cursor; FETCH NEXT FROM Customer_Cursor INTO @customer_no; WHILE @@FETCH_STATUS = 0 BEGIN -- Insert new Temp login INSERT INTO T_CUST_LOGIN (customer_no, n1n2_ind, login_type, login, eaddress_no, primary_ind, inactive, temporary_ind) SELECT a.customer_no, 1, 1, a.address, a.eaddress_no, 'Y', 'N', 'Y' FROM T_EADDRESS a INNER JOIN T_LIST_CONTENTS l ON l.customer_no = a.customer_no JOIN T_CUSTOMER b ON a.customer_no = b.customer_no WHERE a.customer_no = @customer_no AND l.list_no = 3748; -- Continue with the next person FETCH NEXT FROM Customer_Cursor INTO @customer_no; END; --Close out Cursor once done CLOSE Customer_Cursor; DEALLOCATE Customer_Cursor;
Sarah,
T_CUST_LOGIN uses a value from T_NEXT_ID, type = LO, I believe. So you are going to have to insert that yourself and increment manually inside the cursor and then be certain to update the T_NEXT_ID table afterwards. I recommend trying it in TEST first, and best to use a TRY/CATCH/TRANSACTION for error handling with something like this.
Best of luck!
John