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

  • does this help you..??

    No doubt there are other methods.

    use [tempdb]

    Go

    IF OBJECT_ID('tempdb..Tally', 'U') IS NOT NULL DROP TABLE tempdb..Tally ;

    IF OBJECT_ID('tempdb..TableA', 'U') IS NOT NULL DROP TABLE tempdb..TableA ;

    IF OBJECT_ID('tempdb..TableB', 'U') IS NOT NULL DROP TABLE tempdb..TableB ;

    IF OBJECT_ID('tempdb..TableC', 'U') IS NOT NULL DROP TABLE tempdb..TableC ;

    SELECT TOP 100 IDENTITY(INT, 1, 1) AS N

    INTO dbo.Tally

    FROM sys.all_columns ac1

    CREATE TABLE [dbo].[TableA](

    [UniqueID] [varchar](3) NULL )

    INSERT INTO [dbo].[TableA]([UniqueID])

    SELECT 'ABC' UNION ALL

    SELECT 'DEF' UNION ALL

    SELECT 'GHI' UNION ALL

    SELECT 'JKL'

    CREATE TABLE [dbo].[TableB](

    [NumberID] [int] )

    INSERT INTO [dbo].[TableB]([NumberID])

    SELECT 5

    CREATE TABLE [dbo].[TableC](

    [UniqueID] [varchar](3) NULL )

    INSERT INTO [dbo].[TableC]([UniqueID])

    SELECT 'JKL'

    --SELECT * from Tablec

    INSERT INTO TableC (UniqueID)

    (

    SELECT A.UniqueID

    FROM TableA AS A INNER JOIN

    TableC C ON A.UniqueID <> C.UniqueID

    CROSS JOIN (SELECT T.N

    FROM dbo.TableB B

    INNER JOIN dbo.Tally T ON B.NumberID >= T.N) DT

    )

    SELECT * FROM TableC

    ORDER BY UniqueID

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply