Software For Performing Mass Design Changes
Jun 28, 2001
Hi,
I was wondering if anyone knows of any way, including third party tools, to replicate a design change on a table across many different databases. I have written an ASP script that allows me to copy multiple tables to multiple databases in one go but I need something that will allow me to replicate the design of a table by comparing source and destination tables. So far I have scripted most of it but I have no idea on which system table to get the identity information and it seems there must be an easier way!
Any help would be appreciated,
Seoras
View 1 Replies
ADVERTISEMENT
Jun 17, 2015
I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
accnt acc1167
ven1 amnt1 ven job# amnt
1167 100 1167 1 200
1152 50 1167 2 300
1167 110 1167 3 100
1167 300 1167 4 200
1252 1050 1167 5 200
1167 210 1167 6 150
1167 1150
1167 130
2113 800
1167 550
1167 1200
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
acc1167
ven job# amnt
1167 1 61.4
1167 2 92.1
1167 3 30.7
1167 4 61.4
1167 5 61.4
1167 6 46.05
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
UPDATE acc1167
sum1 = sum amnt1 where ven1 = '1167'
from accnt
sum2 = sum amnt
from accnt
SET amnt = sum2/sum1*amnt
FROM acc1167
View 2 Replies
View Related
Apr 25, 2008
I have a Hits table that tracks the hits on the id of a Link table:
Hits:
int linkId (foreign Key to Link)
datetime dateCreated
varchar(50) ip
We recently had to merge Links from different systems that are implemented similarly. As a result, all the linkIds are now wrong in the Hits table because the ids all changed. I managed to track down all the old ids and their associated new ids and have it in a table that I call joined_links
joined_linksint oldId
int newId
So, how do I do a mass update of these linkIds in the Hits table in SQL? I know I could do it in .NET, but I'd rather not write an app to do that runs thousands of update statements. There's gotta be a way to do it something like this:
UPDATE Hits h SET h.linkId = (SELECT newId FROM joined_links WHERE oldId=h.linkId)
but obviously I don't have visibility of that linkId in the subselect... A Loop maybe?
View 6 Replies
View Related
Aug 13, 2004
I have a series of DTS packages.
Each package has 20 queries.
Each query has a server name.
Is there a way to change the servername without editing each query in each DTS package.
I'd like to copy the template DTS package, then perform the modification.
Thanks
View 2 Replies
View Related
May 8, 2008
This is a run of the mill application that moves orders from one table to another. There are 2 tables, Ordersummary & HstOrders.
Ordersummary has the following columns...
Identifier
FollowupId
OrderNumber
OrderReference
OrderReferenceOrigin
......
......
HstOrders has the following columns...
Identifier
OrderNumber
OrderReference
OrderType
......
......
The above two tables are bound by Identifier. After each month end, Orders are moved from Ordersummary to HstOrders.
Now my task is to update all rows in OrderSummary with the order details as seen in HSTOrders for ordertype = 'CREDIT'. OrderReferenceOrigin(in Ordersummary) should be updated with the value of Orderreference(from hstorders).
I have to update each row at a time & I need to write a cursor for mass updates where ordersummary.identifier = hstorders.identifier.
Can someone please help with in writing this update statement as I never wrote a cursor.
View 1 Replies
View Related
Oct 1, 2001
Hi friends,
Any idea about mass mailing system using SQL Server .Pls get back to me.
thanx and regards
Chinmay
View 2 Replies
View Related
Apr 17, 2006
SQL Server 2000 on Win2k
I'm fairly new to SQL Server and I'm just wondering if it's possible to Update Statistice for all indexes somehow? I'm looking at the Update Statistics command and it doesn't seem to be possible.
The situation we have is a reporting DB that basically has all it's tables truncated and remade every night by some DTS jobs that import from another datasource and change the data and build some denormalzed tables etc.
Some of the large Insert operations go from taking 8 mins to taking several hours sometimes and updating the stats seems to fix the problem for a while. So I'd like to make sure the optimizer has all the latest stats for all tables.
Any other advice would be appreciated.
Cheers.
View 2 Replies
View Related
Aug 8, 2006
Hi,
I have an user table with a single integer column. No indexes, no identities, nothing. I have to insert 600,000 rows via a client app. In tests, using BCP/Bulk Insert/DTS all runs OK (sub 3 seconds). However the app takes 5000 rows a second [considerably slower]. I can mimic this slow perfomance, within DTS, by removing the 'Fast Load' & 'Batch' options.
Question= why would the SQL insert run slower on one server and as fast as BCP/Bulk Insert/DTS on another? What can I check on the 'slow' running server? May there be a file version anomally ??
Version = SQL-2000 SP4
Any help much appreciated !!!
View 2 Replies
View Related
Oct 11, 2007
Does anyone know what the best way to do mass updates in SQL server is? I am currently using the methodology suggested in this article
http://www.tek-tips.com/faqs.cfm?fid=3141
But the article is assuming that once I update a field it is going to have a value that is NOT NULL. So I can loop through and update the rows that have a NOT NULL value. But my updated rows do contain NULL values, in this case what is the best way to go about this???
***************************************
Here is my code. I want to avoid using Upd_flag becos
after the following code runs I need to reset that flag
before I run my next query
***************************************
--Set rowcount to 50000 to limit number of inserts per batch
Set rowcount 50000
--Declare variable for row count
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
--Use tablockx and holdlock to obtain and hold
--an immediate exclusive table lock. This usually
--speeds the insert because only one lock is needed.
update t_PGBA_DTL With (tablockx, holdlock)
SET t_PGBA_DTL.procedur = A.[Proc code],
t_PGBA_DTL.Upd_flag = 1
FROM t_PGBA_DTL
INNER JOIN CPT_HCPCS_I9_PROC_CODES A
ON t_PGBA_DTL.PROC_CD
= A.[Proc code]
WHERE t_PGBA_DTL.Upd_flag = 0
--Get number of rows updated
--Process will continue until less than 50000
Select @rc=@@rowcount
--Commit the transaction
Commit
End
View 4 Replies
View Related
Oct 4, 2007
I have the following problem. I need to insert 100.000 records (50Kb each) in one operation from a VB program into a SQL Server 2005 database. All of these records will be ready for inserting at the same time.
How to make this insert as one big transaction instead of 100.000 small ones?
View 2 Replies
View Related
Nov 15, 2007
The company i work for changed names and all email addresses within the company have changed. While it was OK for a while they are no longer going to be forwarding email to the old addresses to the new ones. There are Tons of subscriptions and tons of email addresses that need to be changed to the new names.
If i could find the table with the TO: part of the subscription held in it i could just run an update on that field and it would be solved...however, i cannot find that field...
So,
Without going into every subscription in report manager, how can i change the email addresses? Any Suggestions?
Thanks in advance
View 1 Replies
View Related
May 16, 2008
Hi There
Most of the time my solutions consist of 1 or 2 packages and config files work well.
Now i have a solution with about 50 packages i have to move to QA.
However a config file has the package ID, so even thoug they use the same data source connection. I would have to create and change 50 config files.
Data sources are kept in the package xml , so if i copy all the packages to QA , and then change the Global Data Source connection, i still have to open each package maually and save it again.
Surely there must be an easy way to move all 50 packages and have the connection now point to QA. But config files and global data sources dont do the trick, what am i missing here ?
Thanx
View 5 Replies
View Related
Feb 25, 2004
I am making a prog that needs to import many records from a spreadsheet on a local computer through asp.net into sql server
is there a simple command to do this or is there information on how to do this
please give all the information that you can thank you
View 9 Replies
View Related
May 30, 2002
I want to backing up my hourly transaction log backups direct to a mass storage unit as opposed to the local server. However when trying to set this up it only gives me option of backing up to local drives, even though I have a drive mappping to the mass storage unit.. I'm there is a simple around this.Would appreciate any advice..many Thanks..
View 2 Replies
View Related
Mar 4, 2005
If I have a trigger on a field in a table, and I update one record trigger fire properly. If I do a update to that same field on all records in the table the trigger does not fire. I the error in the trigger, or do I need to change my update statement?
View 2 Replies
View Related
Sep 17, 2013
I have an existing SQL table that I want to import 300,000 rows into. I have copied the table headers into Excel and added all the rows but constantly getting multiple errors no matter what way I try to import it.
So far tried Import Method from SQL Management Studio, BULK INSERT and SQL Script. What is the easiest way?
If I try this
select *
into Coupon FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:coupon.xls;HDR=YES',
'SELECT * FROM [coupon$]')
I get
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
View 9 Replies
View Related
Dec 13, 2007
Hi,
I've been having problems with my tempdb filling up, and causing all databases on the server to stop functioning properly. I've been removing alot of data lately (millions of rows), and I think this is the reason why my tempdb log is going thru an unusual load.
Whats the best way to make sure the tempdb doesnt fill up causing me major problems? I had temporarily turned off backups while I was having a new HD put in. Am I right in thinking that when a DB is backed up, the tempdb log is reduced in size? Should maintaining a daily backup solution help keep things under control ?
Thanks very much for any tips!
mike123
View 4 Replies
View Related
Jun 29, 2007
Hi,I need to update a field in about 20 records on a table. The table hasan update trigger (which updates the [lastedited] field whenever arecord is updated). As a result I'm getting an error: "Subqueryreturned more than 1 value.", and the update fails.Is there a way in the stored procedure to handle this issue?thanks for your help.Paul
View 2 Replies
View Related
Apr 25, 2007
Hi,
I've deleted about 3-4 million rows from one of my tables as the data was old and no longer needed. The problem is that now queries are runnning extra slow. I am in the process of running taras isp_ALTER_INDEX however its taking quite a long time and seems to be slowing things down even further while its running as expected. (It's been running 4 hours already, I have stopped it and will rerun it a slower traffic period for the db server)
Just wondering if I have the right approach here or if anyone else has any suggestions.
Thanks for your help!
View 14 Replies
View Related
Jun 27, 2007
Hello,I need to alter fields in all my tables of a given database, and Iwould to do this via a t-sql script.Example, I want to change all fields called SESSION_ID to char(6). Thefield is usually varchar(10), but the data is always 6 characters inlength. I have serveral fields that are fixed length that I want tomove from varchar to char.I believe I can find all the tables where the field exists usingselect * from INFORMATION_SCHEMA.columns where column_name ='SESSION_Id'but I dont know how to take this into an ALTER TABLE , ALTER COLUMNthat can be automatedTIARob
View 2 Replies
View Related
Feb 26, 2008
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
Thanks, Bruce
View 8 Replies
View Related
Nov 15, 2006
Hola!I'm currently building a site that uses an external database to store all the product details, and an internal database that will act as a cache so that we don't have to keep hitting the external database to retrieve the products every time a customer requests a list.What I need to do is retrieve all these products from External and insert them into Internal if they don't exist - if they do already exist then I have to update Internal with new prices, number in stock etc.I was wondering if there was a way to insert / update these products en-mass without looping through and building a new insert / update query for every product - there could be thousands at a time!Does anyone have any ideas or could you point me in the right direction?I'm thinking that because I need to check if the products exist in a different data store than the original source, I don't have a choice but to loop through them all.Cheers,G.
View 2 Replies
View Related
Nov 30, 2000
Can anyone assist me in solving this problem:
DTSTransformCopy: ValidateSchema failed; see Extended Error Information.
TransformCopy 'DTSTransformation_10' validation error: Source column too narrow to contain a valid value of destination column's datatype for column pair 1 (source column 'Col010'(DBTYPE_STR), destination column 'CHECKDAT' (DBTYPE_DBTIMESTAMP)).
Any help would be greatly appreciated.
Thanks,
Rey
View 1 Replies
View Related
Nov 19, 2003
I need to perform some division on the results of two alias columns that perform counts in a query. What is the best way to do this?
View 7 Replies
View Related
Sep 3, 2002
How to delete a record from a parent table, all relating records in the child tables should also delete.
How to do it by MSSQL 7.0
ANB
View 1 Replies
View Related
Mar 8, 2005
I have two tables that I have to compare:
Table:PR
WBS1 WBS2 WBS3
123-456 1000 01
123-456 1000 02
123-456 2000 02
567-890 2000 01
567-890 2000 02
Table:PR_Template
WBS2 WBS3
1000 00
1000 01
1000 02
2000 00
2000 01
2000 02
After Insert I should have:
wbs1 wbs2 wbs3
123-456 1000 00
123-456 1000 01
123-456 1000 02
123-456 2000 00
123-456 2000 01
123-456 2000 02
567-890 1000 00
567-890 1000 01
567-890 1000 02
567-890 2000 00
567-890 2000 01
567-890 2000 02
Basically, I need to insert the wbs2 and wbs3 where it does not exist in each wbs1.
What I have now will find the values that need to be inserted for a particular project but I don't know how to go through each project and perform the insert:
Select * from PR_template Where Not Exists
(Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')
Order by wbs2, wbs3 asc
Thank You
View 11 Replies
View Related
Mar 26, 2008
Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.
ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am
The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!
View 3 Replies
View Related
Sep 4, 2013
I need to do SUM on a column values and then subtract it from the SUM of values on a column from another table. I am using SQL server Management Studio, but getting the errors on the following Query
SELECT note_id, cap_int_amt)- SUM (ttl_cap_int_amt) as SUM1,
sum(orig_fee_amt)-sum(ttl_qualfyng_fee_amt) as SUM2
FROM [spstrd00_starrpt].[dbo].[rpt23t]
where [rpt20t].note_id =[rpt23t].note_id
ERROR Message
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.rpt23t'.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "rpt20t.note_id" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ttl_qualfyng_fee_amt'.
View 4 Replies
View Related
Oct 10, 2007
Hello,
I want to write a query that joins data in a different table based on a column value. The table is for a "Playlist" and holds play list items. The items can be video, audio, images, etc. The playlist table looks like this:
Table_Playlist
-----------------
ID (int)
MediaType (char)
MediaId (int)
Table_Audio
-----------------
MediaId (int)
Table_Video
--------------
MediaId (int)
If the Table_Playlist.[MediaType] column value = "Audio" then I want to join to the Table_Audio table. If the value = "Video" then I need the video table.
Hope that makes sense. Thanks
View 1 Replies
View Related
Mar 29, 2008
Hi,
I have got a column with the string "I too Love Sql Server". I am trying an output as shown below:
I want to divide this into 4 columns :
Column1 Column2 Column3 Column4
I too Love Sql Server I.t.L.S.S
The Column 4 contains the first letter of each word. If the string contains only 2 words, Eg: "Sql Server" then Column 2 will be empty and only Column 1,3 and 4 will be filled.
How do I do this?
View 2 Replies
View Related
Jul 20, 2005
Hi,Have a database that contains various tables.I need to run a query on two tables.Table A contains a column called TitlesTable B contains a column called Uni_TitlesIn Table B's column it contains multiple titles as shown belowseparated by semi-colons in each row.Table B Uni-Title Column__________________________Row 1: Landlord and tenant; Leases; Rent reviewsRow 2: Acquisitions; Advisers; Appointment; Contract termTable A - Titles Column_________________________Table A's column contains a only one of the words in Table B's columni.e. Landlord and tenantI would like to obtain a count of how many times the same word orphrases appears in the column of Table B.ThanksSteve
View 2 Replies
View Related
Feb 27, 2008
Hi,
I want to copy xls file from remote server to my server.
i want to use xp_cmdshell to perform this operation.
can anybody help me out how to pass parameter to xp_cmdshell.
thanks in advance,
Chetan S. Raut.
View 3 Replies
View Related
Dec 21, 2007
I have to look up a mapping table and add 5 more columns from the mapping table if the look on the first column in the mapping table and corresponding column in source is successful.
But if there is no matching record in then mapping table I still need the record to pass through with NA values.
Please help?
View 3 Replies
View Related
