Goodday
Can anyone help please.
I have made a database and need to duplicate the tables to represent new catogaries. The definitions in the tables will stay the same , just the table names will change.
Is there a way to copy and past / duplicate tables without retyping all the definitions each time.
Thanks
Rob
hi Rob,
robhare wrote:
Goodday
Can anyone help please.
I have made a database and need to duplicate the tables to represent new catogaries. The definitions in the tables will stay the same , just the table names will change.
this does not seems to be a "good design" pattern... you should normalize your design where your main "object"' references another table for the relative categories... something like
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.Categories (
Id int NOT NULL PRIMARY KEY,
Description varchar(10) NOT NULL
);
CREATE TABLE dbo.myObjects (
Id int NOT NULL PRIMARY KEY,
Description varchar(10) NOT NULL,
IdCategory int NOT NULL
CONSTRAINT fk_myObject$has$category
FOREIGN KEY
REFERENCES dbo.Categories (Id)
);
GO
INSERT INTO dbo.Categories VALUES ( 1 , 'cat1');
INSERT INTO dbo.Categories VALUES ( 2 , 'cat2');
INSERT INTO dbo.myObjects VALUES ( 1 , 'a', 1 );
INSERT INTO dbo.myObjects VALUES ( 2 , 'b', 1 );
INSERT INTO dbo.myObjects VALUES ( 3 , 'c', 2 );
SELECT o.Id, o.Description, c.Id, c.Description
FROM dbo.myObjects o
INNER JOIN dbo.Categories c ON c.Id = o.IdCategory;
GO
DROP TABLE dbo.myObjects, dbo.Categories;
--<-
Id Description cat_id cat_description
-- -- --
1 a 1 cat1
2 b 1 cat1
3 c 2 cat2
when you require "additional" categories, you just have to add a new entry in the relative table...
Is there a way to copy and past / duplicate tables without retyping all the definitions each time.
if you really want to, just "script" the object Data Definition Language out with SQL Server Management Studio Express... modify the object's name as long as all the eventual constraints and execute the modified script..
regards
