create table t1 ( oid int identity(1, 1), f tinyint )
and need way to fill it as fast as possible with say 100000 sequential values. Field f may stay = 0 but oid should be raising from row to row. How can I do it in MSSQL 2000?
I have three queries that I use to determine what the party type of a person.
They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.
I would like to fill a temp table with the results so I can use it in reporting.
Here is my code:
Code: SELECTvap.partyID, relationship INTO#Relationship SELECTvap.partyID, Relationship = 'Child' FROMVolunteerActivityParty vap JOIN VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
Ok, so I can connect to the database without any errors, however im not sure about the syntax for filling a table. Heres what i have so far in the pageload. Like i said this all works with out any errors. Thanks in advance for the help.testDS = New DataSet() testDataTable = New DataTable("Tbl")testDataTable.Columns.Add("username") testDataTable.Columns.Add("datecompleted")testDataTable.Columns.Add("lastfive") testDS.Tables.Add(testDataTable)Me.dgrdSearch0.DataSource = testDS.Tables("Tbl") Dim Conn As Object = Server.CreateObject("ADODB.Connection") Dim strConn = "DRIVER={SQL Server};SERVER=serverName;UID=userID;PWD=password;DATABASE=net"Dim DSNtest As String = strConn Dim sql As String = "SELECT * FROM Tbl"
HiI'm developing a website using SQL Server Express 2005 and VIsual Web Developer Express, and now want to start testing my website in a live environment. Until now I have been manually creating tables on my web database to match those on my local DB, but I have so many tables I don't want to manually recreate them all again. Is there a method in SQL Server Express 2005 that will allow me to generate insert scripts for all the tables? CheersAndy
Hello everybody I am doing update on Database using Erwin Is any diffrence in this code and what is is right way to create table in my case ? -- --- this code generated by Erwin 3.5.2 CREATE TABLE obooGrpAllotRmBooking ( BookingDate ASmallDateTime NOT NULL, ID_RoomType ID NOT NULL, ID_GrpAllot ID NOT NULL, ContractQty hAvailQty DEFAULT 0 ) go
ALTER TABLE obooGrpAllotRmBooking ADD PRIMARY KEY (ID_GrpAllot, ID_RoomType, BookingDate) go
------------- This code generated by SQl server 7 generate script --- CREATE TABLE [dbo].[obooGrpAllotRmBooking] ( [ID_GrpAllot] [ID] NOT NULL , [ID_RoomType] [ID] NOT NULL , [BookingDate] [ASmallDateTime] NOT NULL , [ContractQty] [hAvailQty] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [ID_GrpAllot], [ID_RoomType], [BookingDate], [ContractQty]
) ON [PRIMARY] GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD CONSTRAINT [DF__obooGrpAl__Contr__78DED853_05] DEFAULT (0) FOR [ContractQty] GO
I need sql script that can generate 'Create Table' definition including primary and foreign constraints for the table already existing in SQL server 2000.
Please do not refer me to the utilities or commercial software, I need only the sql script.
I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.
I'd like to know more about data warehouse. I've known some for SSIS working with SSAS. But it's existing data warehouse. I just transfer data to data warehouse using bussiness rules. I have no idea about data warehouse, how to design data warehouse from transactional database, how to generate the table structure. Any tips or books for that? Thank you very much.
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
hi I have a field call: password, I want to generates some random chars as default password for users (so user got something to login, and update later).
Current Solution I have: I made myself a trigger and it does generate password on insert, but the problem with that is I have to allow NULL on that field. I don't want NULL to be allowed, and if I don't allow null... sql server won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be more applicaions using the same field from the same database. Just in case if the other developer gets sloopy, I don't want other application(s) allow user put to enter a null or leave it blank on my password field.
My question is: 1) can I put a function or some sort at the "Default" field under "Design Table", if so...how? 2) any suggestions for a better implementation? 3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
I'd I have a problem I'd like to post CREATE TABLE and INSERT statementsthat will create my table and insert data into the table.I can use the scripting feature in Enterprise Manager to generate CREATETABLE scripts.Is there a script I can run that will generate INSERT statements so I caninclude sample data.Thanks
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs) exec msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail' @recipients = 'asdfasdfsdf@aafas.com' @from_address = 'asdfasdfacasca@cc.com' @query = 'Select * from failedjobs' @subject = 'List Of Failed Jobs' @attach_query_result_as_file = 1;
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
I have a list of customers in my Customers table. What I am looking to do is to create QR codes for each customer in Reporting Services. I need to include the customer name, address, postcode, telephone number and contact name.
SET @RowCnt = 1 SET @date = CONVERT(CHAR(10),GETDATE(),110) SET @ArchPath = '\D$EDATAWorkFoldersSendSendData' SELECT @TotalRows = count(*) FROM table1 --select @ArchPath
WHILE (@RowCnt <= @TotalRows) BEGIN SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt --PRINT @AccountNumber --test SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = ''' + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c' --PRINT @sql EXEC master..xp_cmdshell @sql SELECT @RowCnt = @RowCnt + 1 END
hi, how can i fill dropdownlist through code not through visit.and i need to know which is fastest and easy way for web application throught this below query. cmd.Connection = conn conn.Open()Dim ds As New DataSet cmd.CommandText = "SELECT Emp_Name,Emp_ID FROM Employee " da.Fill(ds, "data") cmd.ExecuteNonQuery() conn.Close()
Currently we have tables (in sql 6,5), many of them do not have primary keys. While I was trying to re-index (re-org), many of them got an error: "fillfactor 204 is not a valid percentage; fillfactor must be between 1 and 100." (many tables' fillfactor exceed 100 or more...) How can I fix them so I can upgrade to sql 7 ? Thank you for your help.
I am really confused about this whole fill factor thing. The way I understand it, is if you have a table whose data remains pretty much static, you should use a higher fill factor. Suppose you had a database where you had at most 150 transactions a day that changed the data, should the fill factor be left at the default(0) or increased? How do you determine how much to increase it? Is there a rule of thumb that suggests if you have x number of changes against a table, you should have a fill factor between y and z percent?
Hi all, While creating indexes for a table, I specified a fill factor of 70%. I then inserted a few hundred rows into the table. Is it possible to check to what percent the pages are full after the rows have been inserted?