September 28, 2016 at 5:34 am
Hi,
Please can someone assist me for a syntax issue I'm having. I have a table which I would like to append to daily from a view. I used the INTO function to create the table initially from a view which works fine. The issue I'm having is appending to the table since then. Here is my code:
INSERT INTO [DB].[dbo].[TABLE1]
SELECT
*
FROM [DB].[dbo].[VIEW1]
WHERE [DB].[dbo].[VIEW1].[Report_Run_Date] > [DB].[dbo].[TABLE1].Report_Run_Date
I would like to run this as a proc daily but I've added a where clause so it only appends if the report run date in the view is greater than the report date in the table. I receiving the following error:The multi-part identifier "DB.dbo.table1.Report_Run_Date" could not be bound.
I'm sure it's just something stupid I'm not seeing. Also I know I shouldn't use the asterix and should define each field but I've added it here for brevity.
Thanks in advance.
September 28, 2016 at 6:18 am
Without included the DDL of your table (Report_Run_Date does exist in TABLE1, right?) and view this is a little hard, however, your query looks wrong anyway.
This would likely work better:
INSERT INTO [DB].[dbo].[TABLE1]
SELECT
*
FROM [DB].[dbo].[VIEW1]
WHERE [DB].[dbo].[VIEW1].[Report_Run_Date] > (SELECT MAX(sq.Report_Run_Date) from DB.dbo.TABLE1 sq)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2016 at 6:20 am
In the "SELECT" part of the query, SQL Server has no idea what [DB].[dbo].[TABLE1].Report_Run_Date is, because it is not part of the query.
This might be similar to what you're after:
INSERT INTO [DB].[dbo].[TABLE1]
SELECT *
FROM [DB].[dbo].[VIEW1] AS V1 -- Aliases help writing more compact code
INNER JOIN [DB].[dbo].[TABLE1] AS T1
ON V1.PrimaryKey = T1.PrimaryKey
WHERE V1.[Report_Run_Date] > T1.Report_Run_Date;
-- Gianluca Sartori
September 28, 2016 at 6:26 am
Thank you both, I've implemented the code and it works perfectly. I also now understand why the query wasn't recognising the SELECT Statement.
Many thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply