Hi Guys,
What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
Hi Guys,
What approach should I use to copy content of a text file.
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
for MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you
I need to make something that copies all tabels (structures + data) from a progress database into another mssql2000 database.
THANKS!
Hi all,need advice on the following task:copy the content of a big table from DB_A to DB_B in the same serverthe size of table:~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclusteredindexcurrent practice:use DTS to copy the data, takes over 20 hours as-- first had to delete existing data of the table in DB_B-- then copy-- all these happen while all indexes are in place.I am trying to check what is the best or most efficient way to copythis kind of data and what wouldbe the expected time for such load.my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion600 SAN.
View 2 Replies View RelatedHi,
I'm using SQL Server 2005 Management Studio Express.
I have a database used for an application and I need another copy of that database so I can fix some bugs.
I tried attaching the mdf to the second database but it gives an error.(a database is allready attached)
I need the 2 databases on the same server.
Thanks,
Medeea
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |
I though of using the LAG function but the table does not have any specific order.So after running the select, the table should look like:
date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |
I have to copy a large (3000) amount of different tables from a Oracle machine into an
SQLServer machine.
I am able to do this using a (VB) script.
I use now several methods:
1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')
3) Also used OPENROWSET method (similar to 2)
For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
Also, the DTS seems to bulk copy the data directly into the desired database while the
mentioned methods first fill the tempdb, then the transaction log of the desired database and
then finally the desired table (need very much extra space on your filesystem).
The total size of data is about 300GB.
Can anyone supply me with a simple example how to copy data from an Oracle table into a
SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
I read the bcp (which I use for import/export files) and bulk insert commands, but
I do not understand how to use them in this question.
how can I copy the content of the folder (including sub folders) from FTP location using ssis.
View 4 Replies View RelatedCode Snippet
Hi there,
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")
DaSQL.Update(DsSQL, "Tabella")
Note I tried also the following, withou a result;
DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")
Please is there someone who could respond !!???
HI,
We are using SQLLDR to send data from SQL Server to Oracle database. This utility creates a log file. I want to know whether there is a provision to change the contents of the file. We use this utility in multiple SQL Servers to send the data to the same Oracle Server. Can we add the source server name in the file so that identifying which server does a particular log file belong to? How?
Help would be greatly appreciated. Thanks a lot..
Manoj Deshpande.
I know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
Hi,
I have never use sql server to store binary data. Now, I need to store a .doc file into it. Which column type I should adopt?
In addition, could you provide me related articles to study?
Thanks,
Ricky.
Hi,
I have a requirement where I should be reading the file (I will use File system Task for this) and the header content. I should be able to read the header, validate and when the validation succeeds , I should start process the rest of the file.
To elaborate this further, take a sample Example. Assume that the header will have the date information. In a given folder I should read the file, will check the date (header being the first line of the file). If it matches the current date, I will start processing and on completion I will archive my files.
Can someone help me on this sample. Thanks
Regards,
Explorer
Hello everyone!I'm having a problem with inserting the content of a text file into a Sql Server 2005 database.I'm reading the text file into a dataset, and works fine. What I can't do is what I suspect is the simple part: Insert all the data into a table that has exactly the same configuration that the file. I've never worked with dataset's before, and I can't seem to find the answer to this!This is what I have done so far: Dim i2 As Integer Dim j As Integer Dim File As String = Server.MapPath("..DocsFactsFORM_MAN_V3_1.txt") Dim TableName As String = "Facts"
Dim delimiter As String = "9"
Dim result As DataSet = New DataSet() Dim s As StreamReader = New StreamReader(File) Dim columns As String() = s.ReadLine().Split(Chr(9)) result.Tables.Add(TableName) Dim strs1 As String() = columns For i2 = 0 To CInt(strs1.Length) - 1 Dim col As String = strs1(i2) Dim added As Boolean = False Dim [next] As String = ""
Dim i As Integer = 0 While Not added Dim columnname As String = String.Concat(col, [next]) columnname = columnname.Replace(Chr(9), "") If Not result.Tables(TableName).Columns.Contains(columnname) Then
result.Tables(TableName).Columns.Add(columnname)
added = True Else
i += 1
[next] = String.Concat("_", i.ToString()) End If End While Next i2 Dim strs2 As String() = s.ReadToEnd().Split(Chr(13) & Chr(10).ToString()) For j = 0 To CInt(strs2.Length) - 1 Dim items As String() = strs2(j).Split(Chr(9)) result.Tables(TableName).Rows.Add(items) Next j So now I have my dataset populated with all the information, but how can I insert it into the database?If anyone can help I would appreciate very, very much!Thank you Paula
Dear friends,
I have some images inside my database. How can I export the content of an image field to a file in a Windows Directory?
Thanks!
I have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
Hello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
This delete's the directory content as well as sub directory content! Is their a way i can just set it to delete the files in the said directory and not sub directories?
View 11 Replies View RelatedHello,
I have setup a package configuration and it ran fine; however, I would like to be able to use the script task to change content of the dtsConfig file. For example, I can get the newServerName using the SQL Task, then how can I assign the newServerName value to the DataSource in the package configuration file? Is this possible?
Thanks,
Andrew
Hi. I am currently building a blog application, and I need some advice... I have the post title, post date, post by, etc stored in a database table, however I was wondering whether I should store the actual post content in the database as well or in a text file. For example, if the posts get really long would it slow down database performance or would there not be much of a difference? Furthermore, if I wanted to keep the posts private, a text file would not be ideal as it can be accessed easily by surfers... What do you recommend?Thanks a lot
View 1 Replies View RelatedI get error reports in simple text files like the one below in relatively the same format. The only thing that varies is the number error reasons as there can be any number of error reasons for a file. Usually there is only one but there can be a handful. What is the best way to capture the error description and count of errors no matter how many there are? I want to take these items and update a table I have in sql server 2008r.
Report Message example:
************************************************** *****
Original File Name: some.file.YYYYMMDD.d.incr.02of02.1.dat
Source File ID: file02YYYYMMDD
File Receipt Date: 10/17/2014
Total records received: 1331136
Total records loaded: 1329987
ERROR REASONS
Error code: EBBW002 Error desc: Duplicate Record Total records: 1146
Error code: EABC001 Error desc: Invalid Length Record Total records: 1
Error code: ERRCM10 Error desc: Missing First Name Total records: 2
Total number of Errors encountered during the ODS update processing: 1149
************************************************** *****
I need to implement a file system for an application that allows me to roll back to a point in time. I can do this with either a journaling file system (Unix based such as JFS) or with a database file system such as Oracle Internet File system (now Oracle Content Services). I would MUCH prefer to use SQL Server but cannot find anything that supports this other than a 2000 ppt referencing the then up and coming SQL Server .NET File System.
The application(s) in question are older and store data in proprietary data files and need to access a local (or mapped) drive in standard form (d:programsmyprogram).
Does SQL Server 2005 or 2008 support this type of access? I have searched but cannot find anything to support this.
Thanks.
B.W.
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
Hi,
I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:dest and then upzip the files. Once this is done, sent an notification email to user@mydomain.com.
Thanks,
According to the help for SSIS, one method of deploying an SSIS package
to a SQL Server,
http://msdn2.microsoft.com/en-us/library/ms137565.aspx, is to use the
File...Save a Copy of <package file> as... menu option.
I don't have that menu option at all. And yes, the package is in
focus. My save menu options are simply; Save Selected, Save
<package file> As... and Save All.
I am using Version 9.00.1399.00 of the SSIS Designer.
At one time I did have the Management Studio's CTP installed.
However it was uninstalled before installing the tools from the
Standard Edition. (it would seem like not completely however)
Your help would be greatly appreciated. Thanx much.
p.s. Almost forgot to mention... I am already aware of using the
DTSInstall utility as a workaround. It should be noted, however,
that despite enabling the "CreateDeploymentUtility" property, the
DTSInstall.exe is not copied to the binDeployment directory.
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
hi,
I need to transfer my database from one computer to another. I am using SQL SERVER 2000. So I made the script of the database and transfered to other computer. Now when I try to run the script it gives me error that (mydatabase name).MDF file missing. So I tried to copy the .MDF file from my computer. but it says that access is denied and sharing volilation. Can anyone please help me that how can I copy the .MDF file and remove the sharing volilation.
Can anyone give best way to copy file from one server to another in the network in an automated way?
Di.
how can i create a mdb file which represents a current database in sql server managment studio? i thought this would be obvious but it's not..
View 1 Replies View Relatedhi guys,
anybody know the syntax in SQL to copy file to other server.
we want to copy our db backup files automaticaly to other server.
thanks
-wei
I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,
Thanks