?
Solved

create fields using select into (field) as field2 but keep it blank.

Posted on 2006-05-10
6
Medium Priority
?
280 Views
Last Modified: 2012-06-21


I have this sql in SP. I am trying to create 4 new fields and set it either to null or blank at the same time so that I will not have to write a separate query to accomplish this. I figured out how to create the 4 new fields using one of the existing fields but how can I blank them out in a single query? The values will be populated later in the SP.


select
recipe_id,
measure,
quantity,
measure2,
quantity2,
measure as target_measure,
quantity as target_quantity,
measure2 as target_measure2,
quantity2 as target_quantity2
into mytemp
from nk_recipe
0
Comment
Question by:lunchbyte
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16649836
Does that mean that you don't want the values of measure/quantity/measure2/quantity2 in your new fields?  Are you just trying to get the same datatype?  What are the datatypes of the fields you are trying to create?
0
 
LVL 5

Author Comment

by:lunchbyte
ID: 16649850
Yes, I am just trying to create the 4 fields with the same datatype but leaving the values to either blank or null.

datatypes are varchar for measure and real for quantity
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 16649893
SELECT recipe_id,
   measure,
   quantity,
   measure2,
   quantity2,
   CAST('' AS varchar) AS target_measaure,
   CAST(NULL AS int) AS target_quantity,
   CAST('' AS varchar) AS target_measure2,
   CAST(NULL AS int) AS target_quantity2
INTO mytemp
FROM nk_recipe
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 4

Expert Comment

by:g_johnson
ID: 16649896
select
recipe_id,
measure,
quantity,
measure2,
quantity2,
null as target_measure,
null as target_quantity,
null as target_measure2,
null as target_quantity2
into mytemp
from nk_recipe

populates them as null, but i don't know how to "define" them as specific data type
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16649897
Posted that before i saw your answer.  You should be able to adapt it to your specific datatypes.
0
 
LVL 4

Expert Comment

by:g_johnson
ID: 16649913
lol -- a little late, bricrowe got the answer out there fast!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question