On a nightly basis, this table gets rebuilt in a temporary database. Once the table has been built and scrubbed, i need to move it into our webservers db.
I'd like to do this with minimal interuption to the website.
Possible techniques:
1) I could set up a DTS package to copy the table object overwriting the destination table
2) I could export to a flat file and then bulk import into the live table (after truncating it)
3) I could run a process to update smaller chunks of data at a time running delete queries and insert queries.
Anybody have a thought on the best way to do this so that the web users would be virtually unaware that anything was happening ?
My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.
The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.
For example, the following code is taken from Access :
SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;
But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
hi all, is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
I want to move an existing table from another. Example B.mdf dbo.xx to C.mdf dbo.xx B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx C.mdf dbo.xx is empty - All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx I was telling I could do a cut and paste to copy the dbo.xx Thank you in advance. ~ Peaches ~
Here is the sql code I am using. However this code adds duplicate records. It adds the record and exactly one extra record... any help would be greatly appreciated...
It's wrapped in a little ASP code:
sql2 = "INSERT INTO prodcampsignup " &_ "(Camp_ID,UserName,Password,CampName,Host,Director ,Contact,Address, " &_ "City,State,Zip,Country,Phone,AlternatePhone,Fax,C ontactEmail,AdminEmail, " &_ "URL,CampProfile, InsertTime)" &_ "SELECT * FROM tempcampsignup WHERE Camp_ID = " & lastid
I will be moving production SQL databases (one is 25Gb, the other is 3Gb) to a new server. One of the reasons is the old server has only IDE disks while the new has raid (I don't know the configuration yet). Database 1 has a primary and one other filegroup. I want to spread the disk I/O by moving table A to it's own filegroup on it's own disk. For the purposes of testing I have 1. brought a database onto a test server (using sp_detach_db, copying the files and attaching using sp_attach_db) 2. created a new filegroup 3. created a physical file on disk, allocated space and associated with my new filegroup
my question is how do I now move table A into this file?
How to move tables to the new filegroup which i created. suppose my database has only primary FG and after that i added one FG and due to performance reasons i want to move the existing table to NEW FG. Thanks.
I have worked mostly in oracle, and new to sql server. I am trying to move a very small table from oracle database to sql server database. I see a different methods I can do this, I guess DTS etc. I tried import wizard in SSMS, I was not succesful in that. Can any expert suggest me any possible ways of doing this the best way? Thank you very much!
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
hi, i have 2 question here hope someone can help me.
1) INSERT INTO table2 (col1, col2, col3....) SELECT col1,col2, col3 FROM table1 WHERE someting.....
the select statement used the .ExecuteReader and the insert statement used .ExecuteNonQuery how to join this both used in question (1)? guide me step by step.
2) by refer to question (1), how to move 1000 or more rocord from table1 to table2 ?. how to i store the 1000 records from table1. is there any solution to this problem.
first, i want filter data from table A if IC_NO and passport_Number no is null
then after get the result, i want move the result into table B. my table as below:Table A NUMBER IC_NO PASSPORT_Number ======= ====== =========== 1. 123 A123 2. 3. 456 A456
Hi, I am relatively new to this stuff. I am using Microsoft SQL Server Management Studio Express (9.00.1399.00) Can someone tell me the way to get a table and its content to another database (I use two at webhosts4life) Or perhaps a way to export the data of a table so I can do it at a later stage.
Is that at all possible with this program or do I have to use the non-express version?
To reduce the load on my current server I wish to move a table which is being updated frequently by a static feed to an independant server.
Is there any way of moving a table with all of it's dependancies (Stored procedures and other tables) to another server without manually finding them all.
I'm very new to this as you can tell so if you require specifics let me know :)
Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this: INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED) SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED FROM dbo.TABLENAME WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888' GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
We need to move duplicate data from this sheet to other table also having issue that sometime verifiedmemberID is null as well as verifiermember name is null and also having the values in BCP authorisationcode as well as FPoveridecode but transactionmode/bcpmode is 'n' and also having condition that transactionmode/bcpmode is 'y' but bcpauthorisationcode is blank.
I have a production application that I am building some upgrades usinga second (empty) copy the database. A few of the upgrades includedchanging the datatypes of a few fields from varchar to int(all thevalues in this column are already numbers)I am not trying to move the data from the production database into theblank database using DTS and the fields that had their datatypeschanged are getting dropped by dts.Is there anyway to move this data easily through dts or through anothermethod and not have the fields with the datatype changes get dropped?I have about 60 tables so it is not as simple as just copying andpasting the data..
I have SSIS package where i will be refreshing 5 cubes. Here i am running my package every 15 minutes because i dont know when my ETL tables inserted once my ETL job is done. My question is that i need to move to another task once my first cube get refreshed. i.e when my first cube gets processed for first 15 minutes i will be updating some column as timestamp in ETL table. And then i will be processing remaining four cubes. For the second 15 minutes i should not process the same first cube rather i should process only the four cubes. Because these four cubes data i am getting from different region.
It can be done in SS 2000 under active x script task. But i need to do it in SSIS under Script Task where i will be checking in ETL table whether my TimeStamp column has value or not. Is it possible to do it? Moreover we have option like disconnected edit in DTS 2000. Is there any similar way to acheive?
I am developing an application that has a table with lots of records(network traffic) but the data is summarize every so often to create summary records (old records are deleted). The problem is that I have a PK based on an autoincrement ID (int) that will run out of numbers. However, this ID is not referenced anywhere, (not a foreign key from another table, not use for deletion and there is no update in this table whatsoever).
So my possibilites are: 1.- reseed the id when it is about to run out. 2.- make the id bigint 3.- remove the id and change the PK to 2 other fields 4.- remove the id and without PK
I am leaning toward option 4, because I do not see the need for a PK, but I understand that it is quite out of the normal.. So I would like to hear from other people ( I do not have much experience with DB).
I also like option 3. I already have a index on one of the other fields (time).
If I use BCP to export a very large table will that table be blockedfor writes during the export process? I don't want to prevent usersfrom accessing that table during the bcp process?Thank You, TFD.
I have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
Hey all, Another varbinary question. I am trying to move an image stored in a table varbinary(max) directly from one table to another programmatically. The rest of the data is just nvarchar(50) so I just use a T-SQL select statement in the code behind and feed all of the date into an SqlDataReader, I do this becuse there is some user interaction when the data is moved, so there may be some new values updated when transfering from one table to another, so once the old and possibly new data is stored in seperate variables then I use a T-SQL insert statement to move all of the data into the other table. Problem is I am not really sure how to handle the image data(varbinary(max)) to just do a straight up transfer from the one table to another. I get conversion errors when trying to handle the data as a string which makes sense. Not sure what to put for code examples since I really am stumped with this, but here is what is not working. Dim imageX As String SqlDataReader Code - imageX = reader("imageData") Insert code - myCommand.Parameters.AddWithValue("@imageData", imageX) Thanks in advance,
I have an application , user will read information in Excel file and insert that data into my application, I think it spend a lot of time. I want to make a tool which move data from Excel file to a table in My application (MS SQL) automaticly. How to do it, anybody has tool or know how to do, pls help me.thanks.
I would like to know that how can I move 70 plus tables that are on sql 7.0 to sql 2012 via SSIS.I know its a two step process but what is the best route and how I can process.
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID from grpcon Inner Join groups on grpcon.GroupNum=groups.GroupNum
