CODEWITHSUNDEEP
sqlsql-serversql-insert
Tesla
Tesla

Reputation: 822

SQL insert into statement using select

I have a table, Table1, with 7 columns. Lets name them C1 to C7.

Columns C3-C6 allow for NULL values.

How do I write a SQL insert statement into Table1 that will grab the values for C1, and C2 from a different table (ReferenceTable), while setting C7 to the value 0?

Right now, I have the following:

INSERT INTO Table1 (C1, C2)
SELECT @C1, C2
FROM ReferenceTable

This does almost everything I need, except for initializing C7 with the value 0. How would I go about doing this?

Upvotes: 3

Views: 773

Answers (2)

Christian Phillips
Christian Phillips

Reputation: 18759

You can append the 0 to the SELECT...

INSERT INTO Table1 (C1, C2, C7)
SELECT C1, C2, 0
FROM ReferenceTable

or, you can set C7 to have a default value of 0, which would be my preferred option.

ALTER TABLE Table1 
ALTER COLUMN C7 SET DEFAULT (0)

So, C7 will always be 0 if nothing is inserted.

You'll see this in the designer (within Management Studio), when you modify a table, the field you are looking for is Default Value or Binding

enter image description here

Upvotes: 3

Andreas
Andreas

Reputation: 5103

Use this insert query: 

insert into table1 (c1,c2,c7) (select c1,c2,0 from reftable)

Upvotes: 4

Related Questions