Append one Table one with Table Two

Hello everyone I am trying to append data from one table to another, but SQL wont let me it says that I cannot insert null values.[main table] and [Report] Now one table has more records then the other, I ran a query and found in the report table about 903 records more then the [main table]. Anyone know what I should do the data types are the same for the primary key. I just need to insert some fields from the Main Table to the Report tbl. Can anyone help please?

Code Snippet

INSERT INTO dbo.Report
(Area, LossType, Loss,)
SELECT dbo.Report.Area, dbo.Report.LossType, dbo.Report.Loss, dbo.[Main Table].[IR Number]
FROM dbo.[Main Table] INNER JOIN
dbo.Report ON dbo.[Main Table].[IR Number] = dbo.Report.[Incident Report No]

Code Snippet

SELECT dbo.[Main Table].[IR Number], dbo.Report.[Incident Report No], dbo.Report.LossType, dbo.Report.Loss, dbo.Report.Area
FROM dbo.[Main Table] INNER JOIN
dbo.Report ON dbo.[Main Table].[IR Number] = dbo.Report.[Incident Report No]

There is not quite enough information to give you a defenitive response, but here are some ideas. (The TABLE definintions are sorely needed in this situation.)

In the report table, there are constraints on some columns that prohibit records from having NULL values. Some of the records from the SELECT query have NULL values and cannot be added to the report table. Remove the NULL value constraints from the Report table OR explore why the query is providing data with NULL values and correct the query.

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||

Sorry about that thank you Arnie I will try to remember to do that. is this ok?

Code Snippet

CREATE TABLE [IncidentReports] (
[Notes] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_Notes] DEFAULT (N'Scanned Report'),
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_I/RDocument] DEFAULT (N'Scanned Reports'),
[Action Type] [int] NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_IncidentReports_Loss] DEFAULT (0.00),
[Violation Type] [int] NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inspector] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_IncidentReports] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Code Snippet

CREATE TABLE [Main Table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Inspector] [int] NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [int] NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Main Table] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thank you, that really helps to see what we are working with.

I noticed that in [Main Table], [IR Number] can be NULL, while in [IncidentReports], [IR Number] is NOT NULL.

Could there be NULL values in the [Main Table] for [IR Number]?

If so, attempting to add those record to the reporting table will fail due to the NOT NULL constraint.

|||Ok I fixed that and tried to insert again but it's still giving me that message "Cannot insert the value Null into column 'Incident Report No', table 'incident reportsdb.dbo.Report_Tbl'; column does not allwo nulls. INSERT fails|||

Now back to your original query:

INSERT INTO dbo.Report
(Area, LossType, Loss,)
SELECT dbo.Report.Area, dbo.Report.LossType, dbo.Report.Loss, dbo.[Main Table].[IR Number]
FROM dbo.[Main Table] INNER JOIN
dbo.Report ON dbo.[Main Table].[IR Number] = dbo.Report.[Incident Report No]

It appears that there is something now quite right with the SELECT query. Follow me as I try to tease it out.

You are inserting into three (3) columns, but your SELECT query produces four(4) columns. Therefore, you are inadvertently attempting to insert a NULL value into [Incident Report No]. I also question the purpose of the JOIN between [Reports] and [Main Table]. As a result, you are attempting to insert other columns that may have NULL values (Report.Area, Report.LossType, Report.Loss.

It seems to me that you would want to be SELECTing the data from [Main Table] to insert into the Reporting table. Let me know if I am anywhere in the ballpark...

Code Snippet

INSERT INTO dbo.IncidentReports
( [IR Number],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE {criteria}

|||

Heres my statement I tried before that one and I'm still getting that message...[banging head against wall] Thank you so much Arnie for helping me I really appreciate it

Code Snippet

INSERT INTO dbo.Report
(LossType, Loss, Area)
SELECT [Loss Type], Loss, Area
FROM dbo.[Main Table]

|||

The [IncidentReports] table definition requires a value for [IR Number].

[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

You MUST include a value for [IR Number]. (Or change the table definition for [IncidentReports] and not require it.)

Did the previous query suggestion I offered, that included .[Main Table].[IR Number] not work for you?

|||

this what your talking about

Code Snippet

INSERT INTO dbo.Report
( [Incident Report No],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE [Incident Report No] = [IR NUMBER]

RETURN

|||

Yes. But I did have a typo and you must have caught this before I corrected the typo.

[Incident Report No] should be [IR Number]

Make that change and see if the query executes and gives you the data you want.

|||

Oh shoot sorry Arnie I did a DDL on the wrong table too

Code Snippet

CREATE TABLE [Report] (
[Incident Report No] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[Investigators Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reported by] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Law Enforcement Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of Officer] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Evidence Seized] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associated Reports] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Corrective Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
[TypeOfIncident] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
[86_D] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
[LossType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action/Incident] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[Incident Report No]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Then change the query to [Incident Report No] on the INSERT portion -NOT the SELECT.

Code Snippet

INSERT INTO dbo.Report
( [Incident Report No],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE {criteria}

|||

Is this part correct though

WHERE [Incident Report No] = [IR Number]

See I have a database that I created a while ago and I found the script I used finally and I used a SP_Procedure to do that

Code Snippet

CREATE PROCEDURE [InsertTerms]
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GO

|||

I don't think that you want

[Incident Report No] = [IR Number]

as a WHERE clause. First, since there is not a JOIN between the two tables, it would fail; second, there is not a column named [Incident Report No] in the [Main Table] table; and last, if there was a JOIN between [Main Table] and [Report], you would only get data that already exists in [Report], and it would not add any NEW data.

Turning the INSERT query into a stored procedure is fine. (Remove the WHERE {criteria} IF you want all rows from [Main Table], otherwise, add the appropriate filter.)

Code Snippet

CREATE PROCEDURE dbo.InsertReportData
AS
INSERT INTO dbo.Report
( [Incident Report No],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE {criteria}

|||

Thank you so much for your help Arnie YOUR THE BEST

I'm still learning SQL, just gotta keep reading and trying