INSERT NUMBER OF ROWS BASED ON NUMBER VALUE IN ANOTHER TABLE COLUMN

  • 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

  • The question is a litele vague.

    It's like

    table A

    10,

    20,

    30

    populates like

    table B

    10,

    11,

    12,

    ...

    20,

    21,

    ...

    39

    ???

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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;

  • 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

  • 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? ... 😛

  • 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

  • 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" 😛

  • 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.

  • Well new table A and new table B can be CROSS JOINed to give old table A...

  • 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