January 27, 2012 at 6:46 am
Hi,
I want to insert multiple number of rows in a table based on the number recorded in a column on another table.
Any ideas how to do this without using cursor sql?
Many thanks for all your help in advance.
Regards
January 27, 2012 at 6:58 am
The question is a litele vague.
It's like
table A
10,
20,
30
populates like
table B
10,
11,
12,
...
20,
21,
...
39
???
January 27, 2012 at 7:04 am
OK, what I;m trying to work out is if Table A holds a column with a number, I need to pick that number e.g. 2 and enter 2 rows in Table B only if the 2 rows haven't already been created for that particular ID.
Table A
Type Number
ABC 2
DEF 3
Table B should look like below
ABC
ABC
DEF
DEF
DEF
January 27, 2012 at 7:19 am
jbhatt2 (1/27/2012)
OK, what I;m trying to work out is if Table A holds a column with a number, I need to pick that number e.g. 2 and enter 2 rows in Table B only if the 2 rows haven't already been created for that particular ID.Table A
Type Number
ABC 2
DEF 3
Table B should look like below
ABC
ABC
DEF
DEF
DEF
Read Jeff Moden's article on tally tables. By joining to a tally table, you will be able to achieve what you describe above.
Take note though that it would require a triangular join, which is generally bad for performance.
January 27, 2012 at 7:50 am
any good...??
USE [tempdb]
GO
--- Look up "Tally Table.....http://www.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestData]') AND type in (N'U'))
DROP TABLE [dbo].[TestData]
GO
CREATE TABLE [dbo].[TestData](
[ID] [int] NULL,
[Data] [varchar](3) NULL )
INSERT INTO [dbo].[TestData]([ID], [Data])
SELECT 2, N'ABC' UNION ALL
SELECT 3, N'DEF'
SELECT TestData.Data
FROM TestData INNER JOIN
Tally ON TestData.ID >= Tally.N
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 27, 2012 at 8:03 am
Martin Schoombee (1/27/2012)
Take note though that it would require a triangular join, which is generally bad for performance.
I wouldn't classify this as a triangular join. A triangular joins assumes that there is some grouping and some order within that grouping on both tables. As you add tables to the grouping, you are forced into an ever increasing number of resulting rows.
It doesn't appear that this table has groupings, let alone an order within that grouping. The added rows should be independent of existing rows.
The performance problems of the triangular join are caused by the fact the number of rows returned is a function of n2. This problem appears to be a function of n.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2012 at 9:29 am
Like this?
create table tableA(id int, name char(3))
create table tableB(name char(3))
create table tally(id int)
GO
insert into tableA values (1,'ABC'),(2,'DEF'),(3,'GHI')
insert into tally values (1),(2),(3)
GO
select * from tableA
select * from tally
insert into tableB
select a.name
from tableA a
join tally t on t.id <= a.id
select b.* from tableB b order by b.name
GO
drop table tally
drop table tableA
drop table tableB
GO
remember to use indexes to achieve best performance.
January 27, 2012 at 9:50 am
Another approach, not saying its the best, but it has some interesting features, and won't insert new values if they are already there:
CREATE TABLE dbo.TableA
(
Code char(3) PRIMARY KEY,
Number bigint NOT NULL
);
GO
CREATE TABLE dbo.TableB
(
Code char(3) NOT NULL
);
CREATE VIEW dbo.TableBsummary
WITH SCHEMABINDING AS
SELECT
tb.Code,
RowsCount = COUNT_BIG(*)
FROM dbo.TableB AS tb
GROUP BY
tb.Code;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.TableBsummary (Code);
INSERT dbo.TableA
(Code, Number)
VALUES
('ABC', 2),
('DEF', 3);
GO
DECLARE @Numbers AS TABLE
(
number integer PRIMARY KEY
);
WITH
N1 AS (SELECT N1.n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (n)),
N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R),
Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS n FROM N4)
INSERT @Numbers
(number)
SELECT TOP (1000)
Numbers.n
FROM Numbers;
INSERT dbo.TableB
(Code)
SELECT
ToInsert.Code
FROM dbo.TableA AS ta
OUTER APPLY
(
SELECT
tb.RowsCount
FROM dbo.TableBsummary AS tb WITH (NOEXPAND, FORCESEEK)
WHERE
tb.Code = ta.Code
) AS tbs
CROSS APPLY
(
SELECT TOP (ta.Number - ISNULL(tbs.RowsCount, 0))
ta.Code
FROM @Numbers
) AS ToInsert;
SELECT * FROM dbo.TableA AS ta;
SELECT * FROM dbo.TableB AS tb;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 10:10 am
Thanks for the replies all..
But I'm not quite getting round to what I require.. basically I have a Table A, Table B as example below and Table C should read as below. The tally approach seems like a good approach but how do I join when I don't have any ID e.g. numbers in Table A equal to as on Table B?
Table A
Name
ABC
DEF
GHI
Table B
Number
2
Table C
ABC
ABC
DEF
DEF
GHI
GHI
January 27, 2012 at 10:13 am
jbhatt2 (1/27/2012)
But I'm not quite getting round to what I require.. basically I have a Table A, Table B as example below and Table C should read as below. The tally approach seems like a good approach but how do I join when I don't have any ID e.g. numbers in Table A equal to as on Table B?
Are you sure there isn't a Table D? E? F? ... 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 10:16 am
jbhatt2 (1/27/2012)
Thanks for the replies all..But I'm not quite getting round to what I require.. basically I have a Table A, Table B as example below and Table C should read as below. The tally approach seems like a good approach but how do I join when I don't have any ID e.g. numbers in Table A equal to as on Table B?
Table A
Name
ABC
DEF
GHI
Table B
Number
2
Table C
ABC
ABC
DEF
DEF
GHI
GHI
this is different from your earlier table design
OK, what I;m trying to work out is if Table A holds a column with a number, I need to pick that number e.g. 2 and enter 2 rows in Table B only if the 2 rows haven't already been created for that particular ID.
Table A
Type Number
ABC 2
DEF 3
which is correct?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 27, 2012 at 10:17 am
SQL Kiwi (1/27/2012)
jbhatt2 (1/27/2012)
But I'm not quite getting round to what I require.. basically I have a Table A, Table B as example below and Table C should read as below. The tally approach seems like a good approach but how do I join when I don't have any ID e.g. numbers in Table A equal to as on Table B?Are you sure there isn't a Table D? E? F? ... 😛
Heheh...yes...here comes with first "scope creep" 😛
January 27, 2012 at 10:28 am
OK just to clarify Table A holds unique ID's as below, Table B holds a number value example 2. So I need to insert 2 rows per unique ID in Table C if it goes not exist. I hope that clarifies and explains my issue that I'm trying to resolve.
Table A
Unique ID
ABC
DEF
GHI
Table B
Number
2
Table C
ABC
ABC
DEF
DEF
GHI
GHI
All help will be appreciated, thanks.
January 27, 2012 at 10:30 am
Well new table A and new table B can be CROSS JOINed to give old table A...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 10:35 am
Something like this?!
create table tableA(id int, name char(3))
create table tableB(id int, number int)
create table tableC(name char(3))
create table tally(number int)
GO
insert into tableA values (1,'ABC'),(2,'DEF'),(3,'GHI')
insert into tableB values (1,3),(2,5),(3,7)
insert into tableC values ('ABC'),('ABC')
insert into tally values (1),(2),(3),(4),(5),(6),(7) -- 7 = max number in tableB
GO
select * from tableA
select * from tableC
insert into tableC
select a.name
from tableA a
join tableB b on a.id = b.id
join tally t on t.number <= b.number - (select COUNT(*) from tableC where name = a.name)
select * from tableC order by name
GO
drop table tally
drop table tableA
drop table tableB
drop table tableC
GO
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply