Insert Statement not recognizing an existing field

Question

Insert Statement not recognizing an existing field

sdabiri881

Valued Member

Points: 68
More actions
November 10, 2010 at 2:17 am

#228692

I am trying to add another field to this insert statement - but I am receiving an error message that the field does not exist. I am pulling this field from another temp table in this stored procedure- data is coming from the original table for this field - I have also added this field to my table for the temp table shown below. Any help you can give me would be greatly appreciated.
Thank you
CREATE TABLE #Staging
(
StartRange1int,
EndRange1int,
Minutesint,
callidint,
calldatetimedatetime,
Deathdatetime datetime,
Gender char(10),
OrganizationName varchar(80),
)
INSERT INTO
#Results
(
StartRange1,
EndRange1,
SortOrder,
Gender
)
Select
@StartRange1 As StartRange1,
@EndRange1 as EndRange1,
1.00,
'Female'
union Select
@StartRange1 As StartRange1,
@EndRange1 as EndRange1,
2.00,
'Male'
Union
Select
@StartRange1 As StartRange1,
@EndRange1 as EndRange1,
3.00,
'Unknown'
Union
Select
@StartRange1 As StartRange1,
@EndRange1 as EndRange1,
4.00,
'All(Female, Male, Unknown)'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

>> am pulling this field from another temp table in this stored procedure- data is coming from the original table for this field

Which other field?

You aren't referencing another table in any of the union clauses and everything looks like variables or literals.

Cursors never.
DTS - only when needed and never to control.

sdabiri881 Valued Member Points: 68 More actions · Answer 2

Sorry, I have added the #Staging Table that the #Results table is pulling from for the OrganizationName field. Hope this makes sense. Thank you for your assistance with this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sdabiri881 (11/10/2010)
Sorry, I have added the #Staging Table that the #Results table is pulling from for the OrganizationName field. Hope this makes sense. Thank you for your assistance with this.

The staging table isn't referenced by the INSERT INTO...SELECT at all, as Nigel has stated. You need to post more of your batch for this to make any sense.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nigelrivett SSCertifiable Points: 5472 More actions · Answer 4

sdabiri881 (11/10/2010)
Sorry, I have added the #Staging Table that the #Results table is pulling from for the OrganizationName field. Hope this makes sense. Thank you for your assistance with this.

For each of your unioned statements you will need to join to the new table to get the data. Will also need to specify which row to access for each row in the resultset.

INSERT INTO

#Results

(

StartRange1,

EndRange1,

SortOrder,

Gender ,

OrganizationName

)

Select

@StartRange1 As StartRange1,

@EndRange1 as EndRange1,

1.00,

'Female'

OrganizationName = t.OrganizationName

from #Staging t

where t.StartRange1 = @StartRange1-- this clause needs to get a single row from #staging (if that's what you want)

and t.EndRange1 = @EndRange1

.....

union

...

Cursors never.
DTS - only when needed and never to control.