I have an existing table I need to add data to. The data is in a text file, and the existing table already has data in it (I don't want to delete this I want to add to it).
I used Microsoft's import utility but this created a seperate table with generic fieldnames (column01, column02, ect). Is there a step in this wizard I missed?
Hi everyone!
I would like to import an excel list into an existing sql server table.
How ist that possible?
Thanks a lot in advance!
Greetings from Austria, landau
I am using the DTS wizard and having problems importing excel into an existing table.
Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Good Morning, I need some assistance with SQL Server 2000 Importing Data.
When I import data from a text on a routine basis, three things must happen:
1. New records identified by primary key get appended to table.
2. Exisiting records identified by primary key get overwritten with new/(updated) data.
3. All other existing records are left alone.
Does anyone know how to Import Records with the following the criteria above? It cannot insert duplicate primary keys by nature, so it must overwrite those records!
This is being built into a DTS Package, but I need to get over this obsticle! Thanks for any guidance!
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
I have SQL statement like this ..
CREATE PROCEDURE [dbo].[GetProspectBySearch]
@StartRowIndex AS INT,
@MaximumRows AS INT,
@ModuleType AS VARCHAR(50) = NULL,
@ProspectCategory AS VARCHAR(50) = NULL,
@ProspectsWithoutContact AS BIT = NULL
AS
BEGIN
IF @Status = 'All'
BEGIN
SET @Status = NULL
END
[code]....
1) How do I can write count and paging statements in to single statement ?
2) There is parameter @ProspectsWithoutContact is using for differentiation  for count and paging statements. If @ProspectsWithoutContact is "1" then this clause ( AND ContactPerson = NULL AND TelNo = NULL AND FaxNo = NULL)  will be added in to both count and paging statements. How do I can write this in to single statement with out IF ELSE ?
write normal SQL with out dynamic SQLÂ ?
I have a sql server 2008 backend with an Access 2007 frontend database. Each time I export a query I get the following error:
Code:
Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables. Do you want to proceed anyway?
I don't know what if anything is actually missing because of the amount of data is more thant 6000 records. It seems everything exported but I would have to comb through the data to be sure.
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
Hello,
I was wondering if there was a different approach I should take in appending data to a table...
My destination table has about 94+ million records in it, and I have been taking two approaches to getting new files into this table:
1) I do a data pump task in a DTS to import the file to a trans (temp) table, which is truncated every time, and then do an INSERT INTO statement from the temp table to my destination table.
The import to the trans table only takes a few minutes (about 1 - 2 million records per file, but have short record lenghts,) but when I do the INSERT INTO statement, it takes upwards of 6 hrs to append.
2) I have tried doing a bulk insert task, going directly to the destination table (which defeats the purpose of my trans table to check out the data prior, but I feel the data is clean at this point.)
I am running the bulk insert right now, and it's been running for over 3 hours...so I'm going to assume this will take just as long as the INSERT INTO statement does like I did before.
My destination table does not have any indexes in it at all, and I don't need to do any transformations to the data when bringing it into SQL since the data is clean. Also, I have a default value constraint on one of my fields on the destination table.
Plus there are other ppl and applications hitting the server which could impact the overall processing, but nothing out of the ordinary is going on the server today. I know there are only so many ways to get a file into a table...but maybe someone knows a different way I should try this.
Thanks for anyone's suggestions!
Kael.
good morning,
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Best regards.
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
Thanks again in advance
RB
Hi,
I wish to export a MS SQL table into a text file. I know that I can do this under the DTS wizard but I need the syntax so as to run in on my script. Can anyone help?
Thanks!
Hi.. anyone know wat the syntax to output the table content to a textfile? and copy back the content on the textfile back to the table?
Must use SQL command for MS SQL Server 2000 cannot use manual.. tks!
Declare @cmd varchar(255)
select @cmd = 'bcp "select * from mytable" queryout c:cptest.txt -Uasdfasdfasdf -Pasdfasdf'
when i run this it says "The command(s) completed successfully." but i don't see that file anywhere at all. why is that? is this the right way to do? please help.
I ask for your help for problem of SQL Server 7 DTS .
I got 6 text data files extracted from a SQL server database Sale1 in Company A. I also got a script produced from Sale1 and run it in my company to build a SQL server database A_Sale which structure is the same as Sale1. Then, I transferred these files into my database A_Sale. I have successfully transferred three files (vlet, vlrl, and vlcv), without any trouble. But I met trouble for transferring other three files (vlcl, vlco, and vlpo). For example, the error messages are as:
"Error during Transformation 'DirectCopyXform' for Row number1. …Conversion invalid for datatype on column pair 12 (source column 'Col012'(DBTYPE_STR), destination column 'cl_dob' (DBTYPE_14))."
"Error during Transformation 'DirectCopyXform' for Row number 10825. …Conversion invalid for datatype on column pair 17 (source column 'Col017'(DBTYPE_STR), destination column 'c0_stchdt" (DBTYPE_14))."
- I don't understand why the datatype is not matched? The datatype is 'int' in both source column and destination column and the data for the source column is like 2533222. And, why the error caused for Row 1 for file vlcl and for Row 10825 for file vlco? How to correct the error?
Other errors I met like:
"Error at Source for Row number 24391. …Column Delimiter not found."
- I don't know how to avoid the error at source? My partner in companyA also cannot control to avoid the error, since the file extracted by SQL server.
"Error at Destination for Row number 531377. …Could not allocate space for object '(SYSTEM
table id:-269896619)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full."
- Why the error caused for row 531377 and how to correct it?
Your help is greatly appreciated!
hi,
DataBase i am using is Sql Server6.5.
In a trigger i had written code to transfer updated records from one table to
other table.These updated records needs to be written into a text file.
I had used xp_cmdshell but it is taking time.Is there a way to write data to
flat file.
thanks in advance
karuna
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
i have bulk data that i dont want to have to enter manually. How can i achieve this for sql server. I want to be able to load from a text file (or any text format) with my data separated by delimeters. I know oracle has something that does this called sql loader.
View 6 Replies View RelatedThere doesn't seem to be a specific group covering legacy subsystems so I thought I would post this in here.
I have several hundred of these to import that are going to be left to run as they are until such time somebody has the time to migrate them to SSIS.
I have to say I'm not look forward to doing them one at a time using the brain dead UI (it folds up folders behind itself causing another two mouse click between each import). Is there a way of automating this import?
Thanks.
Dear All,
I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.
can someone give me nice example about these two options,
Thanks,
Hello,
Can somebody please help me with the following problem.
I want to import data from a textfile called "Links.txt" into a SQL-server database called "LinkData". The data in this textfile is separated by pipelines. And this import should be done every 2 hours automatically. How can I make a proces or something in SQL server 7 that will fill the database with the data out of this textfile every 2 hours. Please help me. I really don't know.
greetings
R. Jacobs
I want to import data from a textfile called "Links.txt" into a SQL-server database called "LinkData". The data in this textfile is separated by pipelines. And this import should be done every 2 hours automatically. How can I make a proces or something in SQL server 7 that will fill the database with the data out of this textfile every 2 hours. Please help me. I really don't know.
I was planning on using DTS for this Only I really don't know how. I tried it with "Data driven query task" in combination with "Text file (source)" and "Microsoft OLE DB Provider for SQL Server". But when I run it, it gives an error. What am i doing wrong??? Help me please
thanx
Hi,
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
Thanks,
I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
View 1 Replies View RelatedI must prepare a procedure which should export some data from some DB columns, but these data must be encrypted using the OpenSSL protocol, to be sent to an external recipient.This recipient sent to me a "public" certificate in PEM format, to what I understood (it is a file beginning with the expression "-----BEGIN CERTIFICATE-----", then a long Base64 sequence and then "-----END CERTIFICATE-----").
Then I didn't create the certificate nor I am a certification authority: I just have this public key (or, at least, a file from which I can extract a public key) and I must use it to encrypt data in order to obtain an output which must be analogue to what I can obtain when using this OpenSSL command line instruction:openssl rsautl -encrypt -in CF.txt -out CF.enc -inkey SaniteICF.cer -certin -pkcs
Then the question is: starting from this PEM file and nothing else, can I import it and store an asymmetric key or a certificate in SQL Server and use it to encrypt data to be exported?
Hi All,I'm coming from using MySQL, and in their dialect you could pull data from one table to another using the following: INSERT INTO Table1 (fname, lname)VALUES( SELECT fname, lname FROM Table2 )Let's assume Table1 is a simple table with the fields ID (PK/Identity), fname, and lname. This query would grab all the first and last names out of Table2 (fname and lname fields) and insert them into Table1, generating the ID for each new row.How would I do this in T-SQL?
View 1 Replies View RelatedUsing some VB sample code on the Internet I have the followingthat works well and exports a set of records to an XML file:Dim oCmd, sSQL, oDomSet oDom = CreateObject("Msxml2.DOMDocument.4.0")Set oCmd = CreateObject("ADODB.Command")oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);InitialCatalog=TestXML;UID=sa;Password=123456"sSQL = "<ROOTxmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & "select *from tblTest for xml auto</sql:query></ROOT>"oCmd.CommandText = sSQLoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"oCmd.Properties("Output Stream") = oDomoCmd.Execute , , 1024oDom.Save "C: emp estdts.xml"This is my first day of using XML in SQL Server and I need help on howto imitate INSERT statements by basically importing data from an xml file.With some changes I managed to write VB code that seems to be readingthe XML file I exported earlier but I can't seem to know what to do to beable to take the data only of the XML and INSERT it into a table.The code I used is the one found in the SQL Online Help, search in INDEXfor "OPENXML" and then choose "USING OPENXML"If you have sample code or can point me to a link that has sample code toachieve what I want, I would appreciate your help.Thank you
View 2 Replies View RelatedWhat I need to know is how to get repeated data from a existing table, to normalized tables with out typing it...
Example:
---------------------
| ID |TEXT | TAGS |
---------------------
| 1 |text1|repeating|
| 2 |text2|repeating|
----------------------
How to normalization would be use 3 tables ,one for tags_id and tags, and another relating the tags_id to ID from the table above...but how does the code already know there is a tag called repeating?
Hello,
I would like to create a stored procedure that opens a data connection for an excel file that I have saved. I would then like to export the excel data into an existing SQL table. Can anyone tell me the best way to go about this?
Thanks
Hi guys, I need to import all data from Excel spreadsheet to a Sharepoint Content Database (SQL Server).Please suggest the
best way to do this.
For this when i run the Import wizard under Tasks--> Import in Management Studio 2005 ....it asks me to choose the database
name etc....but How to use the Import/Export Wizard to Export Data from a .xls source to an existing table in a database....that is i need to append/insert my excel data into an existing table.
Thanks,
Kon
Hi everyone, I am new with SQL and I tried to use the code below to export data from Excel into an existing SQL table, but I keep on receiving the following message.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
To export data from Excel to existing SQL Server table,
Insert into dbo.Base_Intraday Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Shortcuts6 - Bolsa de Valores1 - Bolsa de Valores - Bovespa;HDR=YES',
'SELECT * FROM [Link$]')
Can anyone help me on this?
Thanks
Hi,
I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table.
I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table.
Thanks
Guy
Hi all-
I am in need of some help importing a .CSV file into a SQL Server 2005 Enterprise Edition.
The problem is I already implemented Bulk Insert task in SSIS but it is not importing any data. My detailed layout is as follows :
In SSIS package1 -
In Control Flow Bulk Insert Task has been inserted
Properties of Bulk Insert Task:
Connection adtc009d.ganny
Destination Table ganny.dbo.t4
Format
Format Specify
Row Delimiter {CR}
Column Delimiter Comma{,}
Source Connection
File r.csv
Options
Options Check Constraints
Maxerrors 20
This bulk insert task is connected to Data flow task, if we click edit to data flow task, data flow section will come, here Flat file source & OLE DB Destination is there. Flat file source is connected to OLE DB Destination.
Properties of Flat File
Connection Manager
Flat file connection Manager
here by clicking new link flat file properties to this.
Preview
by clicking preview all data are visible
Properties of OLE DB Destination editor
Oledb connection manager adtc009d.ganny
Data access mode: Table or View - fast load
Name of Table or view dbo.t4
After designing all this then if I start debugging I could able to get records are imported to a table.
Please suggest me where I am going wrong.
Thanks in advance
Karna