Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    25

    Unanswered: dynamic insert- error

    prathibha_aviator (2/25/2013)[hr]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','Organizati onID') 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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your dynamic INSERT into the Organization table fails because it does not specify the OrganizationID column, and the OrganizationID column is declared as NOT NULL.

    In other words, the SQL you published here works fine... The dynamic SQL which it wrote has at least one flaw.

    This is actually pretty good for a first attempt at dynamic SQL, don't let that get you down!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2012
    Posts
    25
    Quote Originally Posted by Pat Phelan View Post
    Your dynamic INSERT into the Organization table fails because it does not specify the OrganizationID column, and the OrganizationID column is declared as NOT NULL.

    In other words, the SQL you published here works fine... The dynamic SQL which it wrote has at least one flaw.

    This is actually pretty good for a first attempt at dynamic SQL, don't let that get you down!

    -PatP

    I appreciate ur help Pat. But in particular i did not understand this statement..

    Your dynamic INSERT into the Organization table fails because it does not specify the OrganizationID column, and the OrganizationID column is declared as NOT NULL.

    Can u give me an example??

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  Demo table
    
    CREATE TABLE #Organization (
       OrganizationId   INT         NOT NULL
    ,  Last_Backup      DATETIME    NOT NULL
    ,  Backupsize       BIGINT      NOT NULL
       )
    GO
    
    --  What failed
    
    INSERT INTO #Organization (Last_Backup, Backupsize)
       SELECT GetDate(), 1024000
    GO
    
    --  How to make it succeed
    
    INSERT INTO #Organization (OrganizationId, Last_Backup, Backupsize)
       SELECT 1, GetDate(), 1024000
    GO
    
    --  Show results and clean up
    SELECT * FROM #Organization
    DROP TABLE #Organization
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2012
    Posts
    25
    --This is my query result now
    INSERT INTO DesMoines_DEV.dbo.Organization (OrganizationID, Last_Backup, Backupsize) select POLID, ISNULL(DateModified,' ') AS DateModified, ISNULL(FileSizeinKB,'') AS FileSizeinKB from AIM_Master.dbo.PSBackupStatistics
    INNER JOIN DesMoines_DEV.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID = DesMoines_DEV.dbo.Organization.OrganizationID
    And again the error is
    Msg 515, Level 16, State 2, Line 1
    Cannot insert the value NULL into column 'ParishID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    But I dont have any parishID equivalent column in my source table
    Pls help

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by prathibha44 View Post
    But I dont have any parishID equivalent column in my source table
    I can help you with problems like syntax errors and data integrity issues. You've found a schema problem, and that I can't address for you.

    You need to either find or create a value for every NOT NULL column in the Organization table. This requires insight into your schema and business requirements that I do not have, you must do it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2012
    Posts
    25
    I figured that as the table structure is already created i need to use update instead of insert.. I ignored a basic information there.. Thanks for all ur help though..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •