Copy and past tables

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

Converting numeric data type to text data type

Hi,

I would like to convert a dollar amount ($1,500) to represent Fifteen
hundred dollars and 00/100 cents only for SQL reporting purposes. Is
this possible and can I incorporate the statement into an existing
left outer join query.

Thanks in advance,

Gavin[posted and mailed, please reply in news]

Gavin (gsegal@.mps.com) writes:
> I would like to convert a dollar amount ($1,500) to represent Fifteen
> hundred dollars and 00/100 cents only for SQL reporting purposes. Is
> this possible and can I incorporate the statement into an existing
> left outer join query.

If it is for reporting issues, it may be better to do this on client
level, but you could use a table to hold the various strings. I would
suggest that it will be simpler to implement, if you permit 1500 to be
rendered as "One thousand five hundred".

The table would look like this:

CREATE TABLE numberstrs (nr tinyint NOT NULL
CONSTRAINT ck_nr CHECK (nr BETWEEN 0 AND 99),
str varchar(23) NOT NULL,
CONSTRAINT pk_nr PRIMARY KEY (nr))
go
INSERT numberstrs (nr, str) VALUES (0, '')
INSERT numberstrs (nr, str) VALUES (1, 'one')
...
INSERT numberstrs (nr, str) VALUES (99, 'ninety-nine')

You would then use it as

SELECT CASE WHEN mil.str IS NOT NULL
THEN mil.str + ' millions and '
ELSE ''
END +
CASE WHEN hth.str IS NOT NULL
THEN mil.str + ' hundred '
ELSE ''
END +
CASE WHEN th.str IS NOT NULL
THEN th.str + ' thousand and '
ELSE ''
END +
CASE WHEN hun.str IS NOT NULL
THEN hun.str + ' hundred '
ELSE ''
END +
one.str + ' dollars ' +
ltrim(str((a.amt * 100) % 100)) + '/100 cents'
FROM tbl a
JOIN numberstrs one ON a.amt % 100 = nr
LEFT JOIN numberstrs hun ON (convert(int, a.amt) / 100) % 10 = nr
LEFT JOIN numberstrs th ON (convert(int, a.amt) / 1000) % 100 = nr
LEFT JOIN numberstrs hth ON (convert(int, a.amt) / 100000) % 10 = nr
LEFT JOIN numberstrs mil ON (convert(int, a.amt) / 1000000) % 100 = nr

This is something I made up, and I have not tested it. I don't think
the result will be that excellent for all numbers. For instance 101
would not come out pretty if you want it as "One hundred and one".
For even values like "One million" you need to add some logic.

Notice that there is a upper limit of 100 millions as I have written the
query. You would also have to arrange for the first letter in the
resulting string to be uppercase.

You mention how would incorporate into an existing query, but since I
don't see that query and don't where you want the value, I'm not taking
a stab at that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp