trying to insert using sql

Question

trying to insert using sql

sqlizer

SSCrazy

Points: 2120
More actions
November 7, 2012 at 1:53 pm

#270564

I am trying to run this query but getting following error:
Query:
insert into TB_HOSP_CORE (MON) SELECT AMONTH FROM hosp_core WHERE TB_HOSP_CORE.PATIENT_ID = hosp_core."KEY"
Error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TB_HOSP_CORE.PATIENT_ID" could not be bound.
Actually there are 2 tables and I am trying to insert data from one table to another but with making sure that data matches with original table. I know I am making some silly mistake but any help would be appreciated.
Thanks

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

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

sqlizer (11/7/2012)
I am trying to run this query but getting following error:
Query:
insert into TB_HOSP_CORE (MON) SELECT AMONTH FROM hosp_core WHERE TB_HOSP_CORE.PATIENT_ID = hosp_core."KEY"
Error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TB_HOSP_CORE.PATIENT_ID" could not be bound.
Actually there are 2 tables and I am trying to insert data from one table to another but with making sure that data matches with original table. I know I am making some silly mistake but any help would be appreciated.
Thanks

the problem is that TB_HOSP_CORE does not appear in your FROM clause of the query. the issue is not with the insert its with the select statement. when you use INSERT INTO ... SELECT the select statement has to be able to be run in its own right.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

Edit!!

Snippy Snippy Double Dippy!

i double psoted somehow sorry.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

you know your data better than use;

this query seems to be what you were trying to insert.

does the sELECT return teh expected values? if the PatientId = Key, and it already exists, did you really mean to UPDATE some column with the AMON value for the matching patients? why insert a single column?

--insert into TB_HOSP_CORE (MON)

SELECT AMONTH

FROM hosp_core

INNER JOIN TB_HOSP_CORE

ON TB_HOSP_CORE.PATIENT_ID = hosp_core."KEY"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlizer SSCrazy Points: 2120 More actions · Answer 4

Thanks for the reply lowell, but just added a column to the table and want to populate it with data from hosp_core.

So I took advise and ran update statement instead of insert and it worked. Took about 16 mins to update record but it worked.

sqlizer SSCrazy Points: 2120 More actions · Answer 5

forgot to add code for update here it is:

UPDATE TB_HOSP_CORE

SET mon = AMONTH

FROM

hosp_core ss

INNER JOIN TB_HOSP_CORE tb

ON tb.PATIENT_ID = ss."key"