• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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

0
aneilg
Asked:
aneilg
  • 4
  • 2
  • 2
2 Solutions
 
tigin44Commented:
try this
/****** 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 name = N'sprptAllData' )
DROP TABLE sprptAllData
 
IF NOT EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = N'sprptAllData' )
 
CREATE TABLE sprptAllData (
        [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 sprptAllData
 
(
        [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 RecordDetail
 
--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

0
 
aneilgAuthor Commented:
Thanks for that but my table is still empty.
0
 
aneilgAuthor Commented:
Far some reason My table is not being created.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Khalid Mehmood AwanCommented:
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...
0
 
aneilgAuthor Commented:
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
0
 
Khalid Mehmood AwanCommented:
add a COMMIT statement at end ?
0
 
tigin44Commented:
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...
0
 
aneilgAuthor Commented:
Thanks guys.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now