Create Table With 15,000,000 Default Rows

Jul 23, 2005

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.

Gidon

View 2 Replies

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

Transact SQL :: Adding A Column To A Large (100 Million Rows) Table With Default Constraint?

Apr 24, 2013

IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END

It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.

View 4 Replies View Related

SQL Server 2012 :: Create Table From Rows Of A Different Table

Mar 6, 2015

View 9 Replies View Related

How To Create SELECT For Rows In Table

Oct 20, 2014

I have two db-tables:

"TAB01" with structure:

CREATE TABLE TAB01 (
ID int NOT NULL,
Name varchar(64) DEFAULT NULL,
PRIMARY KEY (ID)
)

It contains only name of some object.And "TAB02" with structure:

CREATE TABLE TAB02 (
ID int NOT NULL,
TAB01_ID int NOT NULL,
PositionA int,
PositionB int,
StringVal varchar(32) DEFAULT NULL,
PRIMARY KEY (ID)
)

which contains following data:

ID|TAB01_ID|PositionA|PositionB|StringVal|
1|1|1|1|A|
2|1|1|2|B|
3|1|1|3|C|

[code].....

and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:

TAB02.ID|TAB01.Name|TAB02.PositionA|TAB02.PositionB|
1|ABC|1|1|
10|DEF|2|3|
25|JKL|4|3|
30|MNO|5|1|
36|MNO|5|7|
41|PQR|6|4|
46|STU|7|2|

Data inserted in "TAB02" represents real-life structure:

ID|Name|P01|P02|P03|...|Pxy|
1|ABC|A|B|C|D|E|A|D|
2|DEF|J|K|A|B|F|F|B|
3|GHI|B|A|C|A|F|G|A|F|
4|JKL|F|E|A|B|B|E|E|
5|MNO|A|B|C|B|A|D|A|B|
6|PQR|D|E|F|A|B|A|F|
7|STU|A|A|B|B|E|B|E|

View 1 Replies View Related

Create Script To Insert 200 Rows Into Table

Aug 15, 2007

I have to create a script to install a database, and one of the tableshas about 200 rows of static data... I dont want to have to manuallytype in 200 insert statements, so is there a better way to do this? Ithought about maybe exporting the data into a CSV file and using somesort of procedure to insert the records that way... Any advise?

View 6 Replies View Related

How To Create A Table With The Rows For A Table

May 13, 2008

Dear all,
i've table tab1 with columns col1 col2

i've 20 rows out put after selecting the data.

i need all the data of col2 as column for a new table. that is tab2.

please guide me in this regard

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View 3 Replies View Related

Create Default

Feb 13, 2004

Hi All,

Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?

Cheers Wimmo

View 7 Replies View Related

Create Default

Dec 13, 2007

I want to create a default object if it does not exist in database.

if not exists (select name from sys.objects where name = 'dflt_zero')

create default dbo.dflt_zero as 0

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'default'.

But I am getting error.. the create default by itself works fine. How do I create this only when it does not exist?
Thanks!!

View 1 Replies View Related

Help: Need To Set Default Dimensions On Rows And Columns

Jun 1, 2005

When I open my Cube the default dimension on Rows is the dimension name that is first on the alphabetic list and on the columns it defaults to the time dimension.

I need to specify a specific dimension to be shown on rows and columns when the cube is viewed.

The analysis server is 2000 with SP3.

Thanks in Advance for the assistance

View 1 Replies View Related

Create A Default Schema

May 22, 2008

I Would like to create a schema sample and make it as default schema instead of dbo.

If a user logs in and creates a table like create table t1 (no int ) .
it would be assigned to sample schema and displayed as sample.t1 not dbo.t1.

How to set the user created schema as primary schema.

View 9 Replies View Related

Identity, Default Values And Multiple Rows

May 27, 2008

Howdy all,
I've run into an interesting scenario that I can't seem to resolve.

We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.

create table ident (seq_id int identity(1,1) primary key NOT NULL)

If I want to grab a new sequence id, I can execute

INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id

All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values.

If the table had one other field in it, I could do the following

