Moving From One Table To Another

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


ADVERTISEMENT

Moving From One Table To Other Table Automatically For Every 3 Months By Checking The Paticular Value Of The Table Field

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

Moving Lots Of Rows From Table To Another Table

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

Moving Data From One Table To Another

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

Moving ONE Table To A Different Filegroup Using T-SQL

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

Moving Data From One Table To Another

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

Moving A Table To A Different Schema

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

Need Help Moving Dates From One Table To Another Using A DTS...

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

Moving One Table Form One DB To Another

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

Moving A Big Table To Another Server. Best Way?

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

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 View Related

Moving A Column Of Data To Different Table.

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

Moving Data From One Table To Another Using Variables

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

Moving Record From View To A Table

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

Moving Table To New File Group

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

Moving Table(s) To A Different Filegroup In SQL 2005

Oct 2, 2007

Is there anyway to move tables without any indexes to a filegroup?

View 1 Replies View Related

Moving Data To A Table With A Different Field Structure

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

Moving Table To A New File Group (SQL2K5)

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

File Groups: Moving A Large Table

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

??Moving Table With Data To Another Database In Sql Express 2005??

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

Partitioned View Broken After Moving Table To New Filegroup

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

Problem While Moving Data From Excel To Table Using SSIS

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

EM Transfer Manager Fails Moving Data After 80th Table?

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

Transact SQL :: How To Get Moving Total Like Moving Average

Nov 10, 2015

I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.

View 3 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float

Jun 11, 2014

So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.

Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,

[Code] ....

View 1 Replies View Related

Reducing Storage Space By Moving Rarely Required Columns To Separate Table While Sharing Primary Key Constraint

Aug 17, 2006

Dear all,

In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.

During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.

However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.

Hence, I had to create a quite awkward design to enforce the constraints:

1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.

Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.

Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.

Thanks in advance for any advice.

View 6 Replies View Related

Moving One Table From One Server To Another Server Using Import/export?

Mar 18, 2008

I have two databases on two different servers (an old hosting solution and a new). I need to get all the data from one server to the other but I have no clue as how to do it with MS SQL Server Management Studio Express.

I can easily make the create table scripts that will allow me to create the tables on the new server but what about all the data. I wondered if there is some way to export and import data from one server to another?

With the old SQL Enterprise Manager I was able to import or export as I saw fit but I can't seem to find anything like this with the express manager.

Anyone has any advise as I would like to avoid coding somekind of export program myself,


Many kind regards

Weinreich

View 3 Replies View Related

Moving ALL DB&#39;s

Jun 28, 2000

does anyone have a script that will read the sysdatabase table and transfer all datbase's to another server. example pseudo code:


start loop
get local sserver db name
create db on other server
transfer ALL objects to new database



end loop

View 9 Replies View Related

Moving SQL From C: To D:

May 29, 2002

I have inherited an SQL server that is running out of disk space. SQL 2000 was installed on a regretfully small C: partition, and I need to move as much as I can to the D: partition. I really don't want to uninstall and start over, and I am hoping there is a whitepaper or something that I can use to accomplish the same thing.

Any help is appreciated!

View 2 Replies View Related

Moving DB From X64 To X86

Apr 26, 2007

My company recently purchased a new ERP system which does not support 64 bit SQL Server. For this reason, I need to uninstall the 64 bit SQL server 2005 and install the 32 bit version. This leads to my question. I have an application currently using a database on the 64 bit version and would like to confirm that the DB will work correctly in the 32 bit version. Do I just have to detach the database, copy the files, then install SQL server 2005 32 bit and attach the DB?

View 4 Replies View Related

HELP MS Access Moving To SQL And Don't Know How

Oct 6, 2006

I have been using MS Access for a long time.  The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run)  We get the errorSystem.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()at System.Data.OleDb.OleDbConnection.Open()at DreamweaverCtrls.DataSet.DoInit()Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up.  Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly.  If people can point me in the right direction that would be very helpful.  Everything I find online is people needing help but no solutions that work for me.  My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.1. A good book to follow and easy to follow for SQL (connections / how tos) or website2. Good examples or book on "ASP.Net Enterprise Manager"3. How to convert access databases into SQL easy (some have over 50,000 listings)4. how to convert Tab files into SQL easy.Thank you so much for any help.  Also I am using Dreamweaver for most of the edditing and connections.  I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.Thanks again,Rusty

View 2 Replies View Related

Moving A Database

Apr 23, 2007

Hi
I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)
I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder).
I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

View 7 Replies View Related







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