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;
Hi Sarah,
T_CUST_LOGIN still uses T_NEXT_ID, so you do have to insert the login_no. I use syntax like this:
CREATE TABLE #create (id INT IDENTITY, customer_no INT NOT NULL, eaddress_no INT NULL, address VARCHAR(80) NULL, login VARCHAR(80) NULL) INSERT INTO #create SELECT customer_no, blah, blah, blah FROM foo WHERE foo_column = blah SELECT * FROM t_next_id WHERE type = 'LO' --For example: 6241572 UPDATE t_next_id SET next_id = 6241572 + 200 WHERE type = 'LO' -- (Max is more than the # of records I'm trying to insert) INSERT INTO dbo.T_CUST_LOGIN ( login_no, customer_no, n1n2_ind, login_type, login, password, eaddress_no, primary_ind, -- const_update_dt, inactive, temporary_ind ) SELECT 6241572 + RANK() OVER (ORDER BY c.customer_no), c.customer_no, 1, 1, c.address, NULL, c.eaddress_no, 'N', 'N', 'Y' FROM #create c WHERE c.eaddress_no IS NOT NULL AND c.address NOT IN (SELECT login FROM t_cust_login)
You want to use ROW_NUMBER() not RANK() as it is possible for Rank to return the same value