create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS

Can this be done if there is only the identity column and if so, someone care to educate me?

View 10 Replies View Related

Inserting Default Values On Inserted Rows

May 12, 2008

Hi!

I want to merge in a Datawarehouse, data of "source databases"

I need to insert on destination inserted rows a default value, acording to the datasource of the rows.

Something like in the field <SOURCE> fill on correspondent ROW the value <DATASOURCE1>
the row data is filled with default DATASOURCE value.

in nowdays i can extract and load the data of sources but i know how i can do this inserts,

thx.

View 1 Replies View Related

Identity, Default Values And Multiple Rows

May 27, 2008

Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.

Code Snippet
create table ident (seq_id int identity(1,1) primary key NOT NULL)

If I want to grab a new sequence id, I can execute

Code Snippet
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id

All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values. If the table had one other field in it, I could do the following

Code Snippet
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS

Is there a way to do this if there is only the identity column and if so, someone care to educate me?

From a design perspective, all I can say is "it's not mine" but I do have to live with it, or at least my coworker has to live with it. I'm merely trying to prevent them from opening a cursor and calling the default values version 45M times and no, that's not an exageration.

View 2 Replies View Related

Problem With 'CREATE DEFAULT ...' Command

Sep 23, 2004

Hi,

Please help

The following command works ok:
create default [Zero] as 0

but the command:
if not exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Zero]')
and OBJECTPROPERTY(id, N'IsDefault') = 1)
create default [Zero] as 0

returns:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'default'.

Env: Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thanks
Helena

View 3 Replies View Related

Connot Create Default Directory??????????

Apr 23, 2007

I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.

Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."

Does anyone know where the <Not Set> virtual directory is stored on the computer?

addl info:

ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.

at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)

(Vista Ultimate)

View 6 Replies View Related

How To Create Login With Default Database

Aug 3, 2006

Hi,

I can't performe this:

use databaseX

CREATE LOGIN [abc]

WITH PASSWORD ='xxxxxxxxxxxxx',

DEFAULT_DATABASE = db_name()

or

DEFAULT_DATABASE = [ db_name() ] as well

I know that

DEFAULT_DATABASE = databaseX

works, but how can I create login with default database with db_name() ?

thanks,

View 6 Replies View Related

MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?

Jan 12, 2005

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in

View 6 Replies View Related

Create A Database Login In The Default Domain

Jul 23, 2005

I'm trying to create a login in the default domain. I know I can pullthis information from xp_loginconfig, but don't see how I can use it inthe context of sp_grantlogin.For example, pull the domain the user is currently logged in on andinsert it into the sp_grantlogin script. Has anyone ever done this inthe past?

View 2 Replies View Related

Should I Create A Non-primary Default Filegroup As A Best Practice?

Nov 7, 2007

This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.

Thanks for your input,
Mike
MCDBA

View 4 Replies View Related

Create Default Tables, Procedures, Etc. On Newly Connected Remote SQL Server

Jan 5, 2007

I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?
-Mathminded

View 4 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.

Any sort of help would be highly appreciated.

Thanks,

View 2 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

View 1 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go

Thanks Guys.

View 14 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):

{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}

How would I do this?

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Create N Rows From 1 Row?

Sep 8, 2006

Hello...

I have a small question about how to realize something in SSIS.

We are rewriting an Application and we will be normalizing a table. The current Table has Data in the Format:

ID - Name - Type500 - Type1000 - Type2000

1 - Test - 2 - 1 - 0

2 - Test 2 - 0 - 2 - 1

The Targets would be:

ID - Name

1 - Test

2 - Test

TargetID , Type_ID , Date (+ ID Field omitted here)

1 - 1 - 1.1.1900

1 - 1 - 1.1.1900

1 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 3 - 1.1.1900

So basically we need to generate N type rows for each "Count" in the Type Fields. So whats the best aproach to convert this data in an SSIS Package? Currently I am thinking about calling a SP to split the rows, but I dont "like" this aproach since it would place import/migration logic into the database and I would have to "clean up" later.

View 3 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?

View 3 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related