Results 1 to 13 of 13

Thread: Table Schema

  1. #1
    Join Date
    Sep 2003
    Posts
    65

    Unanswered: Table Schema

    Hey,

    I'm looking at creating a table in a stored procedure

    [declare @tempTable table]

    however, rather than then declaring the tables schema

    [declare @tempTable table (pkField IDENTITY (1,1), SomeOtherField varchar(50), ...)]

    I'd like it to simply use the schema of an already existing table (plus an additional column).

    Is there a way to do this without having to manually write the table schema?

    A simple example is:

    I have a table
    OriginalTable (idCol, NameCol, InfoCol)

    I'd like to create a temp instance of that table called tempTable which would have a final schema of

    tempTable (idCol, NameCol, InfoCol, myTempCol)

    The reason I'd like to do this using the schema is so that I don't need to update all my procedures in the future when we decide to add some more detail to the originaltable which needs to be selected as well.

    Thanks a lot for any help or direction you can provide.
    -Ashleigh
    -Ashleigh

  2. #2
    Join Date
    Jan 2004
    Posts
    9
    You can do this:

    select * into <your target table>
    from <your existing table>
    where 1=0

    that will create the schema without the data.
    then issue another alter table to add your column.

    I am not sure if there is a way to do everything in one shot though.

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    I added something into waspfish's query statement. The SQL statement is

    ==========================================
    SELECT existingTable.*, tempTable.* INTO targetTable
    FROM existingTable, tempTable
    WHERE 1 = 0
    ==========================================

    tempTable is the temporary table with the fields which do not exist in existingTable
    existingTable is the table existed in database
    targetTable is the new table which has all fields in existingTable plus the fields in tempTable

  4. #4
    Join Date
    Sep 2003
    Posts
    65
    Ah, I can't believe I didn't just try that.

    I've been playing with schema etc. It never occured to me that it would automatically create it, lol.

    Thanks guys,
    -Ashleigh

  5. #5
    Join Date
    Sep 2003
    Posts
    65
    Hey I just tried this out, and I got an error. How do I declare the table I want to put the items into?

    CREATE PROCEDURE company_ddlPages
    @CompanyID int
    AS

    declare @tempTable table
    (select * into @tempTable from Page where 0=1)
    GO

    it says incorrect syntax near select and neat @tempTable.

    Thanks for your help
    -Ashleigh

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Ashleigh
    Hey I just tried this out, and I got an error. How do I declare the table I want to put the items into?

    CREATE PROCEDURE company_ddlPages
    @CompanyID int
    AS

    declare @tempTable table
    (select * into @tempTable from Page where 0=1)
    GO

    it says incorrect syntax near select and neat @tempTable.

    Thanks for your help
    A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
    Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

    INSERT INTO table_variable EXEC stored_procedure

    SELECT select_list INTO table_variable statements.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Sep 2003
    Posts
    65
    Hey,

    so, if I wish to create a temp table for use in an SP, with the same column definitions as an already existing table, how can I go about doing that?

    Thanks
    -Ashleigh

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    CREATE PROCEDURE company_ddlPages
    @CompanyID int
    AS
    (select * into #tempTable from Page where 0=1)
    select * from #tempTable 
    GO
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Sep 2003
    Posts
    65
    Ah ok, great. Thanks a lot

    What does the # change about the tempTable variable? I've never seen the use of a # before.
    -Ashleigh

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Temporary Tables
    You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

    Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

    SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

    CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
    INSERT INTO #MyTempTable VALUES (1)

    If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
    A local temporary table created within a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger is called. However, if a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Sep 2003
    Posts
    65
    Ah ha,

    Thanks, that's very useful. I'll file that in my head somewhere, global tables are interesting to know about as well. I don't have any uses in mind, but next time something pops up I'll have that as another possible option as well.

    Thanks again
    -Ashleigh

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You dont need to file that anywhere .. jsut refer to the Holy Book [SQL Server Books Online ]
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Sep 2003
    Posts
    65
    Yea, the problem I have with SQL Server Books Online is, I don't have time to read through it "cover to cover". I normally spend 10-20 min searching it before I post my initial questions on this forum, however the problem I'm finding is, when I don't know how to do something, it's sometimes next to impossible to find, even using the search feature.

    That said, I am starting to find solutions in there more than I used to, hopefully work will slow down soon, and I can start learning what I'm doing before I have to do it, lol.

    Thanks again,
    -Ashleigh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •