Having Problems Importing Data From 2 Tables Into 1. Please Help.
Nov 9, 2005
HiI am new to SQL and am having problems importing data from two tables to one.I have 3 tables : Franchise, Club and Reference.Franchise has FranchiseID as Primary key and FranchiseNumber as unique.Club has ClubID as Primary key.Reference has a combination of ClubID and FranchiseNumber as primary key. I am not able to insert data into table Reference from Franchise and Club. Any help or suggestions would be highly appreciated. Thanks in advance.
I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table.
I've not explained that very well, basically it's like this...
A B C D E F G H I J K L M N O P
1
2
3
4
5
Table 1 Data
cs# A1 fe B1 cl C1
Table 2
cs# A1 desc D1 date E1 pay F1 amount G1 vat H1 total I1 cReq J1
I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't.
Does anyone know how I can do this? I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM). I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table. Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this. I could use a join on the source tables but how would I direct specific columns to 2 different destinations? Any help on this would be great and appreciated. Thanks
I am going to set up a new SSIS package that will import data into 5 different tables on a SQL Server database. Â The source of the data is on another SQL Server and I will use to select the data. Â If one of the tables fail to import I do not want the SSIS package to import any of the data.What is the best way to create this package? Â Is it best to create one SSIS package, with five data flow tasks that are linked to each other. Â Within each data flow task, is a Source and Destination to transfer the data to each table. Â
Help - I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :
table MOTHER (MOTHER_ID, MOTHER_NAME)
table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)
After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.
My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".
The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...
Is there a solution to chain two insertions with a foreign key constraint in a data flow?
Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?
So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.
I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.
When I run the task it does execute with no errors, however the table remains empty. It looks like I am failing to read the actual data.
Do you have any suggestions? I am willing to go around this approach with stored procs/com/you name it €“ just make it work!
Hi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered....
Not sure if this is the correct Forum, anyway lemme me know your views. I have SQL Server running in two remote machines (in different geographical locations). I need to import a database from one SQL Server installation to another installation. What is best possible solution:
1) Transfering the .mdf and .ldf file from one installation to another installation
2) Generating the records of all the tables in the database to a common file format CSV and then loading it in destination database. If this is ok then how to generate import and export data using CSV format
hello, i have a problem after importing my db tables to the aspnetdb the default database that come with visual studio i put my tables into that db but i added some columns to the aspnet_users and i made a relation between my tables and some of aspnetdb tables the problem is when i drag and drop a grid view and manage its datasource i cant find the columns that i add to the table? second problem is when i press the advanced button in the datasource the two checkboxes are not active i dont know why and i clicked on the column that has the primary key but i still have the problem i cant update or delete records and i tried on the original aspnetdb users table(without my columns or anything) also found that two check boxes not active? is it a problem in the aspnetdb or what? there also something strange when i choose the tables in the sqldatasource the aspnetdb tables names are appear like this : vw_aspnet_roles vw_aspnet_users ...etc they all has that vw word dont know why it also exist in the original aspnetdb, so hope anyone can help cos if it's no solution for that problem i will have to build my db again from the begining ,hope u reply soon.thanks
I am trying to import application tables from oracle but they do not show up in the list of tables. I suspect that this is because the tables are owned by System. Is there any way other than changing the owner to get these tables to appear in the import list?
I'm making an application in Delphi that use an SQL database, i want the user can import a table in DBF format (that its located in another computer) to the SQL Server (in the database that i want) by one click of a button.
I try with the import wizard but its not what i want because it requiere to do the process manually. Its there anyway of achive this (Stored Procedure or something?)
Ernesto Cisneros Sanchez Senior Software Engineer Credit and Trade Bank Havana, Cuba
I am having a problem to get all datas into one table I must get datas from 2 other tables datas from the first table are columns but datas of the second table are rows
I cannot change the structure of TableA because an other application is using it as it is
TableA num | value | key 1 | a | 1400 2 | b | 1401 3 | c | 1402 4 | d | 1403 ...|... | 1403
TableB key | name | descr | value 400 | john | ok | 451
TableC (the table where I want to insert datas from Table1 and Table2)
val1 | val2 | val3 | val4 |name | descr | value 1 | 2 | 3 |4 | john | ok | 451
TableA has thousands of rows and different numbers I must get 8 different datas from TableA
so I get an horrible Query !
SELECT
TableB.name, TableB.descr, TableB.value, TableA_1.value AS val1, TableA_2.value AS val2, TableA_3.value AS val3, TableA_4.value AS val4, TableA_5.value AS val5, TableA_6.value AS val6, TableA_7.value AS val7, TableA_8.value AS val8
FROM dbo.TableB INNER JOIN dbo.TableA_1 ON TableB.key = TableA_1.key INNER JOIN dbo.TableA_2 ON TableB.key = TableA_2.key INNER JOIN dbo.TableA_3 ON TableB.key = TableA_3.key INNER JOIN dbo.TableA_4 ON TableB.key = TableA_4.key INNER JOIN dbo.TableA_5 ON TableB.key = TableA_5.key INNER JOIN dbo.TableA_6 ON TableB.key = TableA_6.key INNER JOIN dbo.TableA_7 ON TableB.key = TableA_7.key INNER JOIN dbo.TableA_8 ON TableB.key = TableA_8.key
WHERE TableA_1.num = 145 AND TableA_2.num = 80 AND TableA_3.num = 3160 AND TableA_4.num = 41 AND TableA_5.num = 50 AND TableA_6.num = 51 AND TableA_7.num = 53 AND TableA_8.num = 56
i'm importing tables from another sql server - now the tables are coming is as esther.tablename (my username on the other server) instead of dbo.tablename
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
hi. this is a beginner question as i am new to databases :)so, I have an access database and I want to take 2 tables from it and copy it to sql server in my ASP.NET application (.MDF file)I have visual studio 2005 installed on my computer and got no sql server 2005 installed on it. what is the way copy 2 tables to the ASP.NET database application?thanks alot guys.
I'm fairly new to MS SQL & I have a problem copying tables from one d/b to another. When I do an import or export select Copy tables, the tables & the data get transferred, but all the Identities are set to NO. My D/Bs have 188 tables & it's a pain to reset all of them manually.
Is there a setting I don't know about that will transfer the tables & maintain the Identity? (I'm using SQL Server Management Studio 9.0)
Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver
I have an origin data folder with many foxpro dbf files
When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine. The form shows me a list of the files in the folder - but I cannot scroll through the list.
When I select the folder and continue, I test the connection and get a message that it is fine.
Then I go on to actually import tables. The list that is presented of tables that I can import does not include all the dbf files in the folder. There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.
Does anyone know what could be causing this strange behavior?
I'm trying import 7 tables from each of 30 SQL2005 databases into a SQL2005 Consolidation database. I can simply create data flow tasks for each one but instead I would like loop through a list instead.
I've created a table to house the names of the databases from which I want to import the data. I've created SQL task to return the database names from the table as a "Full Result Set". I've assigned the result set to a user variable (type = Object) an named the result name 0
What I'd like to do is create a data flow task which connects to each of the databases and imports 7 specified tables from each database appending the table name with my database name in the result set.
I'm stuck on how I'd set the connection strings in my OLE DB Source in my Data Flow task. Any insight would be greatly appreciated.
If I have an XML without an XSD what is the best way to create and import data in SQL Server? I know I can use xsd.exe to create an XSD from my XML.
But if I want my structure to be somewhat different in SQL server how would I go about creating a reliable and repeatable import system for my data so i can easily manage the data updates?
I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:
1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".
2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.
A further note: I created all tables in the SQL2005 database before running the Import.
I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?
We're trying to figure out whether it's possible to link AD objects to SQL tables. For example, if we have a table of Users and a table of Companies, we'd like to enter the users into Active Directory, create the Companies as either OU's or Security Groups, then have those entities magically appear in the corresponding SQL Server tables. Is that possible? Thanks in advance.
I 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.
I 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.Â
I 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.
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
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 .
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?
