CODEWITHSUNDEEP
sqlsql-server
Mike_G
Mike_G

Reputation: 16502

inserting value in to SQL table and use SELECT statement

I am trying to insert values from one table in to the other, with an additonal parameter that is required. For instance:

INSERT INTO table1(someInt, someOtherInt, name, someBit)    
SELECT someInt, someOtherInt, name FROM table2

someBit is not allowed to be null, and I need to set it to False, but I am not quite sure how use it in the same INSERT statement. (Im using SQL 2005 if it matters)

edit: Ouch...looks like i threw out filet mignon to lions :-)

Upvotes: 3

Views: 606

Answers (5)

DBAndrew
DBAndrew

Reputation: 6958

You could also define a default value for that column on the destination table. This way you wouldn't have to reference it all in your insert statement.

Lets say your SomeBit is the Active flag or status on a customer table. Active 0 = off/false/no Active 1 = on/true/yes.

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_Active]  DEFAULT ((1)) FOR [Active]

Upvotes: 2

Tom H
Tom H

Reputation: 47444

You can just hard-code the value in the SELECT like so:

INSERT INTO
     dbo.table1
(
     someInt,
     someOtherInt,
     name,
     someBit
)
SELECT
     someInt,
     someOtherInt,
     name,
     0
FROM
     dbo.table2

Upvotes: 3

dotjoe
dotjoe

Reputation: 26940

INSERT INTO table1(someInt, someOtherInt, name, someBit)    
SELECT someInt, someOtherInt, name, 0 FROM table2

Upvotes: 4

Jaka Jančar
Jaka Jančar

Reputation: 11596

Perhaps

INSERT INTO table1(someInt, someOtherInt, name, someBit)
SELECT someInt, someOtherInt, name, 0 FROM table2

?

Upvotes: 4

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

Maybe

INSERT INTO table1(someInt, someOtherInt, name, someBit)    
SELECT someInt, someOtherInt, name, 0
FROM table2

Upvotes: 14

Related Questions