February 25, 2013 at 9:01 pm
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it says
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
Any help to catch the culprit is appreciated. I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
OPEN @dbLoop
Fetch Next from @dbloop into @dbname
While @@FETCH_STATUS=0
BEGIN
IF OBJECT_ID(@DBName + N'.dbo.Organization') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','OrganizationID') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','POL_ID') IS NULL
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
END
FETCH NEXT FROM @dbLoop INTO @dbname
END
--Pra:-):-)--------------------------------------------------------------------------------
February 25, 2013 at 9:44 pm
The error clearly says that you cannot insert null to organazationId column. I assume this column to be not null or even primary key.
If it is not null try inserting organization id from from the join.
If it is PK, then you may want to revisit your logic altogether. Edit: (Update the table instead of insert)
February 25, 2013 at 10:09 pm
What is ur "OrganizationID"(Keys,Constrain)...can you provide script of Organization table.
February 25, 2013 at 11:33 pm
prathibha_aviator (2/25/2013)
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it saysMsg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
Any help to catch the culprit is appreciated. I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
OPEN @dbLoop
Fetch Next from @dbloop into @dbname
While @@FETCH_STATUS=0
BEGIN
IF OBJECT_ID(@DBName + N'.dbo.Organization') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','OrganizationID') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','POL_ID') IS NULL
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
END
FETCH NEXT FROM @dbLoop INTO @dbname
END
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (Last_Backup, Backupsize) select '
-- The problem might be one of these so try ISNULL. You can replace the blanks with whatever
-- defaults are necessary if you don't want blanks.
'
ISNULL(DateModified,'') AS DateModified
,ISNULL(FileSizeinKB,'') AS FileSizeinKB
'
from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
END
February 26, 2013 at 7:46 am
joeroshan (2/25/2013)
The error clearly says that you cannot insert null to organazationId column. I assume this column to be not null or even primary key.If it is not null try inserting organization id from from the join.
If it is PK, then you may want to revisit your logic altogether. Edit: (Update the table instead of insert)
Hi, Please look at the dynamic query.. First of all I am not trying to insert any data in the primary key column... I created two columns for a table in all the databases. Now I am trying to populate those two columns with data using JOINS using dynamic query.. My query results are like this...
INSERT INTO Detroit_AIMIQ.dbo.Organization (Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics
INNER JOIN Detroit_AIMIQ.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = Detroit_AIMIQ.dbo.Organization.OrganizationID
INSERT INTO DesMoines_DEV.dbo.Organization (Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics
INNER JOIN DesMoines_DEV.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = DesMoines_DEV.dbo.Organization.OrganizationID
I dont see why is it giving the following error with a statement like above
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
--Pra:-):-)--------------------------------------------------------------------------------
February 26, 2013 at 7:56 am
Steven Willis (2/25/2013)
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (Last_Backup, Backupsize) select '
-- The problem might be one of these so try ISNULL. You can replace the blanks with whatever
-- defaults are necessary if you don't want blanks.
'
ISNULL(DateModified,'') AS DateModified
,ISNULL(FileSizeinKB,'') AS FileSizeinKB
'
from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
END
No It doesnt work it gives me the same error..
DO you think applying joins on a table column with NULLS and a table column which is a primary key in it is showing the error??????
--Pra:-):-)--------------------------------------------------------------------------------
February 26, 2013 at 8:36 am
prathibha_aviator (2/26/2013)
DO you think applying joins on a table column with NULLS and a table column which is a primary key in it is showing the error??????
Doing a join between certain data types that do not implicitly convert will usually cause a problem.
February 26, 2013 at 8:39 am
Oh good to know... I will check on dat.. and get back...
--Pra:-):-)--------------------------------------------------------------------------------
February 26, 2013 at 9:01 am
Steve,
I recreated the source table with the same destination datatypes... surprisingly i still get the same error..
--Pra:-):-)--------------------------------------------------------------------------------
February 26, 2013 at 9:28 am
Post the DDL for the table(s) involved.
February 26, 2013 at 9:28 am
You're not providing a value for OrganizationID in your insert statement, and it's a not null field.
Unless it's an identity field the insert will fail.
Need something like this:
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (OrganizationID, Last_Backup, Backupsize) select OrganizationID, DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
Edit: added code.
February 26, 2013 at 9:30 am
Also, if you are attempting to populate columns of existing rows of data, INSERT is not what you want, you want UPDATE.
Looking at your dynamic code, you are trying to insert new records and only attempting to populate the columns Last_Backup and Backupsize.
February 26, 2013 at 9:33 am
prathibha_aviator (2/25/2013)
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it saysMsg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
Any help to catch the culprit is appreciated. I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
OPEN @dbLoop
Fetch Next from @dbloop into @dbname
While @@FETCH_STATUS=0
BEGIN
IF OBJECT_ID(@DBName + N'.dbo.Organization') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','OrganizationID') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','POL_ID') IS NULL
BEGIN
SET @sql = @sql +'INSERT INTO ' + @dbname + '.dbo.Organization (Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics '
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = '+ @dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
END
FETCH NEXT FROM @dbLoop INTO @dbname
END
First thing I'd do then is start hardcoding the query to see what the results would be from just doing a specific select without inserting or using the cursor.
So try the base query and check if any of the values of the 'name' column are null. If so, then you would get concatenation errors in the dynamic SQL. If it's a huge result, add a filter to show any row with a null in the name column.
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
Next, make a version of the dynamic SQL that is NOT dynamic and run it to see where you are encountering nulls using the name of the specific db-name that was displayed in the error message.
SELECT
DateModified,
FileSizeinKB
FROM
AIM_Master.dbo.PSBackupStatistics
INNER JOIN
DesMoines_DEV.dbo.Organization
ON AIM_Master.dbo.PSBackupStatistics.POLID = DesMoines_DEV.dbo.Organization.OrganizationID
WHERE
DateModified IS NULL
OR FileSizeinKB IS NULL
At this point you'll just have to do line-by-line debugging until you find the point-of-failure.
February 26, 2013 at 9:48 am
SOurce table
CREATE TABLE [dbo].[PSBackupStatistics](
[POLID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
[FileSizeinKB] [bigint] NULL,
[Location] [nvarchar](255) NULL,
[Parish] [nvarchar](255) NULL,
[Diocese] [nvarchar](255) NULL
) ON [PRIMARY]
GO
Destination Table
CREATE TABLE [dbo].[Organization](
[OrganizationID] [int] NOT NULL,
[DioceseID] [int] NULL,
[ParishID] [int] NOT NULL,
[Diocese] [varchar](50) NULL,
[Parish] [varchar](100) NULL,
[Add_1] [varchar](50) NULL,
[Add_2] [varchar](50) NULL,
[PO_Box] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](10) NULL,
[Zip] [varchar](10) NULL,
[ZipID] [int] NOT NULL,
[County] [varchar](50) NULL,
[Country] [varchar](30) NULL,
[AreaCode] [varchar](5) NULL,
[Phone] [varchar](25) NULL,
[Fax] [varchar](25) NULL,
[Families] [int] NULL,
[RED_Students] [int] NULL,
[Notes] [text] NULL,
[Schedule] [text] NULL,
[Mission] [text] NULL,
[Website] [varchar](128) NULL,
[StaffURL] [varchar](128) NULL,
[BulletinURL] [varchar](128) NULL,
[WelcomeURL] [varchar](128) NULL,
[Directions] [text] NULL,
[LastUpdate] [datetime] NULL,
[OwnerOrganizationID] [int] NULL,
[SourceOrganizationID] [int] NOT NULL,
[DioUniqueID] [int] IDENTITY(1,1) NOT NULL,
[Last_Backup] [smalldatetime] NULL,
[Backupsize] [bigint] NULL,
CONSTRAINT [PK_Organization1] PRIMARY KEY CLUSTERED
(
[SourceOrganizationID] ASC,
[OrganizationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__Organization__3BFFE745] UNIQUE NONCLUSTERED
(
[DioUniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--Pra:-):-)--------------------------------------------------------------------------------
February 26, 2013 at 9:51 am
Looking at your table structure, I'm tempted to agree with those that have said you need to UPDATE the table rather than INSERT.
Is the data already there and you just need to refresh the values for Last_Backup & Backupsize fields?
Cheers
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply