If I am trying to import data from an Access database into my tables via transact sql how and what are my first steps. I'm a newbie just trying to learn. Do I do via an insert statement and if so how do I do that. My SQL tables have no data in them and I would also have to make sure that can convert the datatypes from Access to SQL. Thanks in advance for anyhelp given.
View 4 RepliesI setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
View 4 Replies View RelatedI have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
View 6 Replies View RelatedI have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
thanks
hi
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
ex:
ARIZONA
ALABAMA
STATE
but i m getting imported
as :
ARIZONA ALABAMA STATE
How to Format a single column while importing?
Regards
Raj
Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
Thnx :)
Wouter de Kort
I'm new to SQL and DTS packages. I am trying to import data from an excel spreadsheet to an SQL server table via DTS package. It seems that the excel task looks at the first few records in a column to determine the datatype for that column. If the first few records are text, the entire column is imported as text. If numeric, the entire column is imported as numeric.
There are about 25,000 records. In one field, the most important one, about half of the records begin with letters and the rest are all numbers. It is the subscriber ID field, and some subscriber IDs are all numbers, some are letters and numbers. The entire column should be imported as text. However, when I run the transform data task from the excel connection, none of the records that are all numbers are imported. I end up correctly importing only 13,000 of the 25,000 records. The rest are imported with the subscriberID field as <NULL>.
I tried using the CAST or CONVERT function in the SQL query, but get the error message "Undefined Function."
Can anyone give me some help? Thanks,
Jim
hello,
I create a txt file with a bash script, and i need to use it in a DTS package. But, i don't know how i can specify the type of my column. So in the transformations task, i have an error due to an incompatible type. what can i do to fix this error ?
thanks,
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment
into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN
The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
I have created a DTS package for SQL Server, saved this as a VB file and upgraded this to the .NET framework.
It is now saved in a file called Shortages.vb.
What I want to do now is add this to an existing ASP.NET project and be able to call this DTS function by the click of a button.
Is this possible and how can I go about it? Can I just add it to the Click event of a button?
Hallo every one,
I', working for a school project.
The setuation is an old database that has to be repaird.
All the data from each table of the old data base are stored in different tabdelimited text files.
I designed a new database, but the tables are a little different.
When I try to import the data I get some errors, because of the changed tables.
An example of my new setuation is a new table thas as colums from 2 different tabdelimited text files.
My question is, how do I solve thos problem!
Do I have to use DTS?
What do I have to do.
tnx
icheron.
How can you can import results into table from system procedures.
For Ex. if you exec sp_spacesued, how do I import the results into the table.
Thank You
I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process. Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?
View 6 Replies View RelatedI am trying to import data from excel into my server, but get this error message:
Error during transformation 'directcopyXform' for row number 1. Errors encountered so far in this task 1. TransformCopy 'DirectCopyXform' conversion error: Conversion invalid datatypes on coulumn pair 19 (source column '*9' (DBTYPE_WSTR( destination column 'F19' (DBTYPE_R8)
Anyone get me in the right direction?
Scott
we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).
This method worked ok in DTS2000 and it works with Excel. Any suggestions?
Thank You
There's alot of discussion in this Forum concerning the importation of data into SSE.
I recently discovered that you can quite easily export tables directly from MSAccess to SSE. Simply 1) select the desired MSAccess table, 2) choose 'Export' from the file menu, 3) in the 'Save as Type' drop down, select ODBC databases(), 4) at this point, an 'Export' dialog with the name of the selected table appears, 5) click 'OK', 6) the Data Source Manager appears - go to 'Machine Data Source' and select a DSN that connects to SSE (you need to have set this DSN up before), 7) click 'OK' and and your table will be exported from MSAccess to SSE.
For whatever reason, the table you exported will always end up in SSEs 'Master' database tables. There is probably some work-around to correct this, but I havnt spent much time fooling with it.
Now, MSAccess has a very good parser for importing several types of external data, including Excel worksheets, CSV files, and text data. However, to get a satisfactory into MSAccess, you may need to edit your data files. Then, to import the data, from MSAccess select 'Get External Data' -> 'Import' from the File menu and just follow the instructions.
BTW, there is alot of confusing discussion concerning the ability of SSE to use DTS. Some have said that it is not supported, only in the full-blown version. Others have suggested you can download the DTS application from Microsoft and use it with SSE although it does not show up in the SSEMS directory tree.
I downloaded the file 'SQLServer2005_DTS.msi' and tried to install it. It appeared to install OK but I cannot find it anywhere on my machine. Weird, eh.
While most of this thread is in the way of a comment, I have a couple of questions:
1 - Is there some way to connect a machine DSN to a database in SSE that is other than 'Master' ?
2 - Are there better ways to import data into SSE ?
3 - Is there some way to move or copy a table from one SSE database to another ?
BTW, please do not respond to this thread by posting some link. Many of the links I have attempted to follow from this Forum are either irrelevant to the posted problem or are no longer available. If I wanted that kind of help, I could use Google or go to the library. Try to answer the question(s) directly. If you don't know the answer, say so, or at least, don't answer at all.
With SQL 2000 there was an Import/Export facility for importing data into a Sql database. Could somebody tell me how to import an old database which could be in Csv text or Paradox into my new SQL 2005 tables. Thanks
View 4 Replies View RelatedI have created an application that uses the login, create, etc login components in .net. How hard is it to convert all my old users, passwords, usertypes into the new tables. It almost looks like I have to do them by hand and created a new guid(userid), along with the same guid in the aspnet_usersinroles and aspnet_Membership. Is there a script to do this programatically?
View 1 Replies View RelatedHi i have an excel spreadsheet in which I want to take the data and put them in a table, the table and excel speadsheet have the same unique-ID, what i need to do is retrieve the extra fields of the excel spreadsheet and match them up with the table. Is this possible, if so how?
View 6 Replies View RelatedHere is the scenario: I have an excel spreadsheet that contains 182 columns, and I need to move this data into a semi-normalized database for reporting. The SQL Server database schema has 11 tables. Some of the tables are going to use identity columns for their PK, other tables are using a value that comes from this spreadsheet for their PK values.Anyway, I have never done a DTS package of any significance before, and know I most likely need to write some VBScript to handle sticking data into the proper data tables, etc.I am just hoping someone can point me at a good resource, give me an alternative means of doing this (this is a process that will need to happen whenever a new Excel spreadsheet is dropped into a folder or on a schedule, either one). I would love to write some C# code to handle these things, but a DTS package would probably be the best, I just don't know where to start.Thanks,
View 2 Replies View RelatedHi 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 RelatedI am building a aspx/c# application with SQL Server 2000 backend. Now i want to have the option for "Importing" the data into one of the tables in my database.
The source file for the import is a text file , CSV format. I want the users to click on the "Import" button placed on my webform and supply the souce file and the data should get imported into the SQL Server 2000 database table.
I want to know the various ways to implement this. Is it possible to invoke the DTS and then DTS will itself guide the users do the import? or if i need to write a SQL query , what would that be like??
may i know where to find more detail like how to importing and exporting data in ms sql server.?
Hope can get the more detail about it and also teach me step by step. (hope can include the photo).
Hi,
I am getting an error while importing data using BCP. The first field in the table is an identity and is a PK, and the data file does not contains the values for it. I am also using a format file. I am getting an error saying
'#@ Row 1, Column 1: Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. @#'
Statement used:
bcp FXPAL_DEV..PALDMF in C:FXPALRptOutPALDMF.TMP -fC:FXPALINPUTFILESPALDMF.FMT -ePALDMF.err -SAPSYDCBCNT66 -T
Error msg :
Starting Copy...
BCP copy in failed
Error message from PALDMF.err file
#@ Row 1, Column 1: Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. @#
Format file - PALDMF.FMT
6.0
20
1 SQLCHAR 0 4 "~" 0 PAL_KEY
2 SQLCHAR 0 8 "~" 2 PAL_DATE
3 SQLCHAR 0 3 "~" 3 PAL_BRA_K
4 SQLCHAR 0 2 "~" 4 PAL_LVH_CODE
5 SQLCHAR 0 4 "~" 5 PAL_PROFIT_CTR
6 SQLCHAR 0 7 "~" 6 PAL_PROD_CAT
7 SQLCHAR 0 11 "~" 7 PAL_BASE_NO
8 SQLCHAR 0 11 "~" 8 PAL_REF_NO
9 SQLCHAR 0 7 "~" 9 PAL_PAL_CAT
10 SQLCHAR 0 3 "~" 10 PAL_CCY_K
11 SQLCHAR 0 4 "~" 11 PAL_CCY_CODE
12 SQLCHAR 0 1 "~" 12 PAL_CCY_DEC
13 SQLCHAR 0 3 "~" 13 PAL_ADJ_COD
14 SQLCHAR 0 8 "~" 14 PAL_MOVEMENT
15 SQLCHAR 0 8 "~" 15 PAL_AMOUNT
16 SQLCHAR 0 8 "~" 16 PAL_POOL_COST
17 SQLCHAR 0 8 "~" 17 PAL_AVE_VOL
18 SQLCHAR 0 8 "~" 18 PAL_TRAN_DATE
19 SQLCHAR 0 30 "~"19 PAL_CAT_DES
20SQLCHAR 0 4 "
"20 PAL_UPL_K
Please help/advice
Thanks in advance
Rahul
Hello,
Can anyone guide me to import data from Sybase 11.03 to MS SQL Server 2000.
And how do I put the option Truncate log on checkpoint of Database in MS SQL Server 2000.
Thanks
Dinesh
Hello.
I want to transfer (Export or Import) my Database TEST1 to TEST2.
To do so I execute the following steps :
- Create an empty Database TEST2
- Create A login TEST2USR which will be the owner of the objests in TEST2
But the owner of the User Objects in TEST1 is TEST1USR
How can i transfer my objects using (Import/Export Utility) from TEST1 to TEST2 changing the owner (From TEST1USR to TEST2USR).
I understand that SQL 2005 now supports XML documents. I have a rather large file that is in XML format that I need to get into a table in SQL2005. I have tried the import wizard to no avail. I even tried to use DTS packages in SQL 2000 and still no luck.
Does anyone know of an article or information I can obtain on how to import data from and XML file to a SQL 2005 Table?
Here is a basic idea of the data as it is shown on the first row of the XML file.
Code:
<?xml version="1.0" encoding="UTF-8"?><SheetName Version="1.0" Date="2006-09-10">
<SheetNameLine>
<action>A</action>
<Id>1</Id>
<Code>ACCOMPS</Code>
<AddData></AddData>
<DataIssue></DataIssue>
<Type>DATA HERE</Type>
<Serial>123546789</Serial>
<Date>DATA HERE</Date>
<Updated>DATA HERE</Updated>
<Info>DATA HERE</Info>
<AColumn></AColumn>
<BColumn></BColumn>
<CColumn></CColumn>
<DColumn></DColumn>
</SheetNameLine>
This particular file is way too large to import into Excel and then into SQL, so I need to find an alternative way.
Any ideas? Help?
Thanks in advance.............
My flat file I import to the table is set up as 2004/06/16 09:40:07.994 comma delimited, and i import this using DTS into a datetime field. but when I run a query on the table, the only thing I see is 2004/06/16 showing up, how come I can't see the time?
Thx for your help!
I have an excel document that I need to import into a table. The format of the Excel spreadsheet has three columns:
|First Name | Last Name | Zip code|
This data is not normalized. I want to convert the data in the spreadsheet to foreign key values for two existing tables.
The table I'm importing to will contain records with a many-to-many relationship.
For example,
|John|Smith|77079
imports as:
|1|1|
Where "Smith" is the first record in the 'rep' table and "77079" is the first record in the 'zip' table.
How can I bring the data in and then convert the de-normalized data into ID values from my existing tables?
I have thousands of records that I need to do this for and want to automate the process.
Thanks for any help.
Regards,
-D-
I am using SQL 2005 the developer edition and need to import a couple thousand records while I try and create a web tool that will filter the data.
I know unltimately I will need to create some sort of application to move the data over. But I am not going to waste my time do that unless I am successful on the web site side.
What is an easy way to import the data. I tried the import wizzard multiple times and have recieved an error...multiple times.
Hi,
I need to import an excel data to sql table (excel 2003 and sql2005).I created a project in SSIS with appropriate components. Since my data in Excel is in DT_R8 and DT_WSTR format and my SQL data is in DT_I4 and DT_STR format I put a Data Conversion component to convert the data types. It runs without error and doesn't import anything to table!!
Any idea is appreciated
Hi
How can i import data from a csv file or text file
i have the text file located at C:Imports/Importdata.csv
the table that i have is as follows:
CREATE TABLE Invoice
(
InvoiceID INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)
i dont have any data for the invoiceID is it possible to import the data from the csv file .... the csv file partially is shown below...
Invoice_no, RefText, Customer details
272727, treesale, Garstang
345667, TidyBox, northampton
thanks