asked on

SQL Insert into table

I have the following query but far some reason my data is not getting inserted into the new table i create.

USE [Reports]
GO
/****** Object:  StoredProcedure [dbo].[sp_rptAllData] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_rptAllData]
 
AS
 
BEGIN
 
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].sp_rptAllData') 
		AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE sp_rptAllData
 
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].sp_rptAllData') 
		AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 
CREATE TABLE sp_rptAllData (
	[01_Jobcode]		VARCHAR (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
	[02_MPAN]			BIGINT,				--02
	[03_GCN]			VARCHAR (50),		--03
	[04_Site]			VARCHAR (60),		--04
	[05_SiteAddress1]	VARCHAR (50),		--05
	[06_SiteAddress2]	VARCHAR (50),		--06
	[07_SiteAddress3]	VARCHAR (50),		--07
	[08_SiteAddress4]	VARCHAR (50),		--08
	[09_SiteAddress5]	VARCHAR (50),		--09
	[10_SitePostcode]	VARCHAR (8),		--10
	[11_JobType]		VARCHAR (50),		--11
	[12_Supplier]		VARCHAR (10),		--12
	[13_Confirmed Date] DATETIME,			--13
	[14_Booked Date]	DATETIME,			--14
	[15_Inserted Date]	DATETIME,			--15
 
) ON [PRIMARY];
 
WITH RecordDetail AS
 
(
 
SELECT DISTINCT
 
	TblJob.Jobcode,												--01
	TblMain.MPAN AS MPAN,												--02
	TblGroupLog.GroupCustName AS GCN,							--03
	TblSiteDetails.Site AS [Site],										--04
	TblSiteDetails.SiteAddress1 AS SiteAddress1,						--05
	TblSiteDetails.SiteAddress2 AS SiteAddress2,						--06
	TblSiteDetails.SiteAddress3 AS SiteAddress3,						--07
	TblSiteDetails.SiteAddress4 AS SiteAddress4,						--08
	TblSiteDetails.SiteAddress5 AS SiteAddress5,						--09
	TblSiteDetails.SitePostcode AS SitePostcode,						--10
	TblType.JobType AS JobType,										--11
	TblType.Supplier AS Supplier,									--12
 	dbo.FormatDateTime(TblDetails.ApptConfirmedDate, 'DD/MM/YYYY') 
		AS [Confirmed Date],											--13
	dbo.FormatDateTime(TblDetails.ApptDateBooked, 'DD/MM/YYYY') 
		AS [Booked Date],												--14
	dbo.FormatDateTime(TblDetails.ApptInsertedDate, 'DD/MM/YYYY') 
		AS [Inserted Date]												--15
 
FROM TblName
		INNER JOIN
			TblJobLog ON TblName.ProgressStatusID = TblJobLog.ProgressStatusFK 
		INNER JOIN
			TblRequired 
		INNER JOIN
			TblMain 
		INNER JOIN
			TblGroupLog ON TblMain.MainID = TblGroupLog.MPANFK 
		INNER JOIN
			TblCustomerLog ON TblMain.MainID = TblCustomerLog.MPANFK 
		INNER JOIN
			TblCustomer ON TblCustomerLog.CustomerFK = TblCustomer.CustomerID 
		INNER JOIN
			TblJob ON TblMain.MainID = TblJob.MPANFK 
		INNER JOIN
			TblDetails ON TblJob.JobDetailsID = TblDetails.JobDetailsFK 
						   ON TblRequired.APPTDetailsFK = TblDetails.ApptDetailsID 
		INNER JOIN
			TblResource ON TblRequired.ResourcesFK = TblResource.ResourceID 
		INNER JOIN
			TblSiteDetails ON TblMain.MainID = TblSiteDetails.MPANFK 
 
		LEFT JOIN TblMainTariff ON TblMain.MainID = TblMainTariff.MPANFK 
 
		INNER JOIN
			TblTypeLog ON TblMain.MainID = TblTypeLog.MPANFK 
		INNER JOIN
			TblType ON TblTypeLog.SupplierFK = TblType.SupplierID 
						ON TblJobLog.JobDetailsFK = TblJob.JobDetailsID
 
		LEFT JOIN TblMeterDetails 
						ON TblJob.JobDetailsID = TblMeterDetails.JobDetailsFK 
		LEFT JOIN TblMeter 
						ON TblMeterDetails.MeterFK = TblMeter.MeterID 
		LEFT JOIN TblDropDownMaster ON TblMeter.ConnectionMethod = TblDropDownMaster.DropDownMasterID 
		LEFT OUTER JOIN TblType  ON TblJob.JobTypeFK = TblType.JobTypeID
 
)
 
INSERT INTO sp_rptAllData
 
(
	[01_Jobcode]							
,	[02_MPAN]		
,	[03_GCN]		
,	[04_Site]	
,	[05_SiteAddress1]	
,	[06_SiteAddress2]	
,	[07_SiteAddress3]		
,	[08_SiteAddress4]
,	[09_SiteAddress5]
,	[10_SitePostcode]
,	[11_JobType]
,	[12_Supplier]
,	[13_Confirmed Date]
,	[14_Booked Date]
,	[15_Inserted Date]
 
)
 
SELECT
 
		Jobcode					--01
,		MPAN					--02	
,		GCN						--03
,		[Site]					--04
,		SiteAddress1			--05	
,		SiteAddress2			--06
,		SiteAddress3			--07
,		SiteAddress4			--08
,		SiteAddress5			--09
,		SitePostcode			--10
,		JobType					--11
,		Supplier				--12
,		[Confirmed Date]		--13
,		[Booked Date]			--14
,		[Inserted Date]			--15
 
FROM NATPRecordDetail
 
--SELECT 
--
--	[01_Jobcode]							
--,	[02_MPAN]		
--,	[03_GCN]		
--,	[04_Site]	
--,	[05_SiteAddress1]	
--,	[06_SiteAddress2]	
--,	[07_SiteAddress3]		
--,	[08_SiteAddress4]
--,	[09_SiteAddress5]
--,	[10_SitePostcode]
--,	[11_JobType]
--,	[12_Supplier]
--,	[13_Confirmed Date]
--,	[14_Booked Date]
--,	[15_Inserted Date]
--
--FROM rptAllData
 
END

Open in new window

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aneilg

ASKER

Thanks for that but my table is still empty.

aneilg

ASKER

Far some reason My table is not being created.

Khalid Mehmood Awan

If you are executing this query through some program chunk, First try to execute this query from some query editor ...

may be you are not committing after insertion...

aneilg

ASKER

If i break the query down it works it parts.

ie.
The Select works fine.
The create table woks fine.

But when i drop the table, then run the query the table does not exist.
Then i try first creating the table, then running the query.
Then the table appears to be empty

SOLUTION

Khalid Mehmood Awan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tigin44

you did created the sp and the table with same name... I changed them and also reference to the cte is changed too... did you compile and try my above post...

aneilg

ASKER

Thanks guys.