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....
Any help gratefully accepted.
Thanks,
Paula.
Importing CVS to SQLServer 2005 in a web application:
I need to import CSV file to a table. This is a .Net 2.0 web application running in the WebServer which has .Net 2.0 installed. SQL Server 2005 is a database in another machine. What are my options? Would SSIS work in WebServer if no database installed in that machine.
Thanks
chapter1
Delegates officially are to nominate Cheney as the GOP's vice presidential candidate before he addresses the group Wednesday night.
chapter2
"I think that the vice president's speech tonight is going to be about big issues, the big issues of this campaign -- the war on global terror, the president's education policy, the fact that the economy is turning up again," she told CNN's "American Morning."
chapter3
She said she had known her husband since he was 14 and planned to share anecdotes that many people have not heard before in her introduction.
Chapter4
Maverick Democratic Sen. Zell Miller of Georgia is scheduled to deliver Wednesday's keynote address -- a role he also played at the 1992 Democratic National Convention, which nominated President Clinton.
Chapter5
In the earlier speech, Miller, then governor of Georgia, said that "for 12 dark years, the Republicans have dealt in cynicism and skepticism. They have mastered the art of division and diversion, and they have robbed us of our hope."
I wanted to import the above word doc into my sql server DB.
I have two columns in the table
1.Chaptereid
2.chapter_notes
chater1,chater2,chapte2,chapter3,chapter4 and chapter5 should go into chaptered column and the text followed by the chapter id should be imported into chapter_notes column.
So how can I import this document.
Thanks.
I would like to import data to a sqlserver express database. The database I am interested in importing was created in MSDE. I tried backing it up and using the restore function in sqlserver express but with no success. I have not been able to find an import function in the Management Studio Express interface. Is it possible to import this information, or should I get busy with my data entry?
View 4 Replies View Related
I am currently Using SQL Server Express for testing purposes while waiting for SQL 2008 to be issued. (Our project will take some 2-3 years to complete so concerns about using a 'new' version of SQL Server are not pertinent). We have ordered this week Visual Studio 2008 as the shrink wrap version is due out the end of this week.
I have been able to migrate all our data from the old database (Superbase) but only field names and field types.
Indexes and validation stuff is lost.
The problem I have is that:
I can only import to an empty database, therefore if I start to create Indexes and otherwise modify the structure of this database in SQL Server then I will lose all this work on the next occasion I need to import (as Superbase will still continue to be our firm's current daily data handling tool for another year or two)
My thoughts where to create a copy of the original import, develop the constraints, indexes etc. on this secondary copy. then (on the day we are ready to migrate) empty the copy of data and import the data only
Does SQL Server Express have the tools to do this?
If not then are my best options to use SQL Server Beta until the new software is realeased (but notice that Visual Studio 2008 does not like SQL2008 - is there a fix for this?)
I hope this is the right forum for these questions
Thanks in advance
Hi,
In my project i have to import csv file into DB, i have written a C# windows application as below...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;namespace FedExDataAnalysis
{
public partial class Form1 : Form
{public Form1()
{
InitializeComponent();
}protected string[] arrayValues;
//string row = "";
// int cnt = 0;private void Form1_Load(object sender, EventArgs e)
{string path = "c:\2008-01-09_01-00_FedExInv_232316837.csv";
importCSV(path);string path1 = "c:\2008-01-10_05-18_FedExInv_232316837.csv";
importCSV(path1);
}public void importCSV(string path)
{string row = "";int cnt = 0;
using (StreamReader sr = new StreamReader(path))
{while (sr.Peek() >= 0)
{
cnt = cnt + 1;
row = sr.ReadLine();arrayValues = row.Split(',');if (cnt > 2)
{if (arrayValues.Length > 0)
{
insertrecord();
}
}
}
}
}public void insertrecord()
{string str;
int Recordaffected;string inserted = "no";
string constr;SqlConnection conn;
SqlCommand cmd;
constr = "Data Source=I07;Initial Catalog=JuiceApp;User ID=sa;Password=dj$ql1736ju1c30ca1a";conn = new SqlConnection(constr);
conn.Open();
try
{str = "insert into xt_importcsv (Bill_to_accountno,Invoice_date,invoice_number,Original_ammountDue,Current_Balance,express_or_ground_trackingid,";
str = str + "transportation_charge_ammount,net_charge_ammount,shipment_date,actual_weight_amount,rated_weight_ammount,recipient_name,recipient_company,";str = str + "recipientaddress_line1,recipient_city,recipient_state,recipient_zipcode,recipient_country,shipper_name,trackingid_charge_description,trackingid_charge_ammount,";
str = str + "trackingid_charge_description1,trackingid_charge_amount1,trackingid_charge_description2,trackingid_charge_ammount2,trackingid_charge_description3,trackingid_charge_ammount3,";str = str + "trackingid_charge_description4,trackingid_charge_ammount4) values ('" + arrayValues[0].ToString().Replace(""", "") + "','" + arrayValues[1].ToString().Replace(""", "") + "','" + arrayValues[2].ToString().Replace(""", "") + "','" + arrayValues[4].ToString().Replace(""", "") + "','" + arrayValues[5].ToString().Replace(""", "") + "','" + arrayValues[8].ToString().Replace(""", "") + "','" + arrayValues[9].ToString().Replace(""", "") + "','" + arrayValues[10].ToString().Replace(""", "") + "','" + arrayValues[13].ToString().Replace(""", "") + "','" + arrayValues[18].ToString().Replace(""", "") + "','" + arrayValues[20].ToString().Replace(""", "") + "','" + arrayValues[25].ToString().Replace(""", "") + "','" + arrayValues[26].ToString().Replace(""", "") + "','" + arrayValues[27].ToString().Replace(""", "") + "','" + arrayValues[29].ToString().Replace(""", "") + "','" + arrayValues[30].ToString().Replace(""", "") + "','" + arrayValues[31].ToString().Replace(""", "") + "','" + arrayValues[32].ToString().Replace(""", "") + "','" + arrayValues[34].ToString().Replace(""", "") + "','" + arrayValues[80].ToString().Replace(""", "") + "','" + arrayValues[81].ToString().Replace(""", "") + "','" + arrayValues[82].ToString().Replace(""", "") + "','" + arrayValues[83].ToString().Replace(""", "") + "','" + arrayValues[84].ToString().Replace(""", "") + "','" + arrayValues[85].ToString().Replace(""", "") + "','" + arrayValues[86].ToString().Replace(""", "") + "','" + arrayValues[87].ToString().Replace(""", "") + "','" + arrayValues[88].ToString().Replace(""", "") + "','" + arrayValues[89].ToString().Replace(""", "") + "')";cmd = new SqlCommand(str, conn);
Recordaffected = cmd.ExecuteNonQuery();if (Recordaffected == 1)
{inserted = "yes";
}
}catch (System.Exception ex)
{Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
}
}
It is working fine.. It is importing data into DB. But the problem what m facing is , in the code above m using the split function and assigning the row of the CSV file into arrayValue. the prob is if the arrayValue has two entries lk: Name: "john,Mikel " it will split it separately and insert into the next field in the DB... M not getting how resolve this... Please help...
Thanks,
Chai.NV.
hi all,i have a table called Employee in sql server 2005 as shown beloweid ename esal001 john 5000002 lina 4000 I need to fill the above table from xml file(Emp.xml) as shown<Root> <Employee> <eid>003</eid> <ename>rose</ename> <esal>2000</esal></Employee><Employee> <eid>004</eid> <ename>sam</ename> <esal>6000</esal></Employee></Root> plz try to help me i am in need or give me any helpful suggestions thanks in advance
View 6 Replies View RelatedOK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.
View 2 Replies View RelatedHi;We just migrated to SqlServer 2005. When I import tables from fromSqlServer database into another the identity fields get switched offfrom being identities.How can I prevent that from happening?Thanks in advance for any informationSteve-------------------------------------------------------------------Microsoft SQL Server Management Studio9.00.1399.00Microsoft Analysis Services Client Tools2005.090.1399.00Microsoft Data Access Components (MDAC)2000.085.1117.00(xpsp_sp2_rtm.040803-2158)Microsoft MSXML2.6 3.0 4.0 6.0Microsoft Internet Explorer7.0.5730.11Microsoft .NET Framework2.0.50727.42Operating System5.1.2600
View 3 Replies View RelatedHello,
Am very conversant with MS SQL 2K, however i just downloaded 2005 express edition and will like to know how to
1. How to import tables using DTS graphic view to Management studio
2. How to get to Query Analyzer ?
thanks
Mary
Yes O !
Hello Everybody,
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
3) Any other options?
Srikanth
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.
View 2 Replies View Relatedhello, 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
View 10 Replies View RelatedI 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?
Thanks,
Ken Nicholson
Hi everyone,
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
how can i do it in the best way ?
Thank you
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
how can i change this?
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?
View 1 Replies View RelatedI'm trying to input a few thousand flat files into a few thousand tables in a sql database, using SQL Server Business Intelligence Development Studio.
im using a for each loop to read all the files in a directory
the problem is i can only insert the data from all the files into one table
does anyone know a way to do multiple tables? maybe using some sort of variable?
Hi,
I hope you can help me.
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.
Steve
When I try to make a connection to an Access .mdb I get the following error:
"Unable to open application. The workgroup information file is missing or opened exclusively by another user"
Yet, I am able to open the file through Access and have necessary permissions and I know no one else has it opened. The mdb is password protected and I have provided the correct login information in the DTS connection.
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.
View 2 Replies View RelatedHello,
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)
Thanks for your help - Breck Cogdill
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?
Thanks.
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
Is it possible to import an MS-Access database (or table) into a new SQL Server 2005 Express database? If so, how is it done?
View 1 Replies View RelatedI 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.
View 3 Replies View RelatedI'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.
Thanks in advance.
Bill Webster
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?
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!
rb
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.
Hello,
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?
Thanks a bunch
j
Hi,
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?
Thanks
Regards,
Arnaud Gervais.