Appending Records To The Existing MS SQL EXPRESS SERVER Table.

May 19, 2007

Dear All,
I am Using  MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.
 Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,
Now I want to append 1 more access Table(5500 records) to the existing table   having same fields.
How to do this.Can any body tell me?

 
Thanks and Regards
mukesh 

View 4 Replies


ADVERTISEMENT

Appending Leading 0 To Existing Varchar

Jun 12, 2014

I have a table with a field called "pro_nums" and needs to have a leading zero appended to it. The field is a varchar datatype and I tried doing an update that simply said '0' + pro_num but that didn't work. What do I need to do to append this leading zero?

View 3 Replies View Related

Creating Nested Tables Inside Of A Existing SQL Server Express Table

May 26, 2007

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,



James Simpson

Straightway Technologies Inc.

View 4 Replies View Related

Replacing Existing Records In A Table

Feb 5, 2007

I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?

View 3 Replies View Related

How To Replace The Existing Records In The Table From Updates In The Transactional File

Sep 13, 2007



Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields.

How do i update the row coming from the transaction file in the tables which contain the rows from the master file .

to better explain the example is
Master File

ID Name AGE Salary Transcode TransDate
2 dev 27 2777

Transaction File indicating change

ID Name AGE Salary Transcode TransDate
2 dev 27 24444 C 08072007


the ouput should be

2 dev 27 24444 C 08072007

replacing the existing row in the table(updating the whole row)

i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row.

what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row.

Thanks...

View 1 Replies View Related

Adding/Appending Records Frequently In SQL

Jan 24, 2008

Hey guys. I need a little help once again, here's the problem:

We are using an attendance system software that adds records in an Access 97 table whenever someone logs in or out of the room (Yes, it's old and I hate it, but it came with the hardware).

So we plan to create a program that lets admins check those login/logout data in an SQL Server 2005 database using a Visual Basic as the frontend.

Our only problem is, how do we transfer data from the Access 97 tables to our SQL tables *everytime* it gets updated? We don't need every detail the Access97 table has (it has IDNo, CardNo, Time, Location, plus other useless stuff). We just need to extract the IDNo(int) and Location(varchar) field(we'll just use getdate() for the Time since the update is done in realtime anyway).

So in a way, we are trying to find out a way to technically sync both tables, just like Database Mirroring style.

Any suggestions?

View 3 Replies View Related

Copy Table And/or Records From SQL Server 2000 To 2005 Express

Apr 7, 2006

How can I copy a table from my sql server 2000 db to my sql server 2005 express edition?
I have a project in VS.NET 2005 and I have a db in App_Data folder. However, when I look into that folder, there is nothing visible. I now need to copy a table from my existing sql server 2000 to my db located in my project's App_Data folder.
Any help would be appreciated..
 
Regards,

View 1 Replies View Related

Import Existing Database Into SQL SERVER EXPRESS Management Studio Express

Feb 18, 2008



Hi,

I am a newbie so i apologies beforehand for any mistakes i make on this forum.
Anyway, i created an asp.net website using the MS Visual Web developer tool. This has a couple of SQL SERVER databases within it.
I then latervinstalled the SQL SERVER management studio express in order to manage the SQL SERVER DBs that i had as part of my website but they do not appear within the management studio db list.
As this is the express version, is there anyway of importing the existing SQL SERVER dbs that i have to the management studio so i can manage them from there?

Regards,

Shuja.

View 5 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

SQL Server 2005 Express:Is It Possible To Convert Existing Data From .mdb To .mdf?

Sep 21, 2006

Hi all,

I need to use the SQLDataSource in Visual Web Developer (VWD) 2005 Express websites. The SQLDataSource must have the .mdf data in the project application. But I have a volume of the .mdb data for evaluation by using T-SQL of the SQL Server 2005 Express and VWD 2005 Express program. Is it possible to convert existing data from .mdb to .mdf? If it is possible, please tell me what software program to use and how to do it.

Thanks,

Scott Chang

View 8 Replies View Related

Convert Existing MDB Database Files To Server Express

Feb 27, 2015

1) What is the current version of SQL Server Express?
2) HOw much SQL Server Express costs (figure about 500 branch servers)
3)Are there any Tools from Microsoft to convert Access 97 directly to SQL Server express, and how much do they cost?
4)Server Hardware requirements to run SQL Server Express - disk size, memory size, security settings, pre-requisite, service needed.
5)Maximum capacity / capabilities of SQL Server Express-max # of simultaneous users,tables,rows,database size.
6)Any installation instruction for SQL Server Express.

