SQLTeam.com | Weblogs | Forums

Insert using Recursive

Transact-SQL
#1

Hi all,

CREATE TABLE #TEMP
(
id int not null,
Accountname varchar(50)
)

CREATE TABLE #UsersEqualtoWebUser
(
id int identity(1,1),
AccountName varchar(100)
)

INSERT INTO #TEMP(id , Accountname)
SELECT 1 , 'ctest0067' union all
SELECT 2 , 'ctest01' union all
SELECT 3 , 'cTest0190' union all
SELECT 4 , 'bstacy11' union all
SELECT 5 , 'bstacy12' union all
SELECT 6 , 'ftest011' union all
SELECT 7 , 'ftest2' union all
SELECT 8 , 'ftest3' union all
SELECT 9 , 'ftest4' union all
SELECT 10 , 'ftestcad8'

Want to insert AccountName into table #UsersEqualtoWebUser
around x number of time and AccountName should be in same order as is in the #TEMP

let say x = 500 for now

thanks in advance

#2

DROP TABLE IF EXISTS #TEMP;
DROP TABLE IF EXISTS #UsersEqualtoWebUser;

CREATE TABLE #TEMP
(
id int not null,
Accountname varchar(50)
);

CREATE TABLE #UsersEqualtoWebUser
(
id int identity(1,1),
AccountName varchar(100)
);

INSERT INTO #TEMP(id , Accountname)
SELECT 1 , 'ctest0067' union all
SELECT 2 , 'ctest01' union all
SELECT 3 , 'cTest0190' union all
SELECT 4 , 'bstacy11' union all
SELECT 5 , 'bstacy12' union all
SELECT 6 , 'ftest011' union all
SELECT 7 , 'ftest2' union all
SELECT 8 , 'ftest3' union all
SELECT 9 , 'ftest4' union all
SELECT 10 , 'ftestcad8';

GO

SET IDENTITY_INSERT #UsersEqualtoWebUser ON;

GO

INSERT INTO #UsersEqualtoWebUser (id, AccountName)
SELECT id, Accountname FROM #temp;

GO

SET IDENTITY_INSERT #UsersEqualtoWebUser OFF;

GO

INSERT INTO #UsersEqualtoWebUser(AccountName)
SELECT 'ftestcad9';

SELECT * FROM #UsersEqualtoWebUser

#3 

TRUNCATE TABLE #UsersEqualtoWebUser

DECLARE @te_joins_needed int
DECLARE @x int
SET @x = 495 /*or whatever*/
SET @te_joins_needed = (SELECT @x / COUNT(*) FROM #TEMP)

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
)
INSERT INTO #UsersEqualtoWebUser ( AccountName )
SELECT te.AccountName
FROM #TEMP te
INNER JOIN cte_tally10K ta ON ta.number BETWEEN 1 AND @te_joins_needed
ORDER BY ta.number, te.id

INSERT INTO #UsersEqualtoWebUser ( AccountName )
SELECT TOP ( @x % @te_joins_needed ) te.AccountName
FROM #TEMP te
ORDER BY te.id

SELECT COUNT(*) AS total_rows_inserted FROM #UsersEqualToWebUser
#4

@ScottPletcher thanks its is as expected.