February 16, 2022 at 6:40 pm
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
February 16, 2022 at 6:44 pm
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/
February 16, 2022 at 6:48 pm
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.
February 16, 2022 at 7:17 pm
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.
February 16, 2022 at 7:33 pm
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/
February 16, 2022 at 7:54 pm
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]
February 16, 2022 at 8:22 pm
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/
February 16, 2022 at 9:16 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply