Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 23rd, 2004, 06:25 AM
Authorized User
 
Join Date: Jul 2004
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create a Table From Another Table

How would I go about creating a table from another table using Enterprise Manager? My get-my-feet-wet assignment is to create table tblSetHistory from table tblSet, structure only, then create a trigger to insert a tblSet row into tblSetHistory when the WFState column in this tblSet row is updated. I'm stuck on the first part of this assignment.

I right-clicked "Console Root - Microsoft SQL Servers - (local) (Windows NT) - Databases - smallcha - Tables -tblSet". This shows "New Table..., Design Table, Open Table, All Tasks, Cut, Copy, Delete, Rename, Properties, Help". Then I thought I should be able to right-click "Tables", left-click "Paste", then rename the copy of tblSet, but right-clicking "Tables" doesn't show "Paste" - it only shows "New Table..., All Tasks, View, New Window from Here, Refresh, Export List, Help".

I even tried left-clicking "New Table", but apparently this is only for starting a table from scratch.
 
Old July 23rd, 2004, 07:15 AM
Authorized User
 
Join Date: Jun 2003
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right-Click the original table. ->All Tasks->Generate SQL Script...
From here you can create a SQL-script that you may run in Analyzer. Just change the name of the table in the script to what you want...

 
Old July 23rd, 2004, 08:59 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Do you have to do this in Enterprise Manager? To "duplicate" a table's structure to a new table you can just execute this simple query:

SELECT * INTO MyNewTable FROM MyExistingTable WHERE 1=2

This creates MyNewTable with the structure of MyExistingTable but without any data (WHERE 1=2).
 
Old July 24th, 2004, 05:45 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 336
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alyeng2000
Default

or just from QA generate table create script and rename the table name

Ahmed Ali
Software Developer
 
Old July 24th, 2004, 11:00 AM
Authorized User
 
Join Date: Jul 2004
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you, Gert, alyeng2000, and planoie, for all your suggestions. I ended up left-clicking start, enterprise manager, console root - microsoft sql servers - (local) (windows nt) - databases - smallcha, Tools, and SQL Query Analyzer, entering SELECT * INTO tblSetHistory FROM tblSet WHERE 1=2, right-clicked the box, and left-clicking Execute, which gave me my new table.

Now, on to problems with the second part of my assignment, which I will post in "SQL language".





Similar Threads
Thread Thread Starter Forum Replies Last Post
Create generic XSL Template to create table Venkatachalapathy XSLT 5 March 11th, 2008 07:49 AM
Create table from another table using ADO Kaustav Pro VB Databases 0 October 15th, 2005 05:44 AM
Why can't create table? Edward King Oracle 2 July 20th, 2005 04:11 AM
Create a table jmss66 Classic ASP Basics 1 July 19th, 2005 12:50 PM
size of table (type table is table of number) MikoMax Oracle 1 November 19th, 2003 03:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.