How To Import CSV Data Into Table With Foreign Key Conversion
Nov 17, 2013
Our SQL 2008 R2 relational database has tables with foreign key relationships for part numbers. We receive production data from a separate program and we need to import the CSV data into our database application.
The problem is our separate program creates a CSV file with the actual part number "362S162-33". In our database we have a separate parts table (example: 362S162-33 has identity "15").
We need to import data into a production table that has a "part number" (FK) column.
How can we, when importing, cross-reference the "parts table" to convert the part number to the identity number. We have thousands of parts, so we need this change of part number column to the FK identity automatically on import.
Production Table:
idComponent (PK), [1000]
ComponentName, [Assembly108]
idPartNumber (FK), [15]
ComponentLength, [230.5]
UserMessage, [Assembly is 230.5 inches using 362S162-33]
Qty; [1]
View 4 Replies
ADVERTISEMENT
Oct 3, 2007
I've imported data from an Excel spreadsheet to a table that has fields to match the destination table I'm trying to populate. The destination table has an Insert trigger with several checks on certain fields to make sure they have corresponding records in other tables.
If I do a statement like
"INSERT INTO destinationTable
(
ItemId,
Product,
SuperID,
etc etc
)
SELECT * FROM oldtable"
it runs for a while then gives me error messages from the trigger and rolls back the Insert.
The trigger has code such as
"IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.Product Is Not Null))) != (SELECT COUNT(*) FROM tblInProduct, inserted WHERE (tblInProduct.Product = inserted.Product))
BEGIN
(Error message code goes here)
END"
So, do I need to do an INNER JOIN to each of the related files?
When I try that, I get this error:
"Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
Is SQL counting the foreign key fields as separate fields, or what?
View 6 Replies
View Related
Aug 28, 2006
Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.
Any help would be appreciated.
View 1 Replies
View Related
Jan 28, 2008
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Jul 23, 2005
This is a rather abstract question about data design, but I ask it herebecause a) the database is SQL Server, and b) you're such a learnedbunch!Let's assume the classic relation of Customers and Orders, where anOrder may reference a single Customer. If I was designing such arelation from scratch, I would create the Customer table with anIdentity column and call it CustomerID. The Order table would containa column called CustomerID, a foreign key to the Customer table.So far, so unexceptional. However, in my current project I have towork with legacy data that comes from a number of old Access systemswhere the data was not normalised. I wish to normalise it.The main table in this new system contains reports on parts. Eachreport may reference a single part. However, the old data which I haveto import allowed the user to type in the part number. This has led todirty data (for example, '40-7889-9098' appears, as does '40-7889-9098') so I will clean this data up. In the application, the partnumber will be selected from a drop down list, though the administratorwill have access to a builder to add, amend or delete part numbers.So, my report table needs to store a reference to a part. When Iimport the data into my SQL Report table, I will initially bring acrossthe part number. I will then populate the Part Numbers table with alldiscrete, distinct part numbers from the Report table. My question isshould I then create a PartNumberID column in both tables, and "backpopulate" the Report table with the PartNumberID which corresponds withthe matching PartNumber - e.g.UPDATERSETR.fldPartNumberID = PN.fldPartNumberIDFROMtblReports RINNER JOIN tblPartNumbers RNON R.fldPartNumber = RN.fldPartNumberI could then drop the fldPartNumber from the tblReports table.My question is - should I bother? Or can I just leave the actualPartNumber in the Reports table, and leave the tblPartNumbers tablewith a single column which is both Primary key and Foreign key?Sorry if this is poorly expressed - I had a tough weekend!Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 4 Replies
View Related
Mar 12, 2008
I recently learned to use SSIS to import a database from a SQL 2005 server to a local SQL 2005 development server. It was working fine for a custom database, but now I'm trying to use it on the membership tables that ASP.NET creates to manage login, profile, and so on.
I created the package, specifying that I wanted to delete data in the destination, and turning on identity insert. I only need the data in a few of the tables, so I am not copying empty tables. When I run it, I get the error:
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [aspnetdb].[dbo].[aspnet_Roles] " failed with the following error: "Cannot truncate table 'aspnetdb.dbo.aspnet_Roles' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
How can I safely allow it to overwrite the foreign key constraints? Is there a special procedure I need to use with membership (aspnetdb) data? I am just beginning with this, so this is probably an elementary question. Thanks for any links or explanations you might know of.
View 2 Replies
View Related
Sep 27, 2004
Hi,
Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.
column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int
pId has been set to primary key with auto_increment.
My csv file has 2 columns of data and it looks like follows:
program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"
Now i use BULK INSERT like this
"BULK INSERT ord_programs FROM 'C:datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='', FIRSTROW=2)"
to import data into my table in SQL server and it gives me this error
"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"
I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...
Please help me out guys and please post a snippet of code if you have.
Thank You.
View 2 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Oct 5, 2007
I have 4 tables I am pulling data from. Table A and B can be joined and Table C and D can be joined via foreign keys but A and/or B can not be Joined to C and/or D via foreign Key.
My statement basically looks like this to start
SELECT s.HTENANT, s.HPROP, s.HUNIT, s.SHEADNAME3B, s.DTOT7I
FROM H8summ S join tenant t on s.htenant = t.hmyperson
And returns this :
29226 128 20577 Almukhtar, Khadijah M 4242.00
26574 128 17980 Archie, Mary 20218.00
The last number is the key it is the income.
In another table we have an income limit table setup that would have the low, medium and high settings for the range.
For instance it may have 11400 as the elimit attribute and say 22000 as the vlimit attribute.
Then in the fourth table we the description that can be linked back to the income limit table via the hinclimit attribute. In this case the desc attribute would be ELI for elimit and VLI for vlimit.
So I want to take the income compare it to the income limit table and then return the description
The output would look like this :
29226 128 20577 Almukhtar, Khadijah M 4242.00 eli
26574 128 17980 Archie, Mary 20218.00 vli
I just not sure how to reference the income limit table without the foreign key in the from statement. When I just add it it returns 6 rows for each person (number of rows on the income limit table)
View 1 Replies
View Related
Dec 10, 2006
[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.
I am using flat file import method.
Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.
I am using SQL server 2005.
Please advise what need to be done to avoid this error ?
Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.
Please help.
regards,
kong
View 1 Replies
View Related
Dec 10, 2006
[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.
I am using flat file import method.
Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.
I am using SQL server 2005.
Please advise what need to be done to avoid this error ?
Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.
Please help.
regards,
kong
View 1 Replies
View Related
Nov 11, 2015
Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-
Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.
View 7 Replies
View Related
Jul 8, 1999
I am a novice in data base designing. I have MS certification in both design, implementation and administration. Can anyone help me provide information regarding converting from Cobol flat files to sql server tables ?
I will be very grateful.. This is my first assignment.. Got to prove myself... help....
View 5 Replies
View Related
Jun 21, 2006
I am copying data from one denormalized table to a COUPLE of normalized ones.
I am using multicast, following advices from the forum.
The problem I have is that the two destination tables (A and B) are sharing a foreign key relationship.Filling in A is no problem, but when I want to fill in B, I don't know how to populate its foreign key, since the multicast doesn't know the corresponding primary key in table A.
View 9 Replies
View Related
Jul 13, 2007
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
View 12 Replies
View Related
Feb 16, 2008
Hello,
I will be getting data in either Excel or Access form on a daily basis. I would like to automate the process of converting this (excel or access) data to a table in an existing SQL database. Since this conversion needs to performed on a daily basis, note that I need to update the table that contains data from the day before.
Is it possible to do this and if it is possible, can someone tell me how to do it.
Thanks in advance,
Joe green
View 1 Replies
View Related
Sep 20, 2006
HelloI have created a table in mssql.2000 which holds details of names etc. I have also included categories of interest. However the table is growing very big and unmanageable as the list of interest expand.Instead I would like to create seperate tables for each category of interest within the same database and populate the table with names taken from the Names_Table I could then indicated yes or no if any name is interested in this category.for example: Art_category. However, I am unsure how I can import the column of names from the Names_Table to polulate the NameID column in the Art_category table.I would appreciate advice and possibly a link to step by step tutorial.Thanks.Lynn
View 3 Replies
View Related
Feb 15, 2006
Hi everyone,I have some data in a CSV file, and I have to import it into a table. For some reason, I am supposed to import this data into a temp table and then move it to the original table and I have to convert it to the right data types while I do this. Is there a better way to do this and how can I give custom error messages saying, for e.g., the data type cannot be converted, the right number of records are not present etc.
Thanks for the help.
View 1 Replies
View Related
Apr 30, 2006
Hi, I'm new to SQL Server, and would appreciate some advice on the quickest way to import data from a CSV file.
I've created a database using Visual Web Developer Express, and added a couple of tables. The Help file in SQL Server Express (which is installed on the same PC) indicates that I should use BULK INSERT to populate the table. Only snag is, I could find anywhere to enter the commands! Eventually, I found out about the SQLCMD command which I entered in a Windows Command Window. I successfully connected to the default (SQLEXPRESS) server instance this way, but when I typed USE <my database name> I got an error back saying it couldn't find the database. I know that Visual Web Developer Express by default creates user-specific instances of the database, but I've turned that off (I think!) via the connection string. So, please could someone tell me how I can connect to my database via the SQLCMD command, or alternativley please let me know how else I can bulk inmport data from a CSV file. Many thanks in advance.
View 4 Replies
View Related
Oct 8, 2007
Hey folks,
My first post and I hope not my last!!
I am very new to the entire world SQL Server databases. I am starting from scratch.
Currently I have a little Website I am doing for myself that is .asp based and will allow users to query some sports boxscores. I hope to create a user interface that will allow folks to seperate team results based on certain criteria...
It is just a hobby of mine that I have been doing for year with excel and now hope to let others like me do it aswell.
here is what I got.
MSSQL 2005 Server with a database.
Iam using SQL 2005 Server Express Studio. Therefore, do not have access to SSIS or DTS or anything like that.
However, I want to import several hundred records into a db I created (hosted by Crystal tech). Since, I don't have access to the Server root directory, I can't use the BULK INSERT statement.
I am looking for a method to query an excel file (or .csv something..) that is stored on my local drive and upload it to the Server db tables.
I would like to do this either through SQL with a query. Or I would to add this VB code to the current VB that I use in my Excel file.
any ideas..??
Thank you for supporting me in advance.
View 8 Replies
View Related
Oct 17, 2007
How to refresh table data on sqlserver without dts or backup/restore whole database.
In sqlserver, is there a way to import table(s) data (like import export in oracle) in a file and export to another unconnected server/db.
Please advice.
View 1 Replies
View Related
Oct 6, 2005
I'm trying to use DTS to import data from an XLS into a SQL table.It works fine in that it INSERT's the data. However, I need it toUPDATE the table, based upon a ProjectID. Can this be done?Can a DTS package be fired from a SP using parameters?Eg UPDATE tProjects SET MyField1=XLS.Sheet1.CellA1,MyField2=XLS.Sheet2.CellA1 WHERE ProjectID = @ProjectID.Also, it must handle dynamic XLS file names, eg 981-Budget.xls,513-Budget.xls, xyz-Budget.xlsIs this the best way to go? Other suggestions most welcome?Thanks everyone in advance!
View 2 Replies
View Related
Aug 20, 2007
Hi!
I have to develop an application for transfering data from an excel file into a sql table.The excel file is uploaded to a server.The database(and the table) is on another server.At first,I used openrowset for transferring data to the table.My sql command looked like this(in my asp page):
SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+Server.MapPath("upload/tmb2.xls")+";hdr=yes', 'SELECT * FROM [Sheet1$]')"
I kept getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]
After reading a few articles,I think the cause of my error is that the excel file is uploaded into the folder where the asp script is located.I have 2 servers : one running the asp scripts and one containing the database.
Is my error generated by the fact that the excel file is on a different server than the sql server?How could I make this work?
View 4 Replies
View Related
Aug 3, 2006
I am new to SSIS, and was only a novice to intermediate skill level with SQL 2000 DTS, so please excuse me if this is an easy question. I am trying to import data from a table in one DB into a table in another. After insertion, I need to store the newly created ID (an identity seed) in a separate table that maps to the original DB's row id. My eventual goal is to import a bunch of related tables from the old DB into the new DB, and maintain relationships, so the mapping of newly created IDs is necessary to make sure data is imported with the correct relationships. Any advice would be greatly appreciated!
View 7 Replies
View Related
Feb 26, 2008
i have a table in sql 2000 db and want to import data from excel sheet in to the table.
my table = Table1
excel file = data.xls
is there a simple method where i can import data from the sheet into the existing table?
View 5 Replies
View Related
Dec 13, 1999
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.
View 1 Replies
View Related
Jul 9, 2004
I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.
The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.
I am obviously missing the right bit on Books online
any suggestions?
many thanks
Steve
View 1 Replies
View Related
Jul 17, 2013
I am using the import wizard in SQL Server 2008 R to import data from an Excel spreadsheet into a table I have created.
The spreadsheet contains 3 columns that SQL recognises as DOUBLE and they contain a 1 or 0. What data type do the corresponding fields in SQL table need to be? I have tried BIT, INT and FLOAT but keep getting an error (can't view details of the error because I get chucked out every time the error pops up). I know the problem is with the DOUBLE data because when I 'ignore' those columns the import works fine.
View 2 Replies
View Related
Jan 3, 2014
I am looking solutions to import csv data into my SQL database table. BUT we want to collect the data from specific columns in the csv file, (NOT the whole csv file) into SQL Database Table.
View 4 Replies
View Related
Mar 18, 2006
I need to make a script in SQL 2005 to import data from an Excel sheet into a SQL table.
I am using the wizard to import now.
Import from Excel 2000. First row of the excel sheet has column names.
Excel file name is: EXL.xls, sheet name is: Sheet1
Destination sql database name is: NM, table name is: Sht1
I use SQL Server Authentication to access the database.
User name: ABC and password: DEF
Database name is: DB
I am using the following setting when importing now:
- Delete rows in destination table
- Enable identity insert
View 3 Replies
View Related
Sep 11, 2007
Hi,
I need to import data from more than 10 excels having the same format in to a single sql server table.
I tried to use
INSERT INTO MyTempTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 11.0;Database=C:Book1.xls', [Sheet1$])
but got the below error
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
If DTS package is used then I am not sure how I can place 10 excels at a time so that they can be picked one by one and data is imported in to table.
Please help.
Thanks,
Vimalraj
View 1 Replies
View Related
Nov 27, 2007
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
View 2 Replies
View Related
Sep 23, 2015
T-Sql query to import data from CSV to sql Azure table.
View 8 Replies
View Related
