Ok, here's the deal. I am 1 of 2 developers at my organization, the other is a COBOL programmer. Long story short, every night he sends me a 22MB XML file containing our company's inventory. This contains about 40,000 items. In C#, I'm basically looping through all the XML nodes and on each node I load up an object with the particular properties (manufacturer, SKU, serial number, etc.) and call a Stored Procedure to to save the item.
In saving the item, I'm checking to see if it was already in my inventory and I either perform an update or an insert on the table as approriate. The query does a couple of other things like retreiving the Purchase Order Number, Cubic Feet, etc. from other tables. All in all, the query takes about 1-2 seconds to complete. I've optimized the query as much as I know how (including the addition of indexes), but this still takes at least 6 hours to complete the 40,000 items. Is this the best approach? Any other suggestions?
So far the only things I've thought about changing is: 1. Break it out into multiple queries to avoid poor compilation of execution plans
2. Multithread the C# app so that I'm running like 10 queries at once.
3. Get the COBOL programmer to give me a delimited file and do a BULK INSERT on it into an empty HEAP, then run a single INSERT and a single UPDATE (using CASE statements)
Has anyone any idea what could be causing this problem.I've a large txt file, with each item on each row seperated withquaotaion marks and seperated from each other by a comma.I can import it no problem to Access, but when I try to import it toSQL server, SQL Server gives me that "cannot find.... within first 8KB"of data error.Any ideas?Ciarán
Hi i'v e installed the file: SQLServer2005_DTS.msiBecause i've heard that i need that to import a csv file into a ms sql database. now i have no idea how to work it, like to even make it open.Where would i open it from? I reckon i'v eread enough literature about using it, to be able to have a bash... thanks in advance...
Hi All,Here is my code Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader") con = New SqlConnection(strCon) con.Open() Dim strselect As String = "" Try strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)" strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')" cmd = New SqlCommand(strselect, con) cmd.ExecuteNonQuery() con.Close()i have defined connection string in web.config file and my csv file is inside C:UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."Regards
I am having importing problem, when i tried to import tables from sybase to Sql server 7.0, it is not importing the keys, it is creating only datas without keys. Do u know any way in solving this issue?? My next issues will be on Serial number generation. In sybase we have Number (*), if we do
select number(*) from a it will show the sequence numbers, but in Sql server what is the equivalent function to show the sequence number when i am using a select statement.
I have an excel file with 5 columns. 1st column values are -
^^234 ^^456 678 123 456
SQL 2000 DTS, doing straight copy into an Access table takes only first two rows from 1st column ( see top 2 rows with trailing white spaces). How can I import all of rows ?
I have a project at work that requires me to transfer all the data, including the queries, forms, macros, and modules from Access 97 to SQL Server 7. I was able to import the tables only using DTS wizard. Is there a way to copy the queries, forms, macros, and modules?
Hi, i've struggled in the past to get ms sql 7 db's from one server to another server and always end up getting frustrated. I usually just export and then import..... more work but it does the trick.
Now i've been sent one single db data file that i'm told is a backup copy of someones database. How do i get this loaded onto my server. I've tried restoring from this file but it complains that the backup file is not for the database i'm restoring to.... which is correct.... this has to be something that many people deal with? isn't it?
Can someone please help me out.... some pointers would be much appreciated.
Hi, this is my first time and was wondering what's the best way to do the following: i've sql server 6.5. also have a sql database(.DATfile) in a folder. i need to import this database to my server. should i just LOAD/import it? or do i need to first create a database device on my server(how much size should i allocate etc) and then load it??
I use ms sql 2000 and i wanted to import a new backup database into a new ms sql 2000 on a different machine but it does not work so i wanted to know if it can be done or i have to do something else thank u.
Hi guys, I have to write a Store procedure which will pickup a txt file from a destination, read it and update some table. How to pick and read a txt file in a Stored procedure. Thanks in advance Vineet
I need to setup a dts package to import all tables from an Access 97 database into a SQL 2000 database. Do I need to specify each table in the package or is there a better way to grab all the Access tables?
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.
Is there any way at all to import from Excel to MS SQL without using the DTS package and without doing it manually? I really do not like the lack of flexability that DTS has and I need something else that will always work. Basically I am importing shipping documents that are in excel and putting them into a table. This has 2 issues, 1 I don't know the file name, just the folder it is in and 2 there is a large amount of header data in the file, some of which I need, some of which I don't. Is there any solutions out there? Thanks!
I have an identity question. I have an ID field with identity set to 'yes (Not for Replication)' with the increment of '1'. I have a range of IDs that skipped at one point so I have a range from 1-500, then the rest starting at 6000. I want to fill in some of the rest when I import new data.
Let's say I have 300 records. I want to start the ID at 501 and go to 801. How do I do that when I'm importing.
I need to copy the data in two spreadsheets into two tables in "SQL Server 2000". Below are the details:
One spreadsheet contains the data that needs to be added to an existing table in SQL server. All field names in the spreadsheet match the DB table column names. Another spreadsheet contains the data that needs to be copied to a new DB table. The table currently does NOT exist in the DB.
I'm not sure how to accomplish this. ANy help would be appreciated.
Is there a way to create a general dts to import tables from access mdb file? so that i will not have to change the dts for every table i am adding to the access file? something like foreachtable? or a way to read the tables list from the access file in the sql connection?
My client drops for me many files like this on a shared drive M: daily 1_Test.csv 2_Test.csv 3_Test.csv
I would like you to advice on how to write a SQL code (that can include DTS if possible) that will take this files, merge them into one (since they have same columns) and send them to another client as one file. But it must only take the files of the same date and must not resend files i have send already. I need this to be an automated process since the files are dumped into M:drive midnite and I need this code so that I can schedule it as a job and run around 4h00am.
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?
I have a problem with SQL Server 2000. I have a rather large database (3 gigs). I have the recovery mode set to bulk-logged because I'm importing 300,000+ records (through Acccess (linked tables) due to several importing checks which need to be done which SQL Server DTS can't handle.) The problem is that my Transaction log keeps growing very large after each import. I've tried several different settings in SQL Server but I don't want to slow down the import process which runs through a rather intensive checking process. The problem is that the Transaction log keeps growing and will only shrink after nightly backups.
I've tried shrinking the database after 1 of the imports (which causes the Transaction log to grow to 400+ megs) and can't get it to shrink no matter how I do it (even tried using Backup Log Rebates WITH Truncate_Only as well as the All Tasks - Shrink Database.)
I also tried setting the Transaction log to 400 megs and not allowing it to grow.
What's the best way to do this with the settings in SQL Server? I still need to use Access to do the checks (a stored procedure/DTS won't work with all the checking needing to be done on each import.)
I'm using DTS to import a flat ASCII file. I checked the primary key(telephone number) in the flat file to make sure it's unique. But for some reason when I import is using the DTS package, I'm missing 4 records. I checked one of the missing records and I don't see any problems with it. Is there a way I can debug the DTS package so it spits out a message/reason why it's not importing the record?
How can I set up a DTS that reads a flat file database and inserts only the new records to an existing table? This DTS will be ran everyday against a flat file that accumulates records.
I am attempting to import a database from one server (2000) into another one (2005). I have tried a variety of methods to do so, but each seems to have at least one hiccup. The biggest frustration is that, depending upon how I setup the tables prior to import, one of two things ends up kicking me in the rear:
1) I leave the IDENTITY cols set as such, and even though I enable identity insert in the wizard (SQL Server 2005 Management Studio), they end up just incrementing starting at 1 as though it was fresh data. Obviously, once I apply the PKs and FKs, everything falls to pieces.
2) I initialize the identity cols as INT, and the data imports just fine. However, when I try to add the IDENTITY to the cols, it tells me to go screw myself. Actually, it just throws a syntax error. Here is the statement that I'm attempting:
ALTER TABLE myTable ALTER COLUMN myCol IDENTITY GO
I have also tried
ALTER TABLE myTable ALTER COLUMN myCol INT IDENTITY GO
I'm pulling my hair out with this one. It's played a rather significant role in me getting only 2 hours of sleep in the last 40+ hours. I really try not to post questions without exhausting myself trying to figure out the answer, or to find it with research (which is part of figuring it out). I am a moderator on EasyCFM.com's ColdFusion forums (mquack is the screenname), so I know how annoying it is when people don't put forth any effort on their own. Hopefully I don't come across here as being "one of them".
Any help or guidance to the *right* sources would be very much appreciated.
