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.
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
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.
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.
I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.
Source Path & Filename:
@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]
Outputs:
\umrdwh2FTPCLAIMDAILY.DATA
Destination Path Now: (Filename not specified per another thread.)
@[User::WorkingFolder]
Original Destination Path: (Which did not work.)
@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]
I receive validation errors:
Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0
How do I overcome this without hard coding the path in the task?
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
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
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
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
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] ....
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.
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
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
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
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.
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.
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 RelatedWondering 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 RelatedI 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
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.
I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
View 2 Replies View RelatedI have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
View 1 Replies View RelatedHello,
I'm having problems using the update wizard to move data from 6.5 sql server
(on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and
passes me over to Dr Watson) when login fails for the 6.5 machine.
I am sure I have the right pasword (I've tried variations as well) and have updated the
hosts file so that the machine is known by it's name (I think one of the FAQ answers
suggested that)....
What other possibilities are there for moving the data ? I looked at bcp, but that seems
a rather long winded route (the data contains time stamps so I suppose there will be
a problem reading them in on the 7.0 side) ...
Many Thanks,
Paul.
Hi all!
What is the best way to move selected data from one database to another using SQL 6.5?
what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000.
Thank You
Hello,I am writing a function that uses two table variables. The structures ofboth are shown here:DECLARE @workdates TABLE (conflict CHAR(1),workdate SMALLDATETIME)DECLARE @existing TABLE (workdate SMALLDATETIME)I need to do an update on the first table:UPDATE @workdatesSET conflict = 'X'FROM @existing sWHERE workdate = s.workdateI am concerned that the unqualified 'workdate' in the WHERE clause willgive me an ambiguous column reference. Is this SQL statement valid?Thanks,Andrew
View 3 Replies View RelatedHey everyone,I read in a SQL Server book that you can now create a tablevariable.DECLARE @TMP TABLE (list of fields)Then you can you can use the statementINSERT INTO @TMPSELECT (whatever).I've tried it and it works. The book also says that youshould be able to pass these variables between storedprocedures and functions. Problem is, when I try todeclare the variable at the top of the procedure, thesyntax checker hates it.Anyone else out there try this out?SAM
View 1 Replies View RelatedWill reporting services allow the use of table variables in the SQL query?
View 1 Replies View RelatedI want to querry a table based on the input of a form, I have tables Monday, Tuesday ect., and want to display the records for Monday if the user selects Monday in a menu. I need to delcare a table name but am not sure how?
DELCARE @myVar char(20)
SET myVar = Request.Form("select2");
"SELECT * FROM @myVar"
I know user defined global table variables are not allowed in sql. I'm trying to avoid using temporty tables for speed reasons. I have a function in which a table variable is defined, and a function within that function that needs to call that table variable. Any ideas?
Thanks
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
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.