May 15, 2011 at 3:38 am
I want to add records to two tables at a specific interval so I may test Merge Replication.
I am using AdventureWorks2008 as an example to get me started. I want to add to Peron.Person records from a “staging” table or CSV file that has some data that I created with Spawner Data Generator.
This is what I have so far to populate Person.BusinessEntity:
while 1=1
begin
INSERT INTO Person.BusinessEntity(ModifiedDate)
Values(CURRENT_TIMESTAMP)
waitfor delay '00:000:15'
End
GO
What do I need to add to insert a corresponding Person.Person record after I create the Person.BusinessEntity for it?
Put another way, I want to create a Person.BusinessEntity then insert Person.Person (with the corresponding Foreign key from the Person.BusinessEntity table) with data from either a CSV or a secondary table.
So….
while 1=1
begin
INSERT INTO Person.BusinessEntity(ModifiedDate)
Values(CURRENT_TIMESTAMP)
Insert next record from CSV or other table TO Person.Person
waitfor delay '00:000:15'
End
GO
Thank you.
May 15, 2011 at 5:39 am
Hi,
since BusinessEntity has BusinessEntityID column which is Identity (1,1), you can get ID of newly inserted BusinessEntity by calling built-in SCOPE_IDENTITY() function.
Like that:
DECLARE @businessEntityId INT
-- insert into BusinessEntity
SELECT @businessEntityId = SCOPE_IDENTITY()
-- insert into Person using @businessEntityId as BusinessEntityId
Jakub
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
May 15, 2011 at 1:59 pm
You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY
http://support.microsoft.com/kb/2019779
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply