Using the While loop to create temp tables based on a list table

  • [

    ...

    Im guessing Im going to have to go for adding them into a permanent table rather than a temp if IDENTITY go this way?

    That is exactly what I've been talking about. To make it a bit nicer, you can create this tables not in default "dbo" (or other) schema, but in the dedicated scheme.

    Juts create a separate schema, call it, let say "work"

    CREATE SCHEMA work

    GO

    Then you can create your "work" tables there (use the code sample for creating temp tables). Don't forget that your dynamic code will need to check if the table already exist and drop it if so:

    ...

    SELECT @sql =

    'IF OBJECT_ID(''work.Example_Table_' + Code + ''') IS NOT NULL DROP TABLE work.Example_Table_' + Code + ';

    SELECT * INTO work.Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''

    FROM

    ...

    After, the execution of such dynamic sql work.Example_Table_ will be created and accessible.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hiya again,

    Right.... Im getting there but Id made the initial ask simpler than it should be.

    Within my SQL there is a source table and Destination table. Both hold slightly different codes to link to so Im imagining something like this...

    But again this is completely wrong because now there are two columns I'm not sure how to set this up

    create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(20))

    insert into #SSC

    select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL

    select 'B', 'TOTAL_B' union all

    select 'P', 'TOTAL_P' union all

    select 'V', 'TOTAL_V' union all

    select 'X', 'TOTAL_X' union all

    select 'Y', 'TOTAL_Y' union all

    select 'Z', 'TOTAL_Z' union all

    Select '!', 'TOTAL_Exclamation_Mark' union all

    Select '.', 'TOTAL_Full_Stop' union all

    Select '(', 'TOTAL_Open_Bracket'

    DECLARE @sql VARCHAR(MAX)

    DECLARE @looper int = 1

    WHILE @looper <= (SELECT MAX(ID) from #SSC)

    BEGIN

    SET @sql = ''

    SELECT @sql = 'SELECT * INTO #Example_Table_' + Dest_Code + ' from main_table Where Source_Code Like ''' + Dest_Code + ''''

    FROM #SSC WHERE ID = @looper

    --Change to Exec when you want to execute rather than print

    PRINT(@SQL)

    SET @looper = @looper + 1

    END

    Im getting this error when i run it

    An explicit value for the identity column in table '#SSC' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  • I do like the idea of creating a schema for all the tables if I have to create them all as permanent tables.

  • Abu Dina (2/25/2013)


    Okay fair enough then just make the tempt table into a global temp table and problem is sorted.

    No, this will not be exactly enough to make it robust...

    If you simply change temp table into global temp table, your code (for the same table ) will only be able to run once in a single session. Also, it will have trouble to be executed by more than one session simultaneously. You will need to make sure that global temp table does not already exist before creating it - exactly the same way as you would need if using permanent table.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • There are few things here.

    1. Your code example has a small visible bug:

    You cannot insert 'TOTAL_Exclamation_Mark' value into your #SSC table as it's longer than maximum allowed by your column definition of Dest_Code varchar(20)

    2. Executing of the code posted does not result with

    "An explicit value for the identity column in table '#SSC' can only be specified when a column list is used and IDENTITY_INSERT is ON." error as there is nothing in your posted code inserts into identity column. I guess it's happen due to another code part or variation which you did not include in your post.

    3. For some reason, I have a feeling that your design is not quite appropriate to the task you are trying to do. But, we will need to have more information and details on what you are really trying to achieve in order to advise you further.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • I guess I just don't understand why you need separate tables for each code to begin with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Getting there!!!

    create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(30))

    insert into #SSC

    select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL

    select 'B', 'TOTAL_B' union all

    select 'P', 'TOTAL_P' union all

    select 'V', 'TOTAL_V' union all

    select 'X', 'TOTAL_X' union all

    select 'Y', 'TOTAL_Y' union all

    select 'Z', 'TOTAL_Z' union all

    Select '!', 'TOTAL_Exclamation_Mark' union all

    Select '.', 'TOTAL_Full_Stop' union all

    Select '(', 'TOTAL_Open_Bracket'

    DECLARE @sql VARCHAR(MAX)

    DECLARE @looper int = 1

    WHILE @looper <= (SELECT MAX(ID) from #SSC)

    BEGIN

    SET @sql = ''

    SELECT @sql = 'SELECT * INTO #Example_Table_' + Dest_Code + ' from main_table Where Source_Code Like ''' + Dest_Code + ''''

    +'And source_code like ''' + Source_Code + ''''

    FROM #SSC WHERE ID = @looper

    --Change to Exec when you want to execute rather than print

    PRINT(@SQL)

    SET @looper = @looper + 1

    END

  • I do for things happening later in the process.

    It would take ages to explain that bit and I don't need to because Im Ok with that part of it.

    Also I just want to know how to do this for lots of other things

  • Jeff Moden (2/25/2013)


    I guess I just don't understand why you need separate tables for each code to begin with.

    That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...

    :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Well its now printing the correct details for my SQL.

    If I can get it to excecute I will be very happy indeed

    create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(30))

    insert into #SSC

    select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL

    select 'B', 'TOTAL_B' union all

    select 'P', 'TOTAL_P' union all

    select 'V', 'TOTAL_V' union all

    select 'X', 'TOTAL_X' union all

    select 'Y', 'TOTAL_Y' union all

    select 'Z', 'TOTAL_Z' union all

    Select '!', 'TOTAL_Exclamation_Mark' union all

    Select '.', 'TOTAL_Full_Stop' union all

    Select '(', 'TOTAL_Open_Bracket'

    DECLARE @sql VARCHAR(MAX)

    DECLARE @looper int = 1

    WHILE @looper <= (SELECT MAX(ID) from #SSC)

    BEGIN

    SET @sql = ''

    SELECT @sql = 'SELECT fct.AC_YEAR, fct.AC_TERM, fct.MAX_Attend_Date, fct.SysTalk_ID,

    fct.Sessional_Attendance_tblschool, fct.'+Dest_Code+' ,ISNULL(src.TOTAL,0)AS Total,

    CASE WHEN fct.'+Dest_Code+' = ISNULL(src.TOTAL,0) THEN ''No change''

    WHEN fct.'+Dest_Code+' < ISNULL(src.TOTAL,0) THEN ''dest less than source''

    WHEN fct.'+Dest_Code+' > ISNULL(src.TOTAL,0) THEN ''Source less than dest''

    ELSE ''NA'' END AS DESCR

    INTO #'+Dest_Code+'_Differences

    FROM

    (SELECT fct.AC_YEAR, fct.AC_TERM, fct.MAX_Attend_Date, fct.SysTalk_ID,

    fct.Sessional_Attendance_tblschool, fct.'+Dest_Code+'

    FROM fact.Sessional_Attendance_By_Pupil fct)fct

    LEFT OUTER JOIN

    (SELECT src.AC_YEAR, src.AC_TERM, src.TBLPUPIL,src.tblSchool,SUM(src.TOTAL)AS TOTAL

    FROM

    (SELECT ''AM'' AS Sess, dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool, COUNT(*)AS TOTAL

    FROM CAYA_Sessional.dbo.TBLPUPIL_ATTEND_SESSIONS_tables a

    LEFT OUTER JOIN (SELECT date, AC_YEAR, AC_TERM FROM dim.Date_Time) dt ON a.AttendDate = dt.Date

    WHERE a.CODE_AM = '+Source_Code+'

    GROUP BY dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool

    UNION

    SELECT ''PM'' AS Sess, dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool, COUNT(*)AS TOTAL

    FROM CAYA_Sessional.dbo.TBLPUPIL_ATTEND_SESSIONS_tables a

    LEFT OUTER JOIN (SELECT date, AC_YEAR, AC_TERM FROM dim.Date_Time) dt ON a.AttendDate = dt.Date

    WHERE a.CODE_PM = '+Source_Code+'

    GROUP BY dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool)src

    GROUP BY src.AC_YEAR, src.AC_TERM, src.TBLPUPIL,src.tblSchool)src

    ON fct.AC_Year = src.AC_YEAR AND fct.AC_TERM = src.AC_TERM AND fct.SYSTALK_ID = src.tblPupil

    AND fct.Sessional_Attendance_tblschool = src.tblSchool

    WHERE CASE WHEN fct.'+Dest_Code+' = ISNULL(src.TOTAL,0) THEN ''No change''

    WHEN fct.'+Dest_Code+' < ISNULL(src.TOTAL,0) THEN ''dest less than source''

    WHEN fct.'+Dest_Code+' > ISNULL(src.TOTAL,0) THEN ''Source less than dest''

    ELSE ''NA'' END <> ''No change'' '

    FROM #SSC WHERE ID = @looper

    --Change to Exec when you want to execute rather than print

    PRINT(@SQL)

    SET @looper = @looper + 1

    END

  • Eugene Elutin (2/25/2013)


    Jeff Moden (2/25/2013)


    I guess I just don't understand why you need separate tables for each code to begin with.

    That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...

    :hehe:

    Sorry?

  • Debbie Edwards (2/25/2013)


    Eugene Elutin (2/25/2013)


    Jeff Moden (2/25/2013)


    I guess I just don't understand why you need separate tables for each code to begin with.

    That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...

    :hehe:

    Sorry?

    Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.

    From information you have posted so far, it's hard to see any reasons for doing so.

    If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .

    Why not just use one single table? Add a "code" column into it to differentiate between its data sources...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (2/25/2013)


    Debbie Edwards (2/25/2013)


    Eugene Elutin (2/25/2013)


    Jeff Moden (2/25/2013)


    I guess I just don't understand why you need separate tables for each code to begin with.

    That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...

    :hehe:

    Sorry?

    Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.

    From information you have posted so far, it's hard to see any reasons for doing so.

    If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .

    Why not just use one single table? Add a "code" column into it to differentiate between its data sources...

    I don't feel its its important to waste peoples time on everything else that is being done and the history behind it. And there is a lot of history behind this one.

    This was simply a query regarding using a loop to create different tables. I wanted to add it to my library of things I can do.

  • Debbie Edwards (2/25/2013)


    I do for things happening later in the process.

    It would take ages to explain that bit and I don't need to because Im Ok with that part of it.

    Also I just want to know how to do this for lots of other things

    Understood. I know you don't need to explain it. I was just curious and thought you might have the time to explain.

    The only time I ever did such things was to build whole databases based on dates and that's only because the folks I was working for wanted it that way. They figured 4 million rows per day was enough for 1 database. I disagreed but it wasn't my call.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Debbie Edwards (2/25/2013)


    Eugene Elutin (2/25/2013)


    Debbie Edwards (2/25/2013)


    Eugene Elutin (2/25/2013)


    Jeff Moden (2/25/2013)


    I guess I just don't understand why you need separate tables for each code to begin with.

    That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...

    :hehe:

    Sorry?

    Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.

    From information you have posted so far, it's hard to see any reasons for doing so.

    If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .

    Why not just use one single table? Add a "code" column into it to differentiate between its data sources...

    I don't feel its its important to waste peoples time on everything else that is being done and the history behind it. And there is a lot of history behind this one.

    This was simply a query regarding using a loop to create different tables. I wanted to add it to my library of things I can do.

    Gosh. It actually is important. It's called "sharing information". 1) You might actually get a better solution if you explained the purpose. Even if you're not interested in a possible better solution, then consider that 2) a whole lot of people have never done such a thing never mind knowing a good reason why to "mess data up" like this. I put that in quotes because some folks like doing a poor man's version of partitioning instead of losing some of the capabilities that partitioning causes.

    Remember this is a 2 way street. People are interested in these types of problems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 30 total)

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