I have a MS SQL table that I want to import into a test db in my SQL 2000. The table is filename.sql and has the script in it to set up the table, etc.
How do I import this table into the sql 2k db?
I tried the wizard and I cannot get a choice to locate the file. How does one go about importing a copy of a sql table? The help file wasn't much of a help, thanks for your input.
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
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.
I have several pdf files that need to be imported into the database. Each pdf file is to link to several rows in a database.
The files have different file names and keep coming every week. I need to import them into a table and link it with the data table. Need help on how to get the filename out of the pdf file and save it on the table with the image. I can use that filename as the foriegn key..Any help would be really great..
-Sri
Hi,
I want to import all data ie tables, stored procedure, view etc from one database to another database in SQL Server 2005.
Example i have a database named Test which has 3 tables, few stored Procedure and many more things and i want to take all this data with data in the tables(all rows and columns) to a database named Huzefa in the same Server. Then is there any command for importing all data from from one table to another table....Please let me know...
Hi,
Can anybody help me? I want to create a new table and i want to import the structure of another table to that new table but not the data.I want to do it by executing a single sql statement.Would it be possible?
Thanks!!
Joydeep
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 RelatedHi, I’m trying to write a script on how to automate few tasks using the SQL Server Agent. Here is the step I need to do for the script, the step is similar importing a table:
1. Choose a Data Source - Import a table from a data source that is a text type, e.g. ImportTable_1.all from the location d:able
2. Select File Format - The File Format is a delimited, File Type is ANSI, the Row Delimiter is LF (Line Feed) and the Text Qualifier is Double Quote {“}
3. Specify Column Delimited – Comma
4. Choose a Destination - The Destination is Microsoft OLE DB Provider for SQL Server, using Window NT Authentication from the Database ‘ImportTBL’
5. Select Source Tables – The Destination Table name is SessTbl; in Transform, change the field name (e.g. TableID) and the data type (e.g. Int)
6. Save, Schedule and Replicate Package – Run immediately
7. Repeat step 1 – 6 again, but this time the table (e.g. ImportTable_2.all) must be appended to the first table. This is done on the daily basis.
Thanks in Advance
Regards
Andy
i used to be able to import Excel spreadsheets and convert them into SQL tables using MS SQL Server Enterprise Manager
however, i haven't been able to find out how to do this in Visual Studio 2005 - does anyone know how to do this (or whether it can be done at all) ?
Hi all,
I need advice on how to get data from xml file into the preexisting table. Right now I have a process that scrapes data from the website and generates xml file everyday. I need to get this data into sql table every night and I would like to make this automated process. What do I need to use in sql to accomplish this? It would be great if you can point me to a tutorial or give me few pointers.
Thanks!
Hi.
I have done this successfully the last time but just couldn't get it to work this time round. Please help.
I was trying to import data from an Excel file into MS SQL table. NONE of the numeric or value fields (right justified) works because they shown <NULL> in MS SQL table after the import via DTS. Those string fields were able to import ok into the table. DTS didn't show any error message during the DTS run.
Any advise? Thank you.
Best regards
Teck Boon
I have two tables I need to create in msSQL and I have the .sql files with the information in them, is it possible to insert them through enterprise manager?
Or another way? Or are they meant for mySQL?
cheers
Using 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 RelatedI have an access database that i upsized a few weeks ago and i need to add another table form that database, however when i use the DTS import wizard to get the particular table i wish i get as far as picking the table but i cant preview or do the import, i get the following error message on it!
Error Source: Microsoft Jet Database, Error Description: record(s) cannot be read; No permission on the table 'InternetBilling', Context: error calling openrowset on the provider.
Ive working on a copy of the access database and given all user groups read persmission and im still getting this, how do i get around it?
With much thanks for any help,
Amit
I know that the DTS Wizard is supposed to be able to handle heterognous
data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT?
HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Thanks
Mark
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
Hi to all
I am working on import module.
is there any direct query to import a text(CSV) file in to a database table?
any one can help me in this matter?
why is this the case when importing to sql server 2005?
View 3 Replies View RelatedI'm using DTSWizard to import a table from my main database to Temp.
This is the SQL statement...
CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)
What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.
Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?
This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.
Thanks,
Bill
Hi,I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...Thanks for your helpJohann
View 3 Replies View RelatedI hope this is the right forum for my question.
I'm developing a website for a Prepaid Calling Cards distributor. Each of the cards they sale have a list of the countries the card is good for. I need to import this data into my countries_rates table. The file they are giving me is an excel file that contain 3 colums (fields)
1- Country-Name
2- Rate
3- Card_$_Price
these files contain aproximaly 400 rows so it will be a hasle to have to insert it manually every week.
In my web application I need to create a form where the user will select the card from a dropdownlist and then find the excel file to be imported for that card.
I would like to know how do I do that with Visual Studio 2005, SLQ 2005 and C#
please direct me to some links where I can learn how to do this or please send me some code snips I can see how is done.
Tia
Charles
Whenever I import (or even append from another table) data to a table that has an Identity column, I get an error: Cannot insert NULL values in Identity column.
Isn't the Idenity column supposed to incement automatically without me having to provide a value? Using INSERT provides an Identity value automatically, using import however doesn't.
How can I overcome this problem? (I now delete and recreate the Identity column - really BAD practice!)
Thank you.
Hello
I am writing program in VC++ through SQl-DMO calls.My problem is when i when i tranfer(import) a text file(comma seperated) into SQl server through a SQl-DMO method called ImportData which is a method of Bulk copy object.Its is not able to convert the data field in the text file to corresponding value datetime in SQl server whereas other data types are working perfectly.
This is the record i need to convert:
90,MichaelB,Wintriss,Inspection,Paper,11,Job101,1, {ts '2000-12-10 15:54:56.000'},D:public233 and 247233.mcs,
and this is the date field
{ts '2000-12-10 15:54:56.000'}
Whereas if i export a table in SQl server in Binary mode and then import the file back it works but when do it as text it gives the above error
Pls help me in this i would be very thankful to you.
Note: I am using SQL Server 7.0 version
Regards
Jitender Singh
I have a database dump using mysqldump. Now I want to import a table from the dump file. How would one do this?
I've used mysqlimport < table.sql, but what if I have databasedump.sql which includes all the tables, but I only want to import ONE table?
-D
I am trying to import a sql table with Query Analyzer. I opened up the Query Analyzer, found the sql table I needed, opened it and it read everything in the table. I see the execution script that defines the headers and the data type for all the columns and the information populated below that. So I told it to "Run".
It says it is executing the query batch, I see the globe spinning like it is doing something, it says it is on line 10, row 43 (whatever that means), so it appears to be still working.
I stopped one of the Query Analyzers and noticed this message: "The name 'T' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"
One of the many lines that it began having issues with this is:
insert into TABLENAME values(1,'AAVS','ANALYZER','S310','','357C','855','776493','DIG VID ANLZR CORE MOD','P','VOTE5WBKAA',207610,'207610--1','CO','','1','','1','S310','ADVANCED AUDIO VISUAL SYSTEMS',T);
It appears that it does not much care for the 'T' at the end of each row. Is this required? Is there a way to take it out of the entire table before running it again?
Is there a way to choose what information I want to populate?
Currently the header script shows this:
CREATE TABLE TABLENAME (
BCKEY Numeric,
MFG Character(4),
DWG Character(11),
LIST Character(25),
SERIES Character(6),
FRC Character(5),
ECN Character(3),
CPR Character(6),
DSC Character(22),
EQTYPE Character(2),
CLEI Character(10),
ECI Numeric,
BARCODE Character(9),
OS Character(2),
OFC Character(2),
UC Character(2),
FILLER Character(8),
INDCODE Character(1),
PN Character(25),
EPN Character(39),
DWGNN Logical);
insert into TABLENAME values(1,'AAVS','ANALYZER','S310','','357C','855','776493','DIG VID ANLZR CORE MOD','P','VOTE5WBKAA',207610,'207610--1','CO','','1','','1','S310','ADVANCED AUDIO VISUAL SYSTEMS',T);
What if I only want some of these fields? Is there a way to tell it that I only want: BCKEY, MFG, LIST, SERIES, DSC, EQTYPE, CLEI, PN & EPN? The rest I do not want, so can I take the rest out somehow?
Thanks,
Hello, everyone:
I know it is a simple question. I am writing a query to import a table (MyTable) from an Access Database (MyDatabase.mdb). MyDatabase.mdb is in C: in local. This is the query from BOL.
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:MyDatabase.mdb';'admin';'mypwd', MyTable) AS a
By BOL, admin and mypwd should be user_ID and password. But I am doing testing in local, and don't set user_ID and password. How to handle two things.
Thanks
YTZ
Hi all,
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success.
Anyone have some info on this?
Thanks.
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
Hi There,I am looking for information on how to import the txt or csv file tothe multiple table in sql 2000. If you have any kind of inf. pleaselet me know wheather we can do this and how.below is the detail information.I received txt file every day which contain the information from 3different related table in my sql 2000 database. Right now we arekeyin the information from the web site (which is link to the txtfile) to our database, but i am wondering if we can import thoserecord in the tables.the header of the file goes to table1 and when we insert the record intable1, it should generate the autoidentityrecord (PK), and that PK islink to other table2 and table3 where rest of the information from txtfile goes. For table2 and table3 there are multiple record per txtfiles.in our txt file each row is separated with row header, like HTC100WITH ROW NO. 1,2,3.., which indecate this information goes to tableand 1,2....are the different row number.Please let me know whether we can achive this task or not.Thanks for all you help in advance.Indra.I have pasted my txt file below:========"FHS000",20041029,0900,,"10",1,"TRAILB10_20041029_1B",3,"2.20","Pason-DataHub",,"#Well 1098831406 Tour 2004/10/29 Trailblazer 10 148",1,"EDR_3-0-10_HF2ETS 2.2""CON000",1,0000,0759"CON000",2,0800,1559"CON000",3,1600,2359"HWI010","0312857","COMPTON BRANT 15-7-18-24","COMPTON PETROLEUMCORP.","TRAILBLAZER DRILLINGCORP.","15-07-018-24W4","100/15-07-018-24W4/00","HANKPARANYCH","CURTIS FIESEL",20041029,,,"10",20041027,0600,,,"148","DD04485","VERT.","NO",,"HCO030",1,"Daily Walk Around Inspection","HP","CF""HCO030",2,"Detailed Inspection - Weekly (using checklist)","HP","CF""HCO030",3,"H2S Signs Posted (if required)",,"HCO030",4,"Well License & Stick Diagram Posted","HP","CF""HCO030",5,"Flare Lines Staked","HP","CF""HCO030",6,"BOP Drills Performed","HP","CF""HCO030",7,"Visually Inspect BOP's - Flarelines and DegasserLines","HP","CF""HDC040",1,"Rig Site Health and Safety Meeting (one/crew/month)","CF""HDC040",2,"C.A.O.D.C. Rig Safety Inspection Checklist(one/rig/month)","CF""HDC040",3,"Mast Inspection Before Raising or Lowering","CF""HDC040",4,"Crown Saver Checked","CF""HDC040",5,"Motor Kills Checked","CF""HFU050",2300,2100,,"HWE060",-5,"Deg C","COOL","WEST","SLIPPERY",,"HCS070",1,177.8,,"mm",25.3,"STELCO","J-55",8,108.44,3.84,108.44,"HCS070",2,114.3,,"mm",14.14,"STELCO","J-55",72,979.50,3.84,979.0,"HDP080",1,127,79.4,"kg/m","E",57,127,"mm","3 1/2 IF",10,"DC","HDP080",2,89,19.7,"kg/m","E",68,120,"mm","3 1/2 IF",15,"DP","HPU090",1,"F-800","EMSCO",254,"mm",,,,"HPU090",2,"F-800","EMSCO",254,"mm",,,,"HTC100",1,"Rig up and tear down""HTC100",2,"Drill Actual""HTC100",3,"Reaming""HTC100",4,"Coring""HTC100",5,"Condition Mud & Circulate""HTC100",6,"Trips""HTC100",7,"Rig Service""HTC100",8,"Repair Rig""HTC100",9,"Cut off drilling line""HTC100",10,"Deviation Survey""HTC100",11,"Wire Line Logs""HTC100",12,"Run Case & Cement""HTC100",13,"Wait on Cement""HTC100",14,"Nipple up B.O.P.""HTC100",15,"Test B.O.P.""HTC100",16,"Drill Stem Test""HTC100",17,"Plug Back""HTC100",18,"Squeeze Cement""HTC100",19,"Fishing""HTC100",20,"Directional Work""HTC100",21,"Safety Meeting""HTC100",24,"WOD""HSS110",1,1,"SWACO","N","110",,"84",,"HPA130","COMPTON BRANT 15-7-18-24",20041029,"COMPTON PETROLEUMCORP.","TRAILBLAZER DRILLING CORP.","CURTISFIESEL","10","ALBERTA","N",253"TCP130",1,,,,"kPa",140,,,,"mm",,"TCP130",2,,,,"kPa",140,,,,"mm",,"TCP130",3,,,,"kPa",140,,,,"mm",,"TTL160",1,1,0.00,0.25,0.25,21,"SAFETY MEETING WITH TONG HAND""TTL160",1,2,0.25,1.75,1.50,12,"RIG TO AND RUN CASING""TTL160",1,3,1.75,2.00,0.25,7,"RIG SERVICE""TTL160",1,4,2.00,2.50,0.50,5,"CONDITION MUD & CIRC.""TTL160",1,5,2.50,2.75,0.25,21,"SAFETY MEETING WITH CEMENTERS""TTL160",1,6,2.75,3.50,0.75,12,"RIG TO AND CEMENT CASING""TTL160",1,7,3.50,6.00,2.50,1,"SET SLIPS, TEAR OUT RIG, CLEAN TANKS""TTL160",1,8,6.00,8.00,2.00,24,"WAIT ON DAYLIGHT/TRUCKS""TTL160",1,9,,,,,"CEMENT WITH BJ USING 13 TONNES OF BVF-1500 NP + .7%FL-5,GIVING 15.5 m3 OF GOOD""TTL160",1,10,,,,,"SLURRY @ 1718 kg/m3,PLUG BUMPED & HELD @ 03:30 HRSOCT 29/04.""TTL160",1,11,,,,,"RIG RELEASED @ 08:00 HRS OCT 29/04""TTL160",1,12,,,,,"MOVE TO 12-3-18-25W4""TDI170",1,"JEFF CASE",8,10,475,"Deg C",,,"RUNNING CASING",,,,,"TLN175",1,"VISUALLY INSPECT PINS, RAMS AND STOOLS PRIOR TO LAYINGOVER DERRICK""TPA180",1,1,"DRILLER",647172865,"JEFF CASE",8,,,"JC""TPA180",1,2,"DERRICK HAND",648519056,"BRYAN VANHAM",8,,,"BV""TPA180",1,3,"MOTOR HAND",651056533,"NEIL WILLIAMS",8,,,"NW""TPA180",1,4,"FLOOR HAND",640352662,"TARAS WOITAS",8,,,"TW""TPI190",1,"REG",25,,,,,,"TPI190",2,"REG",25,,,,,,"TPI190",3,"REG",25,,,,,,=====
View 4 Replies View Related
How do I do this?
I cannot find any facility like there is a Access for getting external data.
I have installed SQL Server Managemert Server Express .... I am wondering how to import data from an excel spreadsheet?
.. Silent Running
I've tried to import a text file into a table using the Import/export wizard.
My problem starts with some columns that should have NULL values (i.e. zero-length string) but the wizard doesn't recognize that it's NULL.
How do I solve the problem?
Thanks,
Mich