troubleshooting Question

SQL Insert into table

Avatar of aneilg
aneilgFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQL
8 Comments2 Solutions323 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros