July 26, 2022 at 6:25 pm
I have a case where in non prod environment, i will have to replicate existing data multiple times with a increasing PK value. Can anyone please help achieving this without loops or cursors.
For example
CREATE TABLE Table_A (Id1 Numeric(5,0) not null PRIMARY KEY,Name1 Varchar(20) null,date1 datetime2(7) null)
go
INSERT INTO Table_A VALUES('1','A1',GETDATE())
INSERT INTO Table_A VALUES('2','A2',GETDATE())
INSERT INTO Table_A VALUES('3','A3',GETDATE())
INSERT INTO Table_A VALUES('4','A4',GETDATE())
INSERT INTO Table_A VALUES('5','A5',GETDATE())
INSERT INTO Table_A VALUES('6','A6',GETDATE())
INSERT INTO Table_A VALUES('7','A7',GETDATE())
INSERT INTO Table_A VALUES('8','A8',GETDATE())
INSERT INTO Table_A VALUES('9','A9',GETDATE())
GO
SELECT * FROM Table_A
In the above example, i have 9 records in the table. These 9 records needs to replicated say 10 times with PK value increasing.
Not sure how to use tally table in this context? Please help.
Reagrds
Jus
July 26, 2022 at 7:34 pm
How about something like this:
With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select checksum(row_number() Over(Order By @@spid))
From t t1, t t2, t t3
)
Insert Into TableA (Id1, Name1, date1)
Select Top (90)
it.n
, concat(char(65 + (it.n / 10)), (it.n % 10))
, getdate()
From iTally it
Where it.n > 9;
This will get increment the Id1 column by 1 for every row - and calculates the Name1 for each group of 10 rows.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 26, 2022 at 7:47 pm
This looks amazing. But i just want to replicate same data for non pk columns.
July 26, 2022 at 8:07 pm
Are those values actually 'A1', 'A2', ... Or was that just for the sample data? If just for the sample data - then you are going to need a source for 10, 20, 30, etc...
You can take the same code above - join to TableA on ta.Id1 = it.n % 10 and use the matching value from TableA.
With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select checksum(row_number() Over(Order By @@spid))
From t t1, t t2, t t3
)
Insert Into TableA (Id1, Name1, date1)
Select Top (90)
it.n
, coalesce(ta.Name1, 'Name0')
, getdate()
From iTally it
Left Join TableA ta On ta.Id1 = it.n % 10
Where it.n > 9;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 26, 2022 at 8:24 pm
Wonderful. Thank a ton Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply