Adding new records to existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding new records to existing table

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-02 : 23:38:32

Hi,

I am trying to add new records to an existing table taking values from a temporary tables. Here's the code:

--COLLECTING INFORMATION
SELECT SD.name, MF.database_id, SUM( CONVERT(decimal(10,2),(MF.size*8)/1024) ) as Size
INTO #TempDBSize
FROM sys.master_files MF
JOIN sys.databases SD
ON SD.database_id = MF.database_id 
WHERE type = 0
--ORDER BY database_id, name	
GROUP BY SD.name, MF.database_id
GO

--UPDATING RECORD FOR EXISTING DATABASE

IF EXISTS( SELECT DISTINCT database_id FROM #TempDBSize WHERE database_id IN (SELECT DISTINCT DBID FROM dbo.DBSize))

BEGIN	

	INSERT INTO dbo.DBInfo
		(DBName, DBID )
		
	(SELECT 
		tds.name, tds.database_id
		FROM #TempDBSize tds)
	
	INSERT INTO dbo.DBSize 
		(DBID, Size, MetricDate)
			
	(SELECT 
		#TempDBSize.database_ID,
		#TempDBSize.Size,
		GetDate() as MetricDate
	FROM #TempDBSize
	)

END

DROP TABLE #TempDBSize;

But when a new record (a new database) has been added, I am not able to insert that record into the physical table; I am getting a PK violation.

Now, I understand why (I am inserting a duplicate value on DBInfo table) But I do not know how to add the new record without violating the PK constraint. I need that new record into DBSize table, which does not have DBID has PK.

To give more background about the two tables. I am trying to keep an historical data of database sizes and for that, I am using a temporary table and then loading those records into a physical table.

Any help is highly appreciated,

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-03 : 01:51:48

whats the currently defined pk for table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-03 : 10:28:24

quote:
Originally posted by visakh16

whats the currently defined pk for table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for reply!

I actually ended solving the problem myself. I'll post it here in case someone else face a similar issue.

USE DBGrowth;
GO
--COLLECTING INFORMATION
SELECT SD.name, MF.database_id, SUM( CONVERT(decimal(10,2),(MF.size*8)/1024) ) as Size
INTO #TempDBSize
FROM sys.master_files MF
JOIN sys.databases SD
ON SD.database_id = MF.database_id 
WHERE type = 0
--ORDER BY database_id, name	
GROUP BY SD.name, MF.database_id
GO

--UPDATING RECORD FOR EXISTING DATABASE

IF EXISTS( SELECT database_id FROM #TempDBSize WHERE database_id NOT IN (SELECT DISTINCT DBID FROM dbo.DBInfo))

BEGIN	
	INSERT INTO dbo.DBInfo
		(DBName, DBID )		
	(SELECT 
		tds.name, tds.database_id
		FROM #TempDBSize tds
		WHERE tds.database_ID NOT IN (SELECT DISTINCT DBID from DBInfo WHERE DBID = tds.database_ID))
	
	INSERT INTO dbo.DBSize 
		(DBID, Size, MetricDate)
			
	(SELECT 
		#TempDBSize.database_ID,
		#TempDBSize.Size,
		GetDate() as MetricDate
	FROM #TempDBSize
	)
END

ELSE

BEGIN	
	INSERT INTO dbo.DBSize 
		(DBID, Size, MetricDate)
			
	(SELECT 
		#TempDBSize.database_ID,
		#TempDBSize.Size,
		GetDate() as MetricDate
	FROM #TempDBSize
	)
END


DROP TABLE #TempDBSize;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources