Create table with 15,000,000 default rows

Hi I want to create a table with one column, which is a identity
column.
Let's say like this:
CREATE TABLE DefaultTable(N int identity(0,1))

Then I want to fill this table with 15,000,000 records, so that I have
a table with the numbers 0 to 14,999,999.

How can I do this as fast as possible. A standard INSERT would take a
long time.

(It can be a temp table or a table variable. I just need a list with
numbered 0 to 15,000,000)

Thank you.

Gidonhttp://www.bizdatasolutions.com/tsql/tblnumbers.asp
--
David Portas
SQL Server MVP
--|||Thanks a lot.

Create table on multiple Filegroups .. is it possible ?

Can i create a table/index that spans multilple FGs such as
CREATE TABLE T1
( cola int PRIMARY KEY,
colb char(8) )
ON FG1,FG2,FG3Are you confused between files and filegroups? Have a read in bol about them.
The answer to the question you have posed is no - you have a clustered index
which resides on the data filegroup.
But I don't think it's the question you wanted to ask.
"Hassan" wrote:
> Can i create a table/index that spans multilple FGs such as
> CREATE TABLE T1
> ( cola int PRIMARY KEY,
> colb char(8) )
> ON FG1,FG2,FG3
>
>|||As Nigel says, it isn't possible. Why do you ask? The purpose of
filegroups is to provide a logical entity on which to place data. The
PHYSICAL placement of data is determined by the location of files,
rather than filegroups. So it should be possible to achieve whatever
configuration you need using a single filegroup per object.
--
David Portas
SQL Server MVP
--

create table if not exists syntax error

CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);

For some reason the above statements are giving me syntax errors?

I want to create table only if it does not already exist.

Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?

thx in advance .

IF NOT EXISTS
( SELECT [name]
FROM sys.tables
WHERE [name] = MyTable
)
CREATE TABLE MyTable (Col1 int IDENITY, etc. )

Use 'IF EXISTS' test to TRUNCATE or DROP.

Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

Create Table

Can i create a table using the structure of an existing table, instead of defining the columns one by one?

e.g.
I already have table1(col1 int,col2 char(3))

I want to create another table with the same structure as table 1 without doing the following:
create table table2(col1 int,col2 char(3))

Is there a command of doing create table2 as table1 let say?Depending on the size of the table you could do the following:

select * into newtable from oldtable|||if you don't want to include data in newtable, you should modify the query as below:

select * into newtable from oldtable where 0=1|||Just what I was going to add:

If you want table structure and data then

select * into newtable from oldtable

else

select * into newtable from oldtable where (statement is false)

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicel
y
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn'
t
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:

> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me t
o
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
[vbcol=seagreen]
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREA
TE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It did
n't
> make sense to me either, but since SQL2K5 mentioned successful completion
I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the databas
e
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:
>

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?
Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus
|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:

> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>
|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
[vbcol=seagreen]
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:
> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:
> > Hans,
> >
> > I'm not really clear what you're trying, but the syntax you posted
> > doesn't make any sense.
> > In the CREATE SCHEMA statement there's no schema name and then you try
> > to grant select permissions on a view before you actually create the
> > view. Unfortunately executing a CREATE SCHEMA statement without a
> > schema name doesn't cause an error, but simply doesn't do anything.
> >
> > About "Ross" , it's the database user who becomes owner of the schema.
> > If you try to execute the following staement in a database with no user
> > Markus you will receive an error.
> > CREATE SCHEMA mySchema AUTHORIZATION markus
> >
> > Markus
> >
> >

Create New Table

hi i have one question,

i have created a table, let's say CallerList:

CREATE TABLE MyDB.[dbo].[CallerList]
(
[pid] [int] NOT NULL,
[Name] [varchar] NULL,
[Surname] [varchar] NULL,
[Phone] [int] NULL,
[Date] [datetime] NOT NULL

)

now i'd like to create table CallerList1, and i want it to have same names of columns: pid, Name, Surname, Phone, Date, but i don't want to create it in the way as i wrote up, is it possible to somehow COPY these column names from CallerList and not to wirte the whole code again? Like just the names of columns and their properties to have the same...

thanx

Hi,

this should help:

SELECT * INTO CallerList1 FROM CallerList WHERE 0 = 1

So you copy only the structure to the new table CallerList1

--Andreas