How to create dynamic tables and database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to create dynamic tables and database

Author

Topic

Dimis
Starting Member

11 Posts

Posted - 2011-05-11 : 17:56:05

I would like to learn how to create dynamic tables and database in sql
server management server.Thanks.

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 18:08:07

By dynamic, do you mean the names would be dynamic or something else? Please elaborate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-11 : 18:31:48

The problem I have is

An executable SQL script file containing commented stored procedures to create the physical tables, together with all constraints, ie primary and foreign keys, null, check, unique and default constraints, plus suggested indexes.Thanks

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 18:37:08

That doesn't make it any more clear. It's actually less clear now.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-11 : 19:02:26

I would like to learn how to create six tables and insert into them at least 10 rows of suitable data.I will do this with query or external executable SQL script .Thanks

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 19:10:54

CREATE TABLE Table1 (Column1 int, Column2 varchar(50)
CREATE TABLE Table2 ...
...

INSERT INTO Table1 ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-11 : 19:25:23

First can I use an executable SQL script file and how.
Second I use
CREATE DATABASE MyDatabase;
and there is no display in databases under sql server.

Msg 1801, Level 16, State 3, Line 2
Database 'MyDatabase' already exists. Choose a different database name.

Dimi

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-11 : 19:26:58

Create a sql script file (somefilename.sql), or several files. Use SQLCMD to execute the scripts against the target system.

To control this, you could use a powershell script to loop through the files and call out to SQLCMD. Or, you can implement the SQL Server cmdlets and use those to call out.

Lots of methods available.

Jeff

akonmask

5 Posts

Posted - 2011-05-13 : 17:31:30

Follow the steps to create dynamic table into database,
1) Create an HTML list, using u, Li, and a elements within the individual list elements.
2) Signal that more information is available.
3) Define the style rules for hiding and displaying list items.
4) Add inline events.

Dimis
Starting Member

11 Posts

Posted - 2011-05-16 : 20:30:33

I would like help because has the sql server the following display
Msg 208, Level 16, State 1, Line 22
Invalid object name 'Loan'.
Thanks

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 22:14:13

You'll need to post your script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shangbaby
Starting Member

3 Posts

Posted - 2011-05-17 : 08:27:49

You can also have a "create table" sql statement run in SSIS. Are you familiar with SSIS?

Shangz R Brown
SQL DATABASE DEVELOPER
SSRS, SSIS, T-SQL, BIDS

Dimis
Starting Member

11 Posts

Posted - 2011-05-17 : 09:39:54

This is the code.Thanks
/*
This is the first procedure
*/

IF OBJECT_ID('Customer') IS NOT NULL
IF OBJECT_ID('Loan') IS NOT NULL
IF OBJECT_ID('Copy') IS NOT NULL
IF OBJECT_ID('DVD') IS NOT NULL
IF OBJECT_ID('TopTenRentalLastWeek') IS NOT NULL
IF OBJECT_ID('TopTenRentalThisWeek') IS NOT NULL

DROP TABLE Customer

CREATE TABLE Customer
(Customerid CHAR(9) NOT NULL CHECK (Customerid>0),
Customername VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
Address VARCHAR(15) NULL,
Email CHAR(20) NULL,
Telno VARCHAR(12) NULL,
CONSTRAINT CustomerPK PRIMARY KEY (Customerid))
CREATE TABLE Loan
(Loanid CHAR(9) NOT NULL CHECK (Loanid>0),
Customerid CHAR(9) NOT NULL,
DOBstart DATETIME NULL,
DOBend DATETIME NULL,
Copyid CHAR(9) NOT NULL,
CONSTRAINT LoanPK PRIMARY KEY (Loanid),
FOREIGN KEY (Customerid) REFERENCES Customer(Customerid),
FOREIGN KEY (Copyid) REFERENCES Copy(Copyid),
UNIQUE (Loanid))
CREATE TABLE Copy
(Copyid CHAR(9) NOT NULL CHECK (Copyid>0),
Quantity VARCHAR(12) NULL,
Dvdid CHAR(9) NOT NULL,
Price VARCHAR(12) NULL,
CONSTRAINT CopyPK PRIMARY KEY (Copyid),
FOREIGN KEY (Dvdid) REFERENCES DVD(Dvdid),
UNIQUE (Copyid))
CREATE TABLE DVD
(Dvdid CHAR(9) NOT NULL CHECK (Dvdid>0),
Dvdname VARCHAR(15) NOT NULL,
TopTenRentalLastno CHAR(10) NOT NULL,
TopTenRentalThisno CHAR(10) NOT NULL,
CONSTRAINT DVDPK PRIMARY KEY (Dvdid),
FOREIGN KEY (TopTenRentalLastno) REFERENCES TopTenRentalLastWeek(TopTenRentalLastno),
FOREIGN KEY (TopTenRentalThisno) REFERENCES TopTenRentalThisWeek(TopTenRentalThisno),
UNIQUE (Dvdid))
CREATE TABLE TopTenRentalLastWeek
(TopTenRentalLastno CHAR(10) NOT NULL CHECK (TopTenRentalLastno>0),
Dvdid CHAR(9) NOT NULL,
CONSTRAINT TopTenRentalLastWeekPK PRIMARY KEY (TopTenRentalLastno),
UNIQUE (TopTenRentalLastno))
CREATE TABLE TopTenRentalThisWeek
(TopTenRentalThisno CHAR(10) NOT NULL CHECK (TopTenRentalThisno>0),
Dvdid CHAR(9) NOT NULL,
CONSTRAINT TopTenRentalThisWeekPK PRIMARY KEY (TopTenRentalThisno),
UNIQUE (TopTenRentalThisno))

