Jon Galloway

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

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

i want to create all the tables of a database with different databasename in Sql Server 2005

Select

*

Into  <NewtableName> from <OldTable> where <ColumnName>=-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?

Really I felt very happy with instant duplication of my existing table.  Thank you very much.

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 ;)

Pingback from  CREATE TABLE LIKE &#8230; in MS SQL Server &laquo;  Prutsoft

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