populate a table with new data set but keep the original identity column values

  • I have a table like below, I would reset the values in the table with some fake addresses. I have already have the fake addresses ready without identity columns. But I would keep the original addressID in the table, so that other table use it as FK still works.

    Basically I want to keep the current table record with current identity columns values, and null out all the other columns and replace them with my new fake set of data, the order is not important, purpose is keep the values of existing identity columns.

    what is the best way to do that, thanks

    table:

    CREATE TABLE [dbo].[addMasterAddress](

    [AddressID] [INT] IDENTITY(1,1) NOT NULL,

    [HouseNumber] [INT] NOT NULL,

    [StreetName] [VARCHAR](60) NOT NULL,

    [StreetType] [VARCHAR](8) NULL,

    [City] [VARCHAR](30) NOT NULL,

    [County] [VARCHAR](30) NULL,

    [State] [VARCHAR](2) NOT NULL,

    [ZipCode] [VARCHAR](10) NOT NULL,

    CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED

    (

    [AddressID] ASC

    )

    ) ON [PRIMARY]

    GO

    Below is some final data samples for the table.

    INSERT [dbo].[addMasterAddress] ([AddressID], [HouseNumber], [StreetName], [StreetType], [City], [County], [State], [ZipCode])

    VALUES (101179, 99996, N'Dogwood', N'Way', N'Seattle', N'King', N'WA', N'98101' ),

    (101180, 100, N'Huron', N'ST', N'Seattle', N'King', N'WA', N'98101' ),

    (101185, 30, N'Pioneer', N'ST', N'Seattle', N'King', N'WA', N'98101' )

    GO

  • Why wouldn't you simply update one of the existing records?

    UPDATE addMasterAddress

    SET column = value...

    WHERE AddressID = <whatever id you want to keep>

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Basically I have two tables, one is AddMasterAddress table with 10000 records, the other is a new fake dataset let's call it FakeAddress table which has no identity column.  So I cannot use join with AddressID to update the other columns.

    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
  • I think of way that may do it. I can add a column named row_number in the query for each table, and join with the row_number to update  other columns of the addMasterAddress table.

  • sqlfriend wrote:

    I think of way that may do it. I can add a column named row_number in the query for each table, and join with the row_number to update  other columns of the addMasterAddress table.

    If you are planning on using the row_number function, that will not work.

    For starters, there is nothing that guarantees that the identity column is sequential and has no gaps.

    Secondly, row_number changes based upon the updates and inserts in the table.  It is not something that is built in to SQL Server, it is a function that calculates the values at runtime.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • I think the row_number it generate as long as gurantee each address row is only used once , address it is unique, it is OK. there is no specific order required for the new data  into the table, as long as each address is unique

    But you said the second point, "row_number changes based upon the updates and inserts in the table" , I am not sure for my case, for there is no partition, it is whole table as partition, so I think it should be OK

    for example

    SELECT *, row_number () OVER (ORDER BY AddressID) AS row_num

    FROM [dbo].[addMasterAddress]

    SELECT *, row_number () OVER (ORDER BY streetname, HouseNumber) AS row_num

    FROM [dbo].[addFakeAddress]

    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
    • This reply was modified 7 months, 3 weeks ago by  sqlfriend.
  • sqlfriend wrote:

    I think the row_number it generate as long as gurantee each address row is only used once , address it is unique, it is OK. there is no specific order required for the new data  into the table, as long as each address is unique

    But you said the second point, "row_number changes based upon the updates and inserts in the table" , I am not sure for my case, for there is no partition, it is whole table as partition, so I think it should be OK

    for example

    SELECT *, row_number () OVER (ORDER BY AddressID) AS row_num FROM [dbo].[addMasterAddress]

    SELECT *, row_number () OVER (ORDER BY streetname, HouseNumber) AS row_num FROM [dbo].[addFakeAddress]

    So, you have zero activity on the addMasterAddress table?  And the identity column on that table is sequential with no gaps?

    Again, how many records are in the addFakeAddress table?  Simply perform an update of the addMasterAddress table with this data.

    If it doesn't matter what rows get updated in the addMasterAddress table, your method will work.  If you are trying to match identity #1 with row_number() #1, then it may not work.

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • No, my addMasterAddress table could have gaps for AddressID, that is why I am not using it, I use generated row_number,

    like this:

    with cteMasterAddressRow as

    (SELECT *, row_number () OVER (ORDER BY AddressID) AS row_num FROM [dbo].[addMasterAddress])

    , cteFakeAddressRow as

    (SELECT *, row_number () OVER (ORDER BY streetname, HouseNumber) AS row_num FROM [dbo].[addFakeAddress])

    update  A

    set A.street=B.street, A.streetType=B.streetType, A.City=B.City.....

    from cteMasterAddressRow A join cteFakeAddressRow  B on A.row_number=B.row_number

    Quote you said:

    "Again,   Simply perform an update of the addMasterAddress table with this data"

    But how? There are two tables,what to join to update? how to update, can you say in detail?

     

    Thanks,

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply