[wish] "CREATE TABLE ... LIKE" in SQL Server 2005

28 Comments

• Another one I like from Informix land is when creating a stored procedure, you can specify the type of a parameter to be LIKE a column of a table.
  

• How bout ..
  
  
  
  sp_exec(sp_helptext(TABLENAME)) -- ;-)
  

• And that Copy Paste stuff u mentioned, doesn't work for Sql2k5 :(
  

• Adam -
  
  That sounds even better. What's the syntax?
  
  
  
  Sahil -
  
  sp_exec(sp_helptext(tablename)) would try to create the table with the same name again, which would just cause an error since the table already exists, right? CREATE TABLE LIKE lets you specify a new table name.
  

• You can do this to create a table with the same columns of another table.
  
  
  
  SELECT TOP 0 * INTO #mytable FROM anotherTable
  
  
  
  
  
  I can't think of a way to include indexes though :(
  
  

• In SQL 2005 Server Management
  Right Click on the table
  Script Table as
  Create to
  File or Clipboard
  

• I wish to create a table named on the fly, specifically a snapshot of an inventory table on a certain date.
  
  Using a variable..
  
  "CREATE TABLE @NewTableName" does not seem to work in SQL2K
  
  Help
  
  

• @Alan - Table variables are available in SQL2K. You use a different syntax for them, though. Rather than CREATE TABLE you use DECLARE @tablename TABLE:
  
  DECLARE @NewTableName TABLE
  (
  ID int,
  Sample int
  )
  
  More info here: http://www.odetocode.com/Articles/365.aspx
  

• hi,
  i want to create a table such that itz should have the table name as sydate.every day table created with the current date..any1 ving idea about this
  

• Don't know if this helps but it creates a table on the fly:
  
  declare @sql nvarchar(1000)
  declare @tablename varchar(100)
  
  set @tablename = 'TestTable'
  
  set @sql = 'CREATE TABLE ' + @tablename + '(
  Name varchar(100),
  Age int
  )'
  
  exec sp_executesql @sql
  
  Jan
  

• Select
  *
  Into from where =-300
  

• Select * Into #TEMPO from klients
  

• I am having real trouble created a table in an exisiting database on the fly.....
  
  the application i am created is a kinda of staff rotaing system, and i need to save each days rota in its own table.
  
  all i basically need to do is name the table with the date...can this be done?
  

• i got the answer thank u
  

• hey y've defined the lot of command. that's good to see and implement
  

• Ok but how to create table with inserting query that returns 10000 rows
  

• To bad Microsoft won't just add SQL99 functionality (although in their defense I've seen some of the SQL99 and what they have added is pretty broad... I guess once it gets turned over to the ISO it all gets vague)
  

• thx Jan - your example works perfectyly ;)
  

• Found nothing helpful!
  

• Dear Friends,
  Below SQL Server Query can help you to create a table form another one, without inserting any data. New table will have 0 rows still structure will be same as the Parent table.
  select * into test2 from tbltmpCostCenter where 1=2
  I Don’t this this is a Feasible one, Let me find it out for you.
  

• but it does not work with xml datatype in linked server
  

• This is now way to crate a tables in sql..
  

• To create a table on the fly with the date appended to the table name.
  
  DECLARE @SourceTableName NVARCHAR(255)
  DECLARE @TargetTableName NVARCHAR(255)
  DECLARE @Date NVARCHAR(8)
  DECLARE @NSQL NVARCHAR(1000)
  
  SET @Date = '20090323'
  SET @SourceTableName = 'dbo.SourceTableName'
  SET @TargetTableName = 'dbo.TargetTableName' + '_' + @Date
  
  SET @NSQL = 'SELECT *'
  SET @NSQL = @NSQL + ' INTO ' + @TargetTableName
  SET @NSQL = @NSQL + ' FROM ' + @SourceTableName + ' WHERE 1 = 0'
  
  EXECUTE sp_executesql @NSQL
  

• This will not copy primary key and foreign key related information
  
  Is there any way to copy all schema of table
  

• ,re: [wish] "CREATE TABLE ... LIKE" in SQL Server 2005,,,
  

• I usually find SQL Server to be inferior to MySQL in most things that I find useful.
  

• select top 0 * into new_table from old_table
  this will create a table with copying any rows
  
  select * into into new_table from old_table
  This will copy all the records along with structure
  

• scipt table as create to ... dont forget to change the name of table 'cos u'llhave object allready exists errorr... hit F5 ...
  this will create keys and everything.
  
  then select * into tbl1 from tbl2 ...
  
  enjoy 2 same tables :)