Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
I need to transfer data from one table to another.I will be using the SQL Query Analyzer to do this.This is not a simple transfer of data to the same structured tables.These tables are completely different, for the most part.For instance, I will be selecting certain fields of one table.....SELECT fldOne, fldTwo FROM someTableI need to take this information, one row at a time and input it into a different type table.So, something like this to insert into the other table...INSERT INTO otherTable( fld1, fld2) VALUES( value1, value2 )I've looked around for a sample to achieve this, but may have overlooked it?Anyone have a link to show this or a quick sample?Thanks all,Zath
Hello,
I created ssis package to transfer tables from one DB to another.
However, I don't find the option where I can make that the transfer with the indexes and pks of the tables. in sql2000 I had this option.
appriciate your help.
Kubyustus
for bcp in,
1. use fixed length format file or delimitered file?
2. table w/o index including primary key?
3. sort the text file before bcp in (will it speed up indexes creation after data uploading?)
which pt will or will not improve the overall bcp in processing?
thx...
Hello,
Just requesting for help about transferring data from one table to another.
For example:
In Table1, there are 3 columns:
Col1 Col2 Col3
T1C1R1 T1C2R1 T1C3R1
T1C1R2 T1C2R2 T1C3R2
T1C1R3 T1C2R3 T1C3R3
T1C1R4 T1C2R4 T1C3R4
In the target Table2, Col1 should be a string constant,e.g. "A" and Col2 from Col1 of T1 and Col3 from Col2 of T1:
Col1 Col2 Col3
A T1C1R1 T1C2R1
A T1C1R2 T1C2R2
A T1C1R3 T1C2R3
A T1C1R4 T1C2R4
I found something like
"INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1"
but this code requires colomn one of the table 1 be selected as well but the column one of the
target table 2 requires a constant string.
I also tried this but doesn't seem to work:
Code Block
dtTable1 = ds.Tables(0) ' Table1
bSQL.Length = 0
For Each dr In dtTable1.Rows
bSQL.Append("INSERT INTO Table2(Col1, Col2, Col3) ")
bSQL.Append(" VALUES(")
bSQL.Append("'" & "A" & "',") ' Constant for column 1 of Table2
bSQL.Append("'" & dr(0).ToString() & "',") ' All data in the first column of Table1
bSQL.Append("'" & dr(1).ToString() & "')") ' All data in the second column of Table1
.......
.......
Next
.....
.....
Thanks,
K
Guys
i have two database on my sql server,, and i m trying to create a report in whch both database need to share their data.. @ the moment what i did, i simply create a view on one database to access the table of other database.. but is anyone has a better idea how can i transfer data from one database to another database... i think if i do with creating temp table that might resolve this problem.. but when it comes on another server of another database , how can i will do this ...
please give me any idea if u got my point .
Thanks and looking forward.
hi iam new to writting stored procedures
so anyone please help me out.
i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.
thanks for your suggestions and answers
regards
ramya.
Is there a way to transfer ntext data from one table to another?I tried thisUPDATE [projects]SET [description] = (SELECT [description_ntext] FROM [table] WHERE[id]=1)WHERE [id_project] = 1;and thisDECLARE @DESCRIPTION ntextSET @DESCRIPTION = (SELECT [bids].[bid_conditions] FROM [bids],[projects] WHERE [bid_accepted_id] = [bids].[id_bid] AND [id_project] =@ID_PROJECT);UPDATE [projects]SET [description] = @DESCRIPTIONWHERE [id_project] = 1;none of those work in MSSQL2K,error reported is "The text, ntext, and image data types are invalidfor local variables."
View 2 Replies View RelatedWhat is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
View 3 Replies View RelatedI have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies View RelatedI am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
I have an excel sheet that contain colummns as in a table in a sql database i want to transfer this data from the sheet to the table frombusiness logic code layer not from the enterprise manager by wizardwhat can i do?? ...please urgent
View 1 Replies View RelatedHello:
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
Hi All,
can anybody help me in creating the SSIS package to transfer the data from SQL table in database engine to OLAP cube in Analysis services
Thanks in Advance.
Archana
I am newbie to MDS of SQL,want to know how we can transfer tables from two different SQL Databases to MDS.Suggest me the steps to proceed with any examples.
View 2 Replies View RelatedHi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
I've a SSIS 2008 parent/child package solution to manage data transfers between two different data sources, so we can copy multiple tables and capture how many rows were transferred and duration for each transfer. This solution was working fine up until last week, when I made some changes to allow the package to perform a source count using standard SQL determined by an expression, or SQL provided from configuration tables, I also changed the package to Truncate or not the destination table, again controlled by configuration settings in a table. The child packages which perform the data flows have not changed!
The day after the controlling package promotion to live, I saw the bizarre behaviour of the Package log stating all rows transferred, but the actual table counts were not what the log stated, see attached file. The package solution works ok on other servers and was ok in DEV, but there were less tables and rows transferred.Re-running the package gave the same errors, but on some of the same tables and some different ones.
As it is the child packages doing the transfers and nothing has changed in them. I cannot see how the log would be able to say all rows are transferred and yet not all of the rows are actually moved?
Process output - where you can see counts and log Table transfer controller (as txt not dtsx)
An example of the data transfer child packages (as txt not dtsx)When I set the ExecuteOutOfProcess = True the package worked fine, unfortunately, this is not a good solution as SSIS 2008 does not tidy up the Dtshost.exe processes it starts and I'd be left with a memory issue after a very short time, we transfer hundreds of tables each day. ( I could write a .net script in the controlling package to kill the child processes, but that would still have hundreds of processes running before I could end them, as we have three parallel streams to allow a bit better performance.
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
Hello:
I have been trying to run transfer manager to transfer all of the data
from the production database on one server to a test database on another
server(to refresh it). In order to make sure it runs on the server, I have
been scheduling it under EM to do so and I am pointing to the log on the
destination server on the EM Transfer panel.
For some reason, I am getting the following message in the destination
server error log:
99/02/16 10:24:41.42 ods Error : 17824, Severity: 10, State: 0
99/02/16 10:24:41.42 ods Unable to write to ListenOn connection
'.pipesqlquery', loginname 'sa', hostname 'TEMP09'.
99/02/16 10:24:41.42 ods OS Error : 232, The pipe is being closed.
99/02/16 10:24:41.42 spid17 Error : 1608, Severity: 21, State: 2
99/02/16 10:24:41.42 spid17 A network error was encountered while sending
results to the front end. Check the SQL Server errorlog for more
information.
I checked the event viewer error log and see no messages for today.
Can any one advise me what I need to do for this to run successfully?
Thanks. Any information furnished will be greatly appreciated.
David Spaisman
Hi everyone,I need some help transfering my database(SQL2005) to the host's server.In SQL 2000 I had the Management tools > DTS or ImportExport tools.SQL2005 lacks this, all I've got is Express Manager > can't connect to multiple databases.Can connect to one database and has a screen to excute TSQL commands.I don't want to have to recreate the whole database on the server.....This would be crazy... 150 stored procedures and 30 tables. Not to mention all the individual settings for each column.I know there is SQL Studio Management tools for the full version of SQL 2005. Anyone got any idea's ?Any input/suggestions is greatly appreciated.Thanks,JShep
View 1 Replies View Relatedcan any body can tell how data trnsfer from oracle database to sql server database. what is the methods i can use? is it required to write scripts.
if scripts required, can anybody can give me sample scipt.
SQL v7.0 - DTS
Tying to transfer all object info, sp's, constraints and data in to a fresh new database in the same instance, but it's hit and miss when it comes to getting everything transferred. Followed step by step instructions (GUI/Scripts) and sometimes the table structure will transfer without PK's, as well as some data and if you're lucky, some constraints. Stored proc's have a tendency to show up 20 minutes later. I'm testing this on a machine with IE 5, SP2 and fresh new Dll's (just in case something was corrupted). Is there a priority as far as what to transfer first? For example, tables only 1st, then constraints on down to records parent to child? I am assumming you should be able to transfer all at the same time since it's built in to the utility. Or, does it come down to something in the schema? If anyone out there has experienced something similar and found a solution, please advise.
hi,
I am having two servers one is 6.5 and another is 7.0.
I would like to export a one database from 6.5 to 7.0.
when i was trying to transfer the data it is saying u have to upgrade the server of 6.5.
pls tell me how i have to transfer data from 65 to 7.0
without bcp.
--raj
Hi,
here we have 5 servers,application server,datawarehousing server,web server,QA server and production server. so i suppose to transfer(by using DTS) the total data(GDWReports) from production server to QA server. so actually i exported that data from production server to one destination by using export wizard and i tried for import that data to qa server by using VB script.but i got one error that "login failed like that". so how to import the data. pl give the suggestions asap.
thanx
Janreddy
I want to transfer data from a sql2k database to access file.
How can I make a program which I can specify the database?
Can SQLDMO or bcp make it? then how?
thanks
I would like to ask about the Data transfer . I have huge data in Access database and want to transfer in the MS SQL Server 2000. Can some body help me how i can do it.
Thanks
I would like to transfer data from an SQL Server 2000(standard edition) on a server located outside of the department TO a laptop/notebook using MSDE located within the department. This will be 1 or 2 times a year of transfering the data from a server to a laptop. So what are some ways or the best ways of transferring data?
Thanks,
Don
We are working with a Mexican developer to share data between our English database (SQL 2005) and his Spanish database (SQL 2000). We are thinking the best way to gain access to his data (since our website will need data from both databases) is to have them Export data from their Spanish tables into English tables within their database and replicate those tables to our database several times a day. Does this seem like a good idea - any suggestions for a better way to share the data? Thanks
View 3 Replies View RelatedI have a table in a database that has just over 1 million records, eachrecord about 50-60 characters. I need to transfer this data to anotherdatabase. I implemented two options:1) Read the data record by record and insert into the destinationdatabase, using ODBC. I played with different commit intervals, up to100,000. This version takes about 12 minutes. Having indexes on thedestination table or removing them made little difference.2) Dump the data into a flat file, and use bulk copy to read the fileinto the destination table. This takes less than a minute.Are these results within the normal ranges? I am particularlyinterested in a way to improve upon the first method.
View 3 Replies View Relatedhi,My problem is I am creating a new database. I need to all the tablesand procedures fromone databse to another database in sql server 2000, both in sameserver.Kalaivanan.
View 1 Replies View RelatedHi,We have a unique situation here that is puzzling me. I have a programthat uploads data to our SQL Server. The program runs fine on WIndows95. I have installed it on a Windows 2000 machine and it will connectto our data but will not upload. It processes the file but the datadoes not appear in the database.I have checked the ODBC setup and everything is the same except I usedsqlsvr32.dll instead of sqlsrvr.dll for the 32 bit driver.I know this isn't a lot of information to go on, but the program waswritten by a contractor who has since been replaced and I don't reallyhave an idea of even where to begin checking.Any suggestions would be appreciated.Thank you,
View 1 Replies View RelatedUsing SSIS, how is it possible to transfer the content of an xml file into an xml/varchar(max) field of a table in sql server 2005?
Thanks