CODEWITHSUNDEEP
sql-server
usefulBee
usefulBee

Reputation: 9692

Copy Table Columns to another Table and Handle other Columns that do not Accept Null

The answer to the first half is borrowed from here

INSERT INTO newAddressBook (newName, newSurname, Phone)
SELECT name, surname, number
FROM oldAddressBook

If I apply the answer as it is without modifications, I get the following error:

Cannot insert the value NULL into column 'Resident', table 'dbo.newAddressBook'; column does not allow nulls. INSERT fails.

I would like to be able to copy columns while passing a default value, false, to a column (Resident) that does not have a match in the original table and does not accept null either.

Upvotes: 0

Views: 28

Answers (3)

Dilip Nannaware
Dilip Nannaware

Reputation: 1480

INSERT INTO newAddressBook (Resident) SELECT ISNULL(Resident, 'Default') As Resident FROM oldAddressBook

In above example if resident value is Null then 'Default' value will be inserted in newAddressBook.

Upvotes: 0

Kramb
Kramb

Reputation: 1092

Give it a CASE WHEN statement?

INSERT INTO newAddressBook (Resident)
SELECT (CASE WHEN Resident IS NOT NULL THEN Resident ELSE 'False' END)
FROM oldAddressBook

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Here is how you can do this. Since Resident does not allow null you must provide a value. You can do this with a literal value.

INSERT INTO newAddressBook (newName, newSurname, Phone, Resident)
SELECT name, surname, number, 'false'
FROM oldAddressBook

Upvotes: 1

Related Questions