View 4 Replies View Related

Is SQL Server Service Manager Still Existing On SQL Express Edition?

Jun 5, 2006

Hi there. I am new to the SQL Express. I just finished installing the SQL Express 2005 with Advanced Services on a Windows XP SP2 computer. The first thing I noticed was the service manager (used to be on the taskbar) was being removed on the Express Edition. How do I get this feature back if it's even possible? I know you can find out the server status from the Configuration Manager but it's handy if the status is showned on the taskbar after logging in.

Any help will be greatly appreciated. Thank you.

KC

View 1 Replies View Related

Appending Data On Same Clumn From Other Table

Apr 12, 2006

hi,

unfortunaly i have two tables where usernames are stored. in the one table the column is called 'remote_U' in the other 'remote_u' ... note the upper case

so want to generate listof all usernames and retrieve them in a single a column.

like
SELECT DISTINCT remote_U FROM table1 APPEND (SELECT DISTINCT remote_u FROM table 2)

i have tried joins and groups, but is not working. can i also do an alphabetical order after appending the second select? distinct is needed, 'cos one user may have more then one entry in the table, but may be also in both tables ....

View 4 Replies View Related

Appending And Deleting Entries In Table

Jul 11, 2014

I have a question on appending and deleting entries in mysql table. This is my sample table.

table name: details

id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

[Code] ....

I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries

E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries

id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

But I want to retain only the one having longest first and end points ie

| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.

My final output in mysql table should look like this.

id_name | model | mode | media| first | last | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
| PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
| PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |

[Code] ....

Is there anything that I could do to append it?

View 1 Replies View Related

Appending Data To A Table But Not Duplicates

Jul 23, 2005

Hiya everyone,I have two tables in SQL 2000. I would like to append the contents ofTableA to TableB.Table A has around 1.1 Million Records.Table B has around 1 Million Reocords.Basically TableA has all of the data held in TableB plus 100,000additional records. I would only like to import or append these newadditional records. I have a unique index already setup on Table B.Any ideas pretty pretty please?Paul.Ps. (Have been messing around with DTS but get a unique violation error- Which is kinda what I want I guess, but would like SQL to ignore theerror and only copy the new data - if only)

View 9 Replies View Related

Creating Snapshot / Transactional Table By Appending

Feb 6, 2015

I am very new to SQL coming from a SAS background.

I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.

For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.

I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.

This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.

I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).

SELECT
CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,

[Code] ...

View 4 Replies View Related

How To Do An Update On Existing Records?

Jul 20, 2005

I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.

View 1 Replies View Related

Updating Of Existing Records

Sep 4, 2007

I have a following problem: I€™m importing records from an Access table into a SQL Server Table. I€™m using lookup to determine if a record already exists in the SQL Server table and in that case I should update the record if it was modified.

I thought of using OLE DB Destination for new records (done, works fine) and OLE DB Command Transformation to update the modified records. It all works but the thing that bothers me is that my table has approx. 40 columns so my OLE DB Command is very long (update table set col1 = ?, col2 = ?, col3 = ? €¦€¦). The other problem is that I€™m always updating all the columns even if only one column was modified.

Is there a better way to update the existing records?

Any help is appreciated.

Thanx
Sara

View 4 Replies View Related

Search No Existing Sets Of Records

Jan 19, 2014

I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B

To illustrate:

Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we

[code]....

View 3 Replies View Related

SQL / Not Existing Records As Blank Cells

Oct 7, 2007

hi

I have data in two tables.

NAMES
IDName
1FIRST
2SECOND


CODES
IDCodeTypeCode
1Axyz
1Babc
1Cgfd
2Axdz
2Bdca



I want to join the two tables to add the Code of CodeType "C" to the records of NAMES

Result Example
IDNameCode
1FIRSTgfd
2SECOND----


I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.

how should the sql statement look like?

Please help!
thanks in advance!

Mikk

View 1 Replies View Related

Using A Lookup To Update Existing Records

Jul 2, 2007

Hi,



I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.



I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.



Thanks

Trent

View 4 Replies View Related

WARNING When Adding A Column On SQL Server Existing Table

Feb 28, 2007

After i run the sql which adds some columns on one particular table.I am getting this Warning

Warning: The table 'usac499_499A' has been created but its maximum row size (9033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I got a series of the above warning message , but the coulmn wa created.

View 10 Replies View Related

SQL Server 2008 :: Create Partition On Existing Table?

Mar 4, 2015

Can we create the Partition on Existing Table?e.g Create table t ( col1 number(10,0), Col2 Varchar(10)) ;After the table Creation can we alter the table to partition the table.

View 2 Replies View Related

SQL Server 2012 :: How To Add A Primary Key For Existing Column In The Table

Oct 19, 2015

How to add a primary key for existing column in the table

View 8 Replies View Related

Importing Data (Overwrite Any Existing Records)

Mar 19, 2007

Good Morning,  I need some assistance with SQL Server 2000 Importing Data. 
When I import data from a text on a routine basis, three things must happen:
1.  New records identified by primary key get appended to table.
2.  Exisiting records identified by primary key get overwritten with new/(updated) data.
3.  All other existing records are left alone.
Does anyone know how to Import Records with the following the criteria above?  It cannot insert duplicate primary keys by nature, so it must overwrite those records!
This is being built into a DTS Package, but I need to get over this obsticle!  Thanks for any guidance!

View 2 Replies View Related

Problem Updating Existing Records With Newid()

Apr 23, 2008

I currently have a table called stores.  I've just added a uniqueidentifier column called store_guid to the stores table.  The table currently has about 500 records in it and now i'm trying to set each store_guid = to a newid().  I've tried using  UPDATE stores SET store_guid = newid()  .   however, that doesn't work because i think it's trying to set each record equal to the same guid using that approach.  All i need to do is fill in my new column with new guids.  any ideas?
 
Thanks in advance,
 

View 3 Replies View Related

Question: Changing Case On Existing Records

Jul 23, 2005

Greetings:I have a SQL 2000 database, in which about 1% of the records are inlower case. I need to make them UPPER CASE.Is there a function to determine and change the case of existingrecords, or will I have to re-write the records manually?Thanks,DW

View 6 Replies View Related

SQL Server 2012 :: New Column In Existing Table And Uploading Data

Jan 13, 2015

Need to change the datatype of existing column which has huge data.

I'm performing below steps

1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes

View 9 Replies View Related

SQL Server 2008 :: Adding Column To Existing Replicated Table

Feb 9, 2015

I have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.

If I alter a table and add a column, would this column automatically get picked up in replication?

View 0 Replies View Related

SQL Server 2012 :: Partition Existing Table And Archive One Of The Partitions

Jun 22, 2015

I have some table that need to be partitioned and archive one of the partitions.

I did this in Oracle several years ago but not in SQL Server.

I'm looking for a basic example on how to do this.

I know the basic steps but the examples that I found on the Web were not quite what I'm looking for.

[url][/
Partition an existing SQL Server Table
url]

View 9 Replies View Related

Validating The Insertion Of A Record Into Database Against Existing Records.

Jan 31, 2008

Hello. I currently have a website that allows appointments to be booked up for doctors. i currently have an insert record page built using ASP components. I would like to introduce some validation so that if a user tries to book an appointment date and time that is already occupied, an error message is returned saying that appointment slot is already booked. I'm quite confused how to do this. My current code looks as follows.
<%@ Page Language="C#" MasterPageFile="~/AdministratorMasterPage.master" AutoEventWireup="true" CodeFile="AddAppointment.aspx.cs" Inherits="AddAppointment" Title="Add Appointments - Bournemouth and Poole NHS Primary Care Trust" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table style="position: relative">
<tr>
<td style="width: 45px">
<br />
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:SqlDataSource ID="AppointmentsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
DeleteCommand="DELETE FROM [Appointment] WHERE [PatientNo] = @PatientNo" InsertCommand="INSERT INTO [Appointment] ([PatientNo], [PatientSurname], [PatientForename], [ConsultantName], [HospitalName], [Time], [Date], [AppointmentStatus]) VALUES (@PatientNo, @PatientSurname, @PatientForename, @ConsultantName, @HospitalName, @Time, @Date, @AppointmentStatus)"
SelectCommand="SELECT * FROM [Appointment]" UpdateCommand="UPDATE [Appointment] SET [ConsultantName] = @ConsultantName, [HospitalName] = @HospitalName, [Time] = @Time, [Date] = @Date, [AppointmentStatus] = @AppointmentStatus WHERE [PatientNo] = @PatientNo">
<DeleteParameters>
<asp:Parameter Name="PatientNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ConsultantName" Type="String" />
<asp:Parameter Name="HospitalName" Type="String" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="PatientNo" Type="Int32" />
<asp:Parameter Name="AppointmentStatus" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox3" Name="PatientSurname" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox4" Name="PatientForename" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList1" Name="ConsultantName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList2" Name="HospitalName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList3" Name="Time" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="Textbox2" Name="Date" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DropDownList4" Name="AppointmentStatus" PropertyName="Text" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label1" runat="server" Style="position: relative" Text="Patient No"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox1" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"
Display="Dynamic" ErrorMessage="RequiredFieldValidator" Style="position: relative"
Width="148px">Enter a Patient No</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label6" runat="server" Style="position: relative" Text="Patient Surname"
Width="116px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox3" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="TextBox3"
ErrorMessage="Enter a Surname" Style="position: relative" Width="140px"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label7" runat="server" Style="position: relative" Text="Patient Forename"
Width="128px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox4" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="TextBox4"
ErrorMessage="Enter a Forename" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label2" runat="server" Style="position: relative" Text="Consultant Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
DataTextField="UserName" DataValueField="UserName" Style="position: relative">
<asp:ListItem Selected="True">Select...</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"
SelectCommand="SELECT aspnet_Users.UserName&#13;&#10;FROM aspnet_UsersInRoles INNER JOIN&#13;&#10; aspnet_Users ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId INNER JOIN&#13;&#10; aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId&#13;&#10;WHERE (aspnet_Roles.RoleName = 'Consultant')"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="DropDownList1"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="152px">Select a Consultant</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label3" runat="server" Style="position: relative" Text="Hospital Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="HospitalName" DataValueField="HospitalName" Style="position: relative">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
SelectCommand="SELECT [HospitalName] FROM [Hospital]"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="DropDownList2"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="136px">Select a Hospital</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label4" runat="server" Style="position: relative" Text="Appointment Date"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:Calendar ID="Calendar1" runat="server" Font-Size="Smaller" Style="position: relative" OnSelectionChanged="Calendar1_SelectionChanged" OnDayRender="Calendar1_DayRender">
</asp:Calendar>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox2" runat="server" Style="position: relative; left: 0px; top: 8px;" Width="256px"></asp:TextBox><br />
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox2"
Display="Dynamic" ErrorMessage="Enter a Date" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px; height: 26px">
</td>
<td style="width: 136px; height: 26px">
<asp:Label ID="Label5" runat="server" Style="position: relative" Text="Appointment Time" Width="132px"></asp:Label></td>
<td style="width: 47px; height: 26px">
</td>
<td style="width: 100px; height: 26px">
<asp:DropDownList ID="DropDownList3" runat="server" Style="position: relative; left: 0px; top: 0px;">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem Value="09:00">09:00</asp:ListItem>
<asp:ListItem>09:30</asp:ListItem>
<asp:ListItem>10:00</asp:ListItem>
<asp:ListItem>10:30</asp:ListItem>
<asp:ListItem>11:00</asp:ListItem>
<asp:ListItem>11:30</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px; height: 26px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="DropDownList3"
Display="Dynamic" ErrorMessage="Select a Time" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px; height: 26px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label8" runat="server" Style="position: relative" Text="Appointment Status"
Width="136px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList4" runat="server" Style="position: relative">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem>Booked</asp:ListItem>
<asp:ListItem>Modified</asp:ListItem>
<asp:ListItem>Patient Notified</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Select a Status"
Style="position: relative" Width="120px" ControlToValidate="DropDownList4"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td align="center" style="width: 100px">
<asp:Button ID="Button1" runat="server" Style="position: relative" Text="Submit" OnClick="Button1_Click1" /></td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr></table>
</asp:Content>
Any help would be very much appreciated.
Thanks,
James.

View 2 Replies View Related

SQL Server 2012 :: Adding RowID To Existing Table - Inconsistent Results

May 6, 2015

I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.

We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:

AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction

My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.

Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.

I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:

AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase

My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:

SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'

Results look like this:

But every time I run the routine, I get different numbers!

Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.

TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved