Server: Msg 107, Level 16, State 3, Line 14
The column prefix 'tmp1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 31
The column prefix 'tbl_tmp_CP' does not match with a table name or alias name used in the query.

CREATE PROCEDURE procCommitCPsToDatabase 

AS

/* Get the list of CPs that are to be uploaded */

IF object_id('tmp1', 'U') IS NOT NULL
	BEGIN	
		DROP TABLE tmp1
	END

BEGIN
	CREATE TABLE tmp1 (	CPID nvarchar(50), UploadID nvarchar(50) )
	INSERT INTO tmp1 (CPID, UploadID)
	SELECT CPID, UploadID FROM CPsForCommittingToDatabase


	/* ###### Need to copy CP data to the deleted CP tables ###### */

	INSERT INTO tbl_final_UploadFile ([UID], [UploadID], [ContractorID], [OriginalFileName], [StoredFileName], [Status], [UploadMessage], [UploadDate], [Reject])
		SELECT [UID], [UploadID], [ContractorID], [OriginalFileName], [StoredFileName], [Status], [UploadMessage], [UploadDate], [Reject]
		FROM tbl_tmp_UploadFile
		WHERE EXISTS(
			SELECT * FROM tmp1
			WHERE tbl_tmp_UploadFile.UploadID = tmp1.UploadID
			)
			AND
			NOT EXISTS (
			SELECT * FROM tbl_final_UploadFile
			WHERE  tbl_tmp_UploadFile.UploadID = tmp1.UploadID
			)

		/* CP */
	INSERT INTO tbl_final_CP ([UID], [CPID], [UploadID], [CP], [Area], [Day], [dCount], [iFlw], [iValid], [ValidationMsg], [UserComments], [Video], [ExtEnum])
		SELECT [UID],[CPID],[UploadID],[CP],[Area],[Day],[dCount],[iFlw],[iValid],[ValidationMsg],[UserComments], [Video], [ExtEnum]
		FROM tbl_tmp_CP
		WHERE EXISTS(
			SELECT * FROM tmp1
			WHERE tbl_tmp_CP.CPID = tmp1.CPID
			)

	/* CountData */
	INSERT INTO tbl_final_CountData ([UID], [CountID], [CPID], [iDir], [Hour], [PC], [2WMV], [CAR], [BUS], [LGV], [HGVR2], [HGVR3], [HGVR4], [HGVA3], [HGVA5], [HGVA6], [HGV], [AMV])
		SELECT [UID], [CountID], [CPID], [iDir], [Hour], [PC], [2WMV], [CAR], [BUS], [LGV], [HGVR2], [HGVR3], [HGVR4], [HGVA3], [HGVA5], [HGVA6], [HGV], [AMV]
		FROM tbl_tmp_CountData
		WHERE EXISTS(
			SELECT * FROM tmp1
			WHERE tbl_tmp_CP.CPID = tmp1.CPID
			)


	/* Now set the status of the CP copies still in tmp tables to 4 (Committed to Database) */
	UPDATE tbl_tmp_CP
	SET [Completed] = 4
	WHERE EXISTS(
		SELECT * FROM tmp1
		WHERE tbl_tmp_CP.CPID = tmp1.CPID
		)

	/*  Delete the temporary table */
	DROP TABLE tmp1
END
GO

CREATE PROCEDURE procCommitCPsToDatabase 
AS
/* Get the list of CPs that are to be uploaded */
IF object_id('tmp1', 'U') IS NOT NULL
	BEGIN	
		DROP TABLE tmp1
	END
BEGIN
	CREATE TABLE #tmp1 (	CPID nvarchar(50), UploadID nvarchar(50) )
	INSERT INTO #tmp1 (CPID, UploadID)
	SELECT CPID, UploadID FROM CPsForCommittingToDatabase

	/* ###### Need to copy CP data to the deleted CP tables ###### */
	INSERT INTO tbl_final_UploadFile ([UID], [UploadID], [ContractorID], [OriginalFileName], [StoredFileName], [Status], [UploadMessage], [UploadDate], [Reject])
		SELECT [UID], [UploadID], [ContractorID], [OriginalFileName], [StoredFileName], [Status], [UploadMessage], [UploadDate], [Reject]
		FROM tbl_tmp_UploadFile
		WHERE EXISTS
		(
			SELECT * FROM #tmp1
			WHERE tbl_tmp_UploadFile.UploadID = #tmp1.UploadID
		)
		AND NOT EXISTS 
		(
			SELECT * FROM tbl_final_UploadFile t
			WHERE  tbl_tmp_UploadFile.UploadID = t.UploadID
		)

		/* CP */
	INSERT INTO tbl_final_CP ([UID], [CPID], [UploadID], [CP], [Area], [DAY], [dCount], [iFlw], [iValid], [ValidationMsg], [UserComments], [Video], [ExtEnum])
		SELECT [UID],[CPID],[UploadID],[CP],[Area],[DAY],[dCount],[iFlw],[iValid],[ValidationMsg],[UserComments], [Video], [ExtEnum]
		FROM tbl_tmp_CP
		WHERE EXISTS(
			SELECT * FROM #tmp1
			WHERE tbl_tmp_CP.CPID = #tmp1.CPID
			)

	/* CountData */
	INSERT INTO tbl_final_CountData ([UID], [CountID], [CPID], [iDir], [Hour], [PC], [2WMV], [CAR], [BUS], [LGV], [HGVR2], [HGVR3], [HGVR4], [HGVA3], [HGVA5], [HGVA6], [HGV], [AMV])
		SELECT [UID], [CountID], [CPID], [iDir], [Hour], [PC], [2WMV], [CAR], [BUS], [LGV], [HGVR2], [HGVR3], [HGVR4], [HGVA3], [HGVA5], [HGVA6], [HGV], [AMV]
		FROM tbl_tmp_CountData
		WHERE EXISTS(
			SELECT * FROM #tmp1
			WHERE tbl_tmp_CountData.CPID = #tmp1.CPID
			)

	/* Now SET the status of the CP copies still IN tmp tables to 4 (Committed to Database) */
	UPDATE tbl_tmp_CP
	SET [Completed] = 4
	WHERE EXISTS(
		SELECT * FROM #tmp1
		WHERE tbl_tmp_CP.CPID = #tmp1.CPID
		)

	/*  DELETE the temporary TABLE */
	DROP TABLE tmp1
END
GO

DECLARE @tmp1 table(CPID nvarchar(50), UploadID nvarchar(50))

quote:

---

Originally posted by adjones1980

OK so putting '#' in front of the table name creates it in memory only then? This would definitely save me managing the tables.

---

quote:

---

Originally posted by adjones1980

I copied the content of the procedure. I'll try exec the procedure.

---