• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 167 views
  • Users 0 members are here
Related

Trying to insert temporary logins using SQL/SSMS

Sarah Covie
$organization

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

Fullscreen Insert into T_CUST_LOGIN attempt.txt Download 
--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;

Parents
  • $organization

    Hi Sarah,

    T_CUST_LOGIN still uses T_NEXT_ID, so you do have to insert the login_no.  I use syntax like this: 

    Fullscreen insert logins.txt Download 
    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)

Reply
  • $organization

    Hi Sarah,

    T_CUST_LOGIN still uses T_NEXT_ID, so you do have to insert the login_no.  I use syntax like this: 

    Fullscreen insert logins.txt Download 
    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)

Children