GO

INSERT INTO Customer VALUES
('102345','Freda', 'Bloggs','Nikiforou-7','HND102345@tees.ac.uk', '01642-345678')
INSERT INTO Customer VALUES
('103456','Greta', 'Davies','Kostou-5','HND103456@tees.ac.uk', '01642-456789')
INSERT INTO Customer VALUES
('205112','Jayne', 'Orrells','Panagou-8','BSC205112@tees.ac.uk', '01642-567890')
INSERT INTO Customer VALUES
('204113','Jack', 'Lee','Fousts-6','BSC204113@tees.ac.uk', '01642-334655')
INSERT INTO Customer VALUES
('206114','Ralph', 'Harries','Jakou-3','BSC206114@tees.ac.uk', '01642-344509')
INSERT INTO Customer VALUES
('207233','Sue', 'Peters','Patou-6','BSC207233@tees.ac.uk', '01642-945778')
INSERT INTO Customer VALUES
('205112','Jayne', 'Orrells','Patsouka-5','BSC205112@tees.ac.uk', '01642-567899')
INSERT INTO Customer VALUES
('204113','Jack', 'Lee','Pat-4','BSC204113@tees.ac.uk', '01642-334664')
INSERT INTO Customer VALUES
('206114','Ralph', 'Harries','Papou-12','BSC206114@tees.ac.uk', '01642-344544')
INSERT INTO Customer VALUES
('207233','Sue', 'Peters','Patatouka-6','BSC207233@tees.ac.uk', '01642-945712')
INSERT INTO Loan VALUES
('1001','American_Black','102345','02/01/2011','02/04/2011','101')
INSERT INTO Loan VALUES
('1002','103456','02/01/2011','02/04/2011','102')
INSERT INTO Loan VALUES
('1003','205112','02/01/2011','02/04/2011','103')
INSERT INTO Loan VALUES
('1004','204113','02/01/2011','02/05/2011','104')
INSERT INTO Loan VALUES
('1005','206114','02/02/2011','02/06/2011','105')
INSERT INTO Loan VALUES
('1006','207233','02/03/2011','02/08/2011','106')
INSERT INTO Loan VALUES
('1007','205112','02/03/2011','02/05/2011','107')
INSERT INTO Loan VALUES
('1008','204113','02/03/2011','02/06/2011','108')
INSERT INTO Loan VALUES
('1009','206114','02/03/2011','02/06/2011','109')
INSERT INTO Loan VALUES
('1010','207233','02/01/2011','02/06/2011','110')
INSERT INTO Copy VALUES
('101','30', '500','20')
INSERT INTO Copy VALUES
('102','30', '501','25')
INSERT INTO Copy VALUES
('103','30', '502','25')
INSERT INTO Copy VALUES
('104','30', '503','25')
INSERT INTO Copy VALUES
('105','30', '504','25')
INSERT INTO Copy VALUES
('106','30', '505','25')
INSERT INTO Copy VALUES
('107','40', '506','25')
INSERT INTO Copy VALUES
('108','40', '507','25')
INSERT INTO Copy VALUES
('109','40', '508','25')
INSERT INTO Copy VALUES
('110','40', '509','25')
INSERT INTO DVD VALUES
('500','American_Black','1')
INSERT INTO DVD VALUES
('501','French_horn','3')
INSERT INTO DVD VALUES
('502','Harmonica','4')
INSERT INTO DVD VALUES
('503','Bobolink','10')
INSERT INTO DVD VALUES
('504','Nightjar','9')
INSERT INTO DVD VALUES
('505','King_Eider','6')
INSERT INTO DVD VALUES
('506','Killdeer','8')
INSERT INTO DVD VALUES
('507','Jews','7')
INSERT INTO DVD VALUES
('508','Melodeon','5')
INSERT INTO DVD VALUES
('509','Rock','2')
INSERT INTO TopTenRentalLastWeek VALUES
('1','500')
INSERT INTO TopTenRentalLastWeek VALUES
('2','501')
INSERT INTO TopTenRentalLastWeek VALUES
('3','502')
INSERT INTO TopTenRentalLastWeek VALUES
('4','503')
INSERT INTO TopTenRentalLastWeek VALUES
('5','504')
INSERT INTO TopTenRentalLastWeek VALUES
('6','505')
INSERT INTO TopTenRentalLastWeek VALUES
('7','506')
INSERT INTO TopTenRentalLastWeek VALUES
('8','507')
INSERT INTO TopTenRentalLastWeek VALUES
('9','508')
INSERT INTO TopTenRentalLastWeek VALUES
('10','509')
INSERT INTO TopTenRentalThisWeek VALUES
('1','500')
INSERT INTO TopTenRentalThisWeek VALUES
('2','501')
INSERT INTO TopTenRentalThisWeek VALUES
('3','502')
INSERT INTO TopTenRentalThisWeek VALUES
('4','503')
INSERT INTO TopTenRentalThisWeek VALUES
('5','504')
INSERT INTO TopTenRentalThisWeek VALUES
('6','505')
INSERT INTO TopTenRentalThisWeek VALUES
('7','506')
INSERT INTO TopTenRentalThisWeek VALUES
('8','507')
INSERT INTO TopTenRentalThisWeek VALUES
('9','508')
INSERT INTO TopTenRentalThisWeek VALUES
('10','509')

GO

Dimi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources