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.
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
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
declare @tempTable table
(select * into @tempTable from Page where 0=1)
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.
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:
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.
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.