Loading records from two tables at specific interval….INSERT INTO?

  • 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.

  • 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

  • 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