Select into question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select into question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 07:35:18

I am using

declare @cnt int

select @cnt = COUNT(*)

FROM DebtorCategories AS dc
JOIN Debtor AS dtr
ON dtr.DebtorID = dc.DebtorID
JOIN debt AS dt ON
dt.debtid = dtr.debtid

WHERE dtr.DebtorID = dc.DebtorID AND
dc.DebtorCategoryCode = '0001'

INSERT INTO dc SELECT dtr.DebtorID, '0001'

where @cnt = 0

GO

but am receiving the error

'the multi-part identifier dtr.debtorid could not be bound'.

I have, however, already referenced this elsewhere without problem. Can anyone please see why this is?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 07:43:18

Your SELECT as input for your INSERT has no FROM so dtr.DebtorID is unknown

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 07:44:36

The INSERT has NO relation to your first query

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 08:19:57

Should be something like this, else give table structure, sample data and wanted result


insert DebtorCategories
select dcs.DebtorID, '0001' from DebtorCategories AS dcs
where not exists(select * from DebtorCategories AS dc 
                 JOIN Debtor AS dtr ON dtr.DebtorID = dc.DebtorID 
                 JOIN debt AS dt ON dt.debtid = dtr.debtid
                 WHERE dc.DebtorCategoryCode = '0001'
                   and dcs.DebtorID = dc.DebtorID)

No, you're never too old to Yak'n'Roll if you're too young to die.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 08:21:30

Many thanks for your help. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources