CODEWITHSUNDEEP
sqlsql-servert-sql
Lex James Russell
Lex James Russell

Reputation: 11

INSERT INTO causing nulls in columns

I'm trying to concatenate two columns and add a comma and space between them in SQL Server 2014.

Here's the code I'm using (redacting the table name for privacy reasons)

INSERT INTO Table (EntityName)
    SELECT CONCAT(MailingLine1, ', ', MailingState) 
    FROM Table

This is concatenating the strings correctly, but in the process, it's causing all other columns and all other rows to read as NULL and wipes the data from the table.

There are roughly 30 columns that already have data within them

Any thoughts?

Upvotes: 1

Views: 82

Answers (3)

Hadi
Hadi

Reputation: 37313

If you are looking to add a new column with this function CONCAT(MailingLine1, ', ', MailingState) so you can use a computed column

--if [EnityName]  already exists in the table you have to drop it first
alter table TABLE
drop column [EntityName]

alter table TABLE
add [EntityName] as CONCAT(MailingLine1, ', ', MailingState)

If you don't want to add a computed column you can follow @GordonLinoff answer (use UPDATE command)

Else, If you are looking to insert new rows not to update rows, the issue is that you are only inserting values in EntityName column and ignoring all other columns, you can assign values as the following (assuming you have 3 columns)

Insert into Table (EntityName,EntityName2,EntityName3)

SELECT CONCAT(MailingLine1, ', ', MailingState),Value1,Value2 FROM Table

Where Value1,Value2 can be columns from the table, or fixed value i.e. 'John'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I'm guessing you want update, not insert:

update Table
    set EntityName = CONCAT(MailingLine1, ', ', MailingState);

Upvotes: 0

Ambrozie Beniamin Paval
Ambrozie Beniamin Paval

Reputation: 115

Set a default value for the other volums, a not NULL value. (See example)

ALTER TABLE {table_name} ADD DEFAULT {dafault_value} FOR {column_name}

Upvotes: 0

Related Questions