Problem inserting into a table from another table

Transact-SQL
1

Hi everyone

I'm having a problem inserting data into a table from a temporary table. Basically, I have a temporary table called TEMPADDU25 which contains two columns called SERIALNUMBER and MAILINGTYPE.

I need to get the contents of TEMPADDU25.MAILINGTYPE and insert it into MAILINGPREFERENCE.MAILINGTYPE

I'm using the following SQL script, but am receiving the error:
Cannot insert the value NULL into column 'SERIALNUMBER', table 'dbo.MAILINGPREFERENCE'; column does not allow nulls. INSERT fails.

My SQL script:
INSERT INTO mailingpreference (mailingtype)
SELECT mailingtype
FROM tempaddu25
WHERE serialnumber=TEMPADDU25.serialnumber .

2 
INSERT INTO mailingpreference (mailingtype)
SELECT mailingtype
  FROM tempaddu25
 WHERE mailingtype is not null
3

Hi Bitsmed

Thank you - that makes perfect sense now. However, I'm still getting the same error.

Best wishes
Jon

4

Sorry my mistake.
Try this instead:

INSERT INTO mailingpreference (mailingtype,serialnumber)
SELECT mailingtype
      ,serialnumber
  FROM tempaddu25
 WHERE serialnumber is not null
5 
INSERT INTO mailingpreference (serialnumber, mailingtype)
SELECT serialnumber, mailingtype
  FROM tempaddu25

The table mailingpreference has the column serialnumber defined as not null - which means it is required.

6

Thank you once again. I can see how the script should work but I'm now getting this error:

Violation of PRIMARY KEY constraint 'PK_MAILINGPREFERENCE'. Cannot insert duplicate key in object 'dbo.MAILINGPREFERENCE'.

The MAILINGPREFERENCE table does contain other columns that aren't in the TEMPADDU25 table. These columns should all be NULL. I'm unsure if that is significant.

7

How is primary key defined in mailingpreference table?

8

Both SERIALNUMBER and MAILINGTYPE are Primary Keys on the table.I can add multiple MAILINGTYPE entries via the actual database, but can't work out how to do this in SQL.

9

Try this:

INSERT INTO mailingpreference (mailingtype,serialnumber)
SELECT DISTINCT
       a.mailingtype
      ,a.serialnumber
  FROM tempaddu25 as a
 WHERE a.serialnumber is not null
   AND NOT EXISTS(SELECT 1
                    FROM mailingpreference as b
                   WHERE a.mailingtype=a.mailingtype
                     AND b.serialnumber=a.serialnumber
                 )
1 Like
10

Perfect! Thank you so so much!