Moving Rows Up And Down In A Table
Oct 19, 2007
Hi all,
I am currently writing a web service that gets data from a MSSQL2000 DB and I need to get the data to create some drop down lists, but these lists are composed of many concatenated elements. To sort these alphabetically is proving to be a royal pain in the butt as they just come out as they are ordered in the DB.
It would be a whole lot easier if i could just reorder the rows in one relevant table. Does anyone know how to do this or if it is possible to move rows up or down?
Thanks a lot!
Richee.
View 22 Replies
ADVERTISEMENT
Jan 17, 2008
Hi all,
I have two tables within the SQL server and there is an application writes 20.000 or 40000 rows in the First table.
1) I need to know when the table completes filled to start move the data to the second table.
2) After I copy the rows I need to delete the rows from the first table.
I though to use the trigger but I am not sure if this is the best approach or not
Please help....
Thank you
sms
View 11 Replies
View Related
Jul 20, 2005
I've seen this answered a number of times in the group, but now that Iactually need the answer I don't know where to look or what to put inthe search.The question is this:I have a query that produces these reults:Col1 Col2A BA CA DB AB CB DI would like take the above reults, manipulate them and get these:Col1 Col2 COl3 Col4A B C DB A C DRight now I've got a loop through a cursor, which is ok. It works,but I'm worried about it because if I end up with a large table in thefuture, the longer it will take to run the query with the curors.Can someone help me please - point me to previous answers to thisquestion?Thanks,Jennifer
View 1 Replies
View Related
May 18, 2007
I am new to script components. I would like to make a simple filter that either passes a row through untouched or eliminates it. I have my input and output buffers set the same, and I have it set as asynchronous. Now these are big rows. Is there a painless way to copy all columns from the input to output buffer, or do I have to do a "Output0Buffer.Col1 = Row.Col1" for each column?
View 6 Replies
View Related
Oct 21, 2007
Hi guys,
I have a table with the following structure
ID CodeID Code Desc
1 10 AA AAAAAAA
2 20 BB BBBBBBBB
3 30 CC CCCCCCCC
4 10 DD DDDDDDDD
5 20 EE EEEEEEEEEE
Now I need to create a new table with the following structure
CodeID AA BB CC DD EE
10 AAAAAAAA Null Null DDDDDD Null
20 Null BBBBBBBB Null Null EEEEEEEEE
30 Null Null CCCCCCCC Null Null
I have managed to create a logic that solve this problem by selecting all AA values and insert it to the new table and then using a series of update statements I updated the values of BB, CC, DD €¦€¦ The problem is that I have to do a 30 update statement so the performance is very bad.
Are there any better solution to implement this table using SSIS that could give me a better performance ?
Thanks a lot.
View 1 Replies
View Related
Feb 29, 2012
So I just got an email from Production Support saying an hour and a half downtime is unacceptable to move a half billion rows between 2 partitions because I am moving a clustered index and space is a consideration.
I can not use partition switching because the clustered index is changing.
This is what I am doing...
1. I am creating a new table with the new cluster on a new partiton
2. I am moving the records in 5K set based batches by doing a range search on the existing clustered index on the existing table.
3. I then reapply all of the nonclustered index from the original table to the new one.
4. I do a sp_rename swap out.
The same way I have done this many times before. Is there some new secret special sauce (other than partition switching) I can use?
View 14 Replies
View Related
Mar 29, 2007
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
View 1 Replies
View Related
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
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.
Any sort of help would be highly appreciated.
Thanks,
View 1 Replies
View Related
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
Apr 6, 2007
I am trying to write a stored procedure that copies one row from one table and moves it to another table. The two tables do not have exactly the same number of columns and not all of the columns that are the same in each table have the same name. I am using MS SQL Server 2000 and I cannot figure out a way to do this.
My big question is how do you execute a query in a stored procedure and then use the results to execute an update in the same procedure. I want the entire function to occur without the client application having to do any of the work.
Thanks,
Julian
View 1 Replies
View Related
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
May 27, 1999
Hi all,
This is (probably) a simple question, but I'm new to SQL Server scripting.
What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.
Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.
Ideally, we want this to be a script that is run daily.
Does anyone have any ideas or examples that we could use.
Any help is much appreciated!
Tim
View 3 Replies
View Related
Aug 29, 2002
I need to know if it is possible to move ONE table from an existing filegroup to another existing filegroup.
The answer I received to use ALTER database only modifies the file/filegroup name or changes the default filegroup.
Any assistance will be greatly appreciated.
View 1 Replies
View Related
Nov 10, 2004
I imported a little over 9 million records into my database without "cleaning" the data.
I'm looking for suggestions on what would be the most efficient way to get all the fields to the correct types and to insert decimals on the fields that need them.
My only idea thus far is to make another table with the correct field types and append the data to the new table.
View 1 Replies
View Related
Aug 27, 2012
We currently have all tables in the dbo schema, but for organizational reason we would like to split them up in multiple schemas and I wonder if that can be done without re-creating the tables.
So my question is, is there a way to move a table into a different schema without re-creating it? (For those familiar with Postgres I'm looking for an equivalent to "ALTER TABLE foo SET SCHEMA newschema") sp_rename only allows a "one-part name" for the new name, so apparently that cannot be used.
View 2 Replies
View Related
May 17, 2004
I have a timestamp in one table that is of datetime type and I need to move it to a table with a Varchar(30) type. What is happening is a date that should be set up like this "11/12/2004" is actually coming across as "Nov 12 2004". I tried using a cast(fieldname) as Varchar(30) on the datetime type, but it didn't help. does anyone have any ideas on how to retain the original date form in moving it to a varchar type? thanks in advance...
View 6 Replies
View Related
May 8, 2014
Wondering if its possible to copy a table form one DB and paste to another dB? is there a tool for that?
View 7 Replies
View Related
Oct 23, 2007
I have a table with 200 million rows which needs to be moved to a different instance. What is the best way to move the table while at the same time getting rid of any unused space in the table? I think BCP is the fastest way to do this, but I am conrned that the results would be different than exporting the t-sql equivalent and running that.
Any comments? BOL does not appear to address this issue very clearly. . .
Thanks,
Michael
View 4 Replies
View Related
May 1, 2008
Hi,
Here is my scenario simplified.
tbl1
userid, secondaryid
tbl2
userID, secondaryID
tbl1 is already populated. I just want to transfer the secondary ID to tbl2 based on the userID. I don't want to store secondaryID in tbl1 anymore.
View 4 Replies
View Related
Oct 20, 2013
I need to move data from one table to another using variable percentages and business days.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
View 1 Replies
View Related
Mar 28, 2006
Hi All,
I have a view that contains 30 million records.I want to move the view to a table in my database using DTS,but it is taking a lot of time,and making my tempdb to grow fast in giga bytes.Please is there anyway i can copy this view into the table easily in minutes.The view structure and the table structure are the same.Also, how can I index a view and can I add unique key to a view.
Thanks All in advance.
Mokah
View 6 Replies
View Related
Oct 17, 2007
Hi all
I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following
1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)
2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO
3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]
The above command executed successfully and now the table is in new file group [FG_ACTV]
My question is how come the table was moved to new file group though it has no files attached to it?
Thanks in advance.
View 5 Replies
View Related
Oct 2, 2007
Is there anyway to move tables without any indexes to a filegroup?
View 1 Replies
View Related
Sep 25, 2003
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
Any suggestions?
Thanks,
Steve Hanzelman
View 6 Replies
View Related
May 19, 2008
How to move a table to a new file group when the table DOES NOT HAVE A CLUSTERED INDEX.
I also don't want to , create a new table, move the data, drop the old table.
Is there a easier way?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
May 8, 2007
We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
Like I said a rough estimate is fine.
Many thanks,
Scott
View 4 Replies
View Related
Jul 23, 2005
Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"
View 14 Replies
View Related
May 30, 2006
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
View 1 Replies
View Related
Jul 20, 2005
I am using SQL Server 2000, SP3.I created an updatable partitioned view awhile ago and it has beenrunning smoothly for some time. The partition is on a DATETIME columnand it is partitioned by month. Each month a stored procedure isscheduled that creates the new month's table, and alters the view toinclude it. Again... working like a charm for quite some time.This past weekend I moved some of the first tables onto a new filegroup. I did this through Enterprise Manager, by going into designmode for the table, then going into the properties for the table andchanging the file group there as well as in all of the indexes. Nowthe partitioned view is no longer updatable. It gives the errormessage: "UNION ALL view '<view name>' is not updatable because apartitioning column was not found."I have extracted the DDL for all of the partition tables and comparedthem and they all look the same. I checked and then double-checked theCHECK constraints to make sure that they were all valid and they are.If I remove the tables that I moved to the new file group from theview, then it is once again updatable, but when I put them back in itfails again.Any ideas? If you would like samples of the code then I can send italong, but it's rather large, so I have not included it here.Thanks!Thomas R. Hummel
View 3 Replies
View Related
Jan 5, 2008
Hi,
I have an excel sheet in which there is some data in sheet1,sheet2.I need to transfer this 2 sheets data to single table using a single package.How can i do this in SSIS.
thanks in advance
Pradeep
View 4 Replies
View Related
Apr 4, 2008
Dear Friends,
Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way.
Thanks all!
kiruthika
http://www.ictned.eu
View 3 Replies
View Related
Sep 14, 1999
Hello:
I am running on mssql 6.5, sp4. We have been trying to use EM transfer manager to move one test database on one server to another database on another server.
We are dealing with 135 tables on this database. The transfer works up until about the 80th atble and then just dies but the scheduled task says it failed and check error log. The transfer creates the tables on the destination database but only loads the data until this one table.
WE use all of the options in EM Transfer manager which are st as defaults.
THere is no one on the source or destination databases locking this table.
Other smaller databases were successfully transferred from one database on one server to the other database on the other server without any problems today and yesterday.
Has any one run across something like this?
THanks.
David Spaisman
View 1 Replies
View Related
