SQL:Insert record under condition with constant and select values

[vyline]

Hi,

I need insert records into table1 if condition in table2 is met. The insert values include constant values and values that are selected from table2. How can I do it?

There are 10 fields in table1, 5 fields are constant value, 5 fields need to be selected from table2 when condition, such as table2.status ='N' met. So if table2.status ='N', then insert a record into table1 with 10 fields.

if all values are from table2, then I will do:

Insert into table1(f1,f2,f3,f4,f5...f10)
select f1,f2...f10
from table2
where table2.status ='N'

But I need to set values for f1 to f5 which are not select from table2, they are constant. How can I set the values at the same time when select others. I am working on SQL server 2008.
Thank you so much for any helps and suggestions!!!

 

[gmmastros]

You can just hard code the values, like this:

Insert into table1(f1,f2,f3,f4,f5...f10)
    select [!]21,'apple',14.2[/!],...f10
    from table2
    where table2.status ='N'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vyline]

gmmastros,

Thanks a lot!!
My database is not available now,I will try later.

 

[vyline]

It is working!

But a new issue comes out. I have one field which is uniqueidentifier data type and I need to hard code that field. When I insert it, I got the error "Conversion failed when converting from a character string to uniqueidentifier." even I did CAST(’DE1651A5-36A5-85EB-5K3B-KLC46592867M’ AS uniqueidentifier)

Insert into table1(f1,f2,f3,f4,f5...f10)
select 21,'apple',CAST(’DE1651A5-36A5-85EB-5K3B-KLC46592867M’ AS uniqueidentifier),...f10
from table2
where table2.status ='N'
Any idea for inserting a hard code uniqueidentifier?

Thank you for the helps!!!

 

[gmmastros]

Unique Identifiers can only contain characters 0 through F.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vyline]

I am sorry.
The value that I inserted is between 0-F, I changed it incorrectly when I posted. The value I used is from another table with the same data type. But I still got the same error.
Thanks lot!!