IS Anyway To Insert The Data In Ascending Order?
Jan 8, 2008Hi
I have a table with column name starts from col1 to col20. IS their anyway to insert the data and sort the table by col1 and col2 and SAVE IT.
ADVERTISEMENT
Sorting The Data In Ascending Order .
Aug 16, 2002Hi ,
Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .
Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .
Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?
Many Thanks .
Anita.
Conditional Order By - Sort Result Set By Employee Number Ascending Order
Sep 24, 2012In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
Force The ORDER BY To Be In Ascending Order??
Jul 5, 2007I noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
Field Not Sorting In Ascending Order
Dec 30, 2005Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 Replies View RelatedColumn Sorting In Ascending Order
Nov 27, 2007
Hi
I have Constructed a table
Table Name : MasterEntry
Column Name:
MasterEntryNumber
ServiceName
ServiceDepartment
EmployeeName
MasterEntryNumber is GenerationNumber where ever any entry is happen in table
if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order
e.g MasterEntryNumber has
1
2
3 it give correct ordering
but if i exceed more 10 if i put same queries
Output of queries is
1
10
11
2
3 this is the out of the queries if it exceed more 10 rows
i want queries should be look like this
1
2
3
...... 10
did any have experience on this issue please let me know where i can fix
kinds regards
How To Sort Table In Sql2000 With Ipaddress(format X.x.x.x) As Column With Nvarchar Datatype In Ascending Order
Apr 19, 2006How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?
May 19, 2015I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Ascending And Count Help
Jul 23, 2005Below is the statement i am working with and i am trying to figure outhow to have the count in ascending order...is this possible? Right nowit groups by FP in alphabetical order but i dont want that i want thewhatever the FP1 count is to be ascending but i am not sure how to goabout doing this...so any help or ideas?SELECT FP1 AS FP, COUNT(FP1) AS Expr1FROM dbo.FP1WHERE (FP1 IS NOT NULL) AND (LTRIM(RTRIM(FP1)) <> '')GROUP BY FP1
View 3 Replies View RelatedSort Ascending Isn't Sort Ascending
May 20, 2008Hi,
have uncovered a curious problem with sort ascending in tables.
In a table I'm grouping on CONTRACT_ID field. The grouping works fine but the sort acending on CONTRACT_ID doesn't.
Here are the actual contract id's I have to sort:
0202018B
0202019C
0202020G
0202021H
0202022J
0202023K
0202800B
The above list is how the Crystal Report I'm converting does this sort - i.e. the normal, common sense, intuitive answer.
Here's how SSRS does the sort:
0202022J
0202021H
0202019C
0202800B
0202018B
0202023K
0202020G
i.e. a completely non-intuitive, non common-sense answer!
I can probably solve this by stripping the last character and converting to int via an expression, but why does one get a completely non-sensical answer with the standard values? What is the logic by which Microsoft have implemented sorting. It seems very odd to me. Can anyone shed any light on this?
thanking you in advance,
cheers,
rob
GridView With Ascending Drop Down List
Apr 16, 2008Currently I have a small page that includes a drop down list with about 120 possible choices to chose from.
The list works fine, however, the choices are not sorted ascending/alphabetical order. So it is very slow searching for
the selection you want.
See the link.
http://visualboxscore.com/boxscores/cfb_box_scores.aspx
Does anyone have any easy solution for this problem?
Below is the code I am using.
<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>Visualboxscore.com - College Football Box score Query page</title>
</head>
<body>
<form id="form1" runat="server">
<b>Select Team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<table>
<tr>
<td valign="top">
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="date"
AutoGenerateColumns="false" Width="427px">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Team" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Rush No." DataField="rush_no" ItemStyle-HorizontalAlign="Center" SortExpression="rush_no" />
<asp:BoundField HeaderText="Rush Net" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Att." DataField="pass_att" ItemStyle-HorizontalAlign="Center" SortExpression="pass_att" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="No. Plays" DataField="no_plays" ItemStyle-HorizontalAlign="Center" SortExpression="no_plays" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="TO Margin" DataField="turnover_margin" ItemStyle-HorizontalAlign="Center" SortExpression="turnover_margin" />
<asp:BoundField HeaderText="APY" DataField="all_purpose_yards" ItemStyle-HorizontalAlign="Center" SortExpression="all_purpose_yards" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
<asp:BoundField HeaderText="Opponent" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT [date], [offense], [rush_no], [rush_net], [yards_per_carry], [pass_att], [pass_yards], [yards_per_att], [no_plays], [total_yards], [yards_per_play], [turnover_margin], [all_purpose_yards], [points], [defense] FROM [cfb_boxscores] WHERE ([offense] = @offense)"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="offense" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
<td valign="top">
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" ID="SqlDataSource3"
runat="server" SelectCommand="SELECT [date], [offense], [rush_no], [rush_net], [yards_per_carry], [pass_att], [pass_yards], [yards_per_att], [no_plays], [total_yards], [yards_per_play], [turnover_margin], [all_purpose_yards], [points], [defense] FROM [cfb_boxscores] WHERE ([date] = @date)">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="date" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
</table>
<br />
</form>
</body>
</html>
Ascending Categories And Ascended Subcategories
Apr 18, 2014I've been trying some examples on line and have not been able to get this to work.
Here is my table followed by what I am trying to output.
Id | ParentId | CategoryName
33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges
[Code] ....
I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories
48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges
[Code] ...
This is what I have currently, still a long way off :/
SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name
INSERT INTO With ORDER BY
Apr 23, 2007Hi Everyone,
I want to insert data in the table in the sorted order itself.
my qry is :
INSERT INTO table
SELECT col1, col2, col3 FROM table1
UNION ALL
SELECT col1, col2, col3 FROM table2
ORDER BY col1, col2, col3
The order by clause doesn't work in this manner.
Can anyone help me.
Thanks in advance.
Riyaz
Sort Order On Insert
Jun 22, 2001Are there any techniques I can use to specify the sort order on an insert statement.
I want to insert data from tableA to tableB in a certain order .
I know I can do it with a cursor, but I'm sure theres a better way
Pargat
Rows Insert Out Of Order
Oct 30, 2000I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure.
columnA columnB columnC columnD columnE columnF columnG
char char int int int smallint char
it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts.
insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3)
insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2)
These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens.
Thanks
Order Of Insert Row In A Table
Jun 13, 2000Without inserting an additional column with a timestamp or adding an IDENTITY column, can you tell which row was inserted first in a table?
Angel
Order By In A INSERT INTO..SELECT
Mar 27, 2006I have the following basic statements being executed:Create a temp table, #TempPagingInsert Into #TempPaging (Col1, Col2)Select Col1, Col2 From SomeOtherTable Order By Col2, Col1Select * from #TempPagingI can't provide a reproduceable scenario right now without making thisinto a 200K post, so I'm hoping someone will know what the issue is.. Ican't find anything regarding this in BOL.Basically the order is off a little bit every now and then when thereare large amounts of data, like more than a couple hundred rows. Isthere something I need to do to guarantee the originally selectedorder?This is very important that it be in order from the original selectstatement as this is for paging. Adding an order by in the secondselect from the temp table will not fix the problem. In this particularinstance that I have reproduced we are using SQL 2005 but have alsoseen this on SQL 2000 servers. I had previously asked this question asI was using a SELECT INTO statement, but now we are manually creatingthe temp table (Pain in the ass) and still having the same issue. Bestcase scenario is for it to work for a SELECT INTO.Any ideas?
View 29 Replies View RelatedINSERT INTO As SELECT With ORDER BY
Aug 1, 2006Hi,
This may sound like a dumb question, but I need to be certain of the answer.
If I have a query like this:
INSERT INTO table1
(col1, col2, col3, ... )
SELECT col4, col5, col6, ...
FROM table2
ORDER BY col7, col8, col9, ...
and table1 has an identity column that increments by 1 each time, am I gauranteed that the records inserted into table1 will always be inserted in the order as specified in the ORDER BY clause and hence the increasing identity column in table1 will reflect the same order as that of the ORDER BY clause?
Seems like it should be the case but I need to be sure.
Thanks,
Peter
SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data
Feb 26, 2015I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
Order By On Bulk Insert Task
Aug 23, 2007I want to check sorted data option for bulk insert task on DTS Package. I want to sort data on 1st column. How can I put order by clause on sorted data option? I put ORDER (SSNo) but I am getting error.
Thanks
ss
Execution Order Insert && Update
Mar 18, 2008
Hello All,
I have a Conditional Split, where there are two outputs.
First output is a dataset which is to be inserted into the database.
Second output is a dataset for which the data already exists in the DB. I just need to update those data.
I have a doubt here. I want the insertion to be done first and then updation.
Is there any property to be set for insertion or updation, something that maintains the order of execution or priority of execution.
Please do ask me if you need any further clarification.
Thanks,
Kapadia Shalin P.
Inconsistent Order By Using Insert Into In A Stored Procedure
Jul 20, 2005hi there,i am using sql server 7. below is the stored procedure that is givingme grief. its purpose it two-fold, depending on how it is called:either to return a pageset (based on page number and page size), or toreturn IDs of previous and next records (based on current record id).the problem is, that the order in which records are inserted into thetemp table is inconsistent, even though the calling statement and theorder by is always the same: sometimes records are ordered correctly,by project_number, and sometimes the order is broken starting at somerecord (which is always the same).i have no idea what is wrong here, i would appreciate any help!thanks so much.here is the calling statement:EXECUTE spProjects 2,null,'project_number','asc','',6,50here is the proc:CREATE PROCEDURE spProjects@action int,@currID int,@sortBy varchar(50),@sortDir varchar(4),@searchBy varchar(255),@Page int,@RecsPerPage intASSET NOCOUNT ONDECLARE @nextID intDECLARE @prevID intDECLARE @currRow intDECLARE @rowCount intDECLARE @firstRec intDECLARE @lastRec intDECLARE @total intDECLARE @more intDECLARE @sortBy2 varchar(50)-- setup temp tableSELECT r.id as row, r.*,a.name agr_type,pu.name purpose,sp.name sponsor,pr.name prime,p.lname p_lname, p.fname p_fname, p.mname p_mi, p.emailp_email,o.name org,convert(varchar(10), r.created_date, 101) adddate_c,convert(varchar(10), r.updated_date, 101) upddate_cINTO #project_temp_tableFROM spm_projects r, spm_agreement_types a, spm_purpose_typespu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs oWHERE 1 = 0IF @sortBy IS NULL SELECT @sortBy = 'project_number'IF @sortBy = '' SELECT @sortBy = 'project_number'SELECT @sortBy2 = @sortBy + ' ' + @sortDirIF @sortBy NOT LIKE '%project_number%' SELECT @sortBy2 = @sortBy2 +', project_number'-- get projectsEXEC ('INSERT INTO #project_temp_tableSELECT r.id as row, r.*,a.name agr_type,pu.name purpose,sp.name sponsor,pr.name prime,p.lname p_lname, p.fname p_fname, p.mname p_mi, p.emailp_email,o.name org,convert(varchar(10), r.created_date, 101) adddate_c,convert(varchar(10), r.updated_date, 101) upddate_cFROM spm_projects r, spm_agreement_types a, spm_purpose_typespu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs oWHERE r.agreement_type_id = a.idAND r.purpose_type_id = pu.idAND r.sponsor_id = sp.idAND r.prime_id *= pr.idAND r.pi_id = p.idAND r.org_id = o.id' + @searchBy + 'ORDER BY ' + @sortBy2)SET @rowCount = 0-- number recordsUPDATE #project_temp_table SET @rowCount = row = @rowCount + 1-- prev/nextSELECT @currRow = row FROM #project_temp_table WHERE id = @currIDSELECT @prevID = id FROM #project_temp_table WHERE row = @currRow -1SELECT @nextID = id FROM #project_temp_table WHERE row = @currRow +1-- pagingSELECT @firstRec = (@Page - 1) * @RecsPerPageSELECT @lastRec = (@Page * @RecsPerPage + 1)SELECT @more = COUNT(*) FROM #project_temp_table WHERE row >=@LastRecSELECT @total = COUNT(*) FROM #project_temp_tableSET NOCOUNT OFF-- prev/nextIF @action = 1 SELECT @prevID as prevID, @nextID as nextID--pagingIF @action = 2SELECT *, @more as more, @total as totalFROM #project_temp_tableWHERE row > @firstRec AND row < @lastRecDROP TABLE #project_temp_table
View 7 Replies View RelatedTransact SQL :: Generate Insert Order Script
Apr 22, 2015I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
Transact SQL :: How To Update ItemOrder With Ascending Numbers Starts With 1 For Child Items
Jul 23, 2015I have a below table,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL
Here,
1. for all ReportId, child items's ItemOrder = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0
I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.
DB Engine :: BCP Output Is Performing Ascending Sort Based On Values Of First Column
Jun 24, 2015As bcp does not allow for the column names to be included; I have developed a method for providing the columns. The end result is that two Tables are required for each output; a "ColumnNames" table and the Table that contains the actual data; however the bcp command is sorting the data; why this is happening?
According to Microsoft, by default bcp will not apply any sorting unless specified.
Here is the command I am using to perform the bcp output: -
SET
@bcpCommand =(select
'bcp "SELECT * FROM GPReports.dbo.MIS001_BCPColumnNames UNION SELECT * FROM GPReports.dbo.voltemp" queryout '
+ @FilePath+'
-c -t -T')
EXEC
master..xp_cmdshell
@bcpCommand
This is the bcp topic I referred to [URL] ....
SQL 2005 Upgrade Insert Into Select * Changes Order Of Columns
Dec 13, 2007
Hi there,
This appears to be a change in behaviour between SQL 2000 & 2005. Can anyone confirm?
We have two tables with same schema but different column orders. In Sql 2000, the statement
Insert Into table1 select * from table2
appears to map the column names between the two tables. There is one column out of order, however Sql 2000 doesn't seem to care and correctly inserts the data.
In Sql 2005 the behaviour is to return the columns in the order of table2, rather than mapping column names. This results in incorrect values being added to the columns. (Column shift)
The Sql 2005 behaviour seems to be correct, and select * is bad practice anyway, however I would like to confirm why this was changed and whether there is a service pack/hotfix in 2000 that would have the same result.
We are running compatibility mode in Sql 2005 v9.0.3042.
Sql 2000 is 8.0.2187
thanks,
Andrew
Comparing Values In Two Tables In Order To Do Insert -- Comparision Is Not Working!!
Jul 23, 2005I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
View 1 Replies View RelatedINSERT INTO OPENROWSET Does Not Respect ORDER BY Clause On SQL Server 2005 EE
Jul 3, 2007Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId
FR
On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.
Best regards,
Ângelo Vilela
Export Wizard Disturb The Order Of Data While Exporting Data To Acess 2003 From SQL Server 2005
Feb 24, 2007
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
Apr 9, 2007Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
Can't Insert New Data To Sql Using Sqldatasource.insert, Web Forms And A Master Page
Sep 11, 2006Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
View 3 Replies View RelatedInsert Command Fails When I Want To Insert Records In Data Table
Apr 20, 2008On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri