CODEWITHSUNDEEP
sqlsql-serversql-server-2008schema
January Migalbin
January Migalbin

Reputation: 11

SQL Server 2008: copy table structure, and schema

thanks for your time. i edited my script, ran it, and still got this name: srp.dbo.gstDataCutover. i used to be able to do this easily with MSSQL2005. we've recently upgraded to 2008. and i dont remember doing it any other way...

Hi,

I'm trying to copy a table structure (columns, datatypes, schema) into a new table to have the same schema and structure, using the sql code below.

SELECT     dbo.gstData.*
INTO            [dbo.gstDataCutover]
FROM         dbo.gstData
WHERE      dbo.gstData.gstID < 1

My problem is, when i run this script the new table dbo.gstDataCutover is named as "dbo.gstDataCutover" but the schema is defaulted to the system schema ("srp"), which is actually srp.[dbo.gstDataCutover].

I want to copy both the structure and the schema.

Thanks!

Upvotes: 1

Views: 5112

Answers (2)

bvdb
bvdb

Reputation: 24770

You can download the community edition of Visual Studio, which has features for comparing schemas as well as data. It will list the differences and allows you to select a set of changes, for which it will generate an update-script.

enter image description here

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332731

Without any periods, the hard brackets indicate table name -- it's including the "dbo." in your example as part of the table name.

If you want the table created in the dbo schema:

SELECT t.*
  INTO dbo.gstDataCutover
  FROM dbo.gstData t
 WHERE t.gstID < 1

Likewise, if you want the table created in the srp schema:

SELECT t.*
  INTO srp.gstDataCutover
  FROM dbo.gstData t
 WHERE t.gstID < 1

The table name doesn't have any unusual characters, so there's no need to use hard brackets...

Upvotes: 4

Related Questions