I need to combine data from two tables located in the same database.
The column GID in Table 1 and RealEstateID in Table 2 has a common id
I want to specify a date interval (AcquistitionDate in Table 2) eg 20131001 to 20131030 and get Name from table 1, and combine these to a result table.
Something like this (?) : [URL] .....
Hello everyone,
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO
Is there any way to do this? Or do I have to manually enter all the entries?
Any help would be great. Thank you.
-Alec
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
Hello,
I have two tables that I would like to combine but with some rules.
Table 1 has two columns with Actual Time and Actual People
Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time
If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex.
Planned Time has 20:00,23:00
Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15
Actual People has 5,2,5,3
Output should be like
Planned_Time,Actual_Time,Planned_People,Actual_People
,19:00,,5
20:00,19:30,2,2
,19:45,,5
,21:15,,3
23:00,,5,
I can do this in asp but if this can be done in sql it would be better.
Any ideas appreciated.
Thanks.
hi
this is my query:
tabel1:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'
if i run this query i will get output like this:
userid user_name password role_code expiry_date created_date
1 karthik karthik AD 01/17/20082007-01-24
active
0:00:00.000 1
i have another table which has the following records
tabel 2:
select * from code master
codename codedescription
AD admin
sp supervisor
so in the first query i need the output as like tithe following:
userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/20082007-01-24
active
0:00:00.000 1
so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad
so please give me query for this
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b
15 _ a ____________ ny _____ arena
25 _ d ____________ fg_____ metus
35 _ f ____________
45 _ f ____________
these two tables above will become table-c like below;
table-c
15__arena
25__metus
35__null
45__null
but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
Hi all,
I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS.
When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works..
SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT
FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS,
MAT_INCIDENTS I, MAT_DELIVERY D,
PASMAIN P
WHERE FA.QUESTIONID=QS.ID
AND FA.INCIDENTID=I.INCIDENTID
AND I.INCIDENTID=D.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND QS.QUESTIONNAIREID=2
AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER
from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P,
MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS,
MAT_ANSWERS A, MAT_INCIDENTANSWERS IA
WHERE I.INCIDENTID=D.INCIDENTID
AND I.INCIDENTID=QI.INCIDENTID
AND D.INCIDENTID=IA.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND Q.ID=QI.QUESTIONNAIREID
AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID
AND QS.ID=A.QUESTIONID
AND A.ID=IA.ANSWERID
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND Q.ID=2
AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
Would appreciate any help!
Fiona
I have a database, and I need data from 3 tables.
From the first table I just need the primary key: issueID.
I can make a left outer join with my second table so I can have this result.
select A.issueID, B.projectID
from issuerequest as A left outer join projects as B on (A.issueID=B.referenceID)
group by A.issueID
gives:
100 | null
101 | P003
102 | P002
103 | null
...
When the value in the second column is null, I need to check the value
in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project
from issuerequest as C left outer join ProductCustomer
on (C.customer = D.customerID) and
(C.product=D.produktID)
where actuellproduct='1'
group by C.issueID
this gives a result as:
100 | P002
100 | P003
101 | P004
102 | P002
103 | null
...
What I actually want is just 2 columns with
in the first IssueID and in the second Project
and no duplicate data. Everything (except null) is more
important then the same value in column 3.
100 | P002
101 | P003
102 | P002
103 | null
I've tried with except, but can't get all duplicate
data out.
Hi...
I have two tables (teachers, students) each table has 3 fields (FirstName, LastName, EMail). I'm trying to combine the data from both tables into one so I have a "master list" to send e-mails to all teachers and students via an ASP script/CDO.
I've been reading up on Joins but I can't figure out the SQL to combine data from both tables. Any ideas?
Thanks,
Steven Lee
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?
I am trying to do a really weird combination of an Append/Union query..I'm doing most of my commands through macros and queries.how to do a normal Append/Union/Create queries but I am trying to do something kind of weird. If table X has rows A, B, C, and D, and table Y has a column with rows 1, 2, 3, and 4... I need the values in table Y to be assigned to each individual row in table X. Is there a way to create a new table where the columns merge such that values can be assigned like:
X Y
A 1
B 1
C 1
D 1
A 2
B 2
C 2
D 2
A 3
B 3
Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.
I have the following 2 tables:
I'm trying to get the below output:
Here's the SQL I've been using:
SELECT
a.PERSON_ID,
sum(a.AMOUNT),
CASE
WHEN b.JOB_CODE = 10 then "Level 2 Seller"
WHEN b.JOB_CODE = 15 then "Level 3 Seller"
ELSE
'Level 1 Seller'
END as "Job_Code"
FROM
tbl_SALES a,
tbl_JOBFUNCTION b
WHERE
a.PERSON_ID = b.PERSON_ID
GROUP BY
a.PERSON_ID,
b.JOB_CODE
Here's what my results are with the query I've been using:
Each person could have many job codes, but when they have 10 then they're consider "level 2".
- If they have 15 then they're "level 3".
- If they don't have either 10 or 15 then they're "level 1".
- If they have both 10 and 15 they're still "level 3".
Please help.
Thanks,
Jim
Hey all
I'm trying to get data from two tables that have the same fields. One is a log table and the other is the actual table. I have the following:
SELECT tbl_LCData.* FROM tbl_LogOfLCDataTable, tbl_LCData
WHERE (((SELECT DATENAME(MONTH, tbl_LCData.PrintDate)) = (SELECT DATENAME(Month,getdate())) AND (SELECT DATENAME(MONTH, tbl_LogOfLCDataTable.PrintDate)) = (SELECT DATENAME(Month,getdate()))))
I am trying to display records that were printed this month. I only get data from tbl_LCData. It doesn't display data from the log table. For example:
Contents of tbl_LCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
18, B, Whatever, 11/04/2007
Contents of tbl_LogOfLCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 01/06/2008
So, I want the query to display:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
17, A, Sample, 01/06/2008
The above shows records printed in the month of June!
Is there a join I need to use??
Many thanks,
Rupa
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
View 2 Replies View RelatedHi all,
How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks
Table A
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Table B
Client Weight Purchase
Jim 2 5
Lee 4 3
Bob 6 7
Combination table (result)
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Jim 2 5
Lee 4 3
Bob 6 7
Hii all.
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
any help is appreciated.
Neil
I have database with two different tables BaseData and QualityDetails as given below with ID as the Primary key
BaseData.
Sent_to_Client Name ID
1/2/2015 ABC1 1000000
2/24/2015 ABC2 1000001
5/24/2015 ABC3 1000002
3/24/2015 ABC4 1000003
QualityDetails
ID Error
1000000 1
1000001 2
1000002 0
1000003 3
I want to write an SQL Query to get the data by combining both the tables by dates from 2/1/2015 to 5/30/2015 in BaseData table. My table after executing the query should be
ID Name Error
1000001 ABC2 2
1000002 ABC3 0
1000003 ABC4 3
I have two tables - T_Assets and T_Inspections.
I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:
SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)
FROM T_Assets
INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID
GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this.
Jagdip
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are :
setup_zipcode,
setup_category,
and listing
and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name
and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone
Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
SHPH.ProductID,
SHPH.ReceivedDate,
SHPH.ShipmentNo,
SHPD.Vendor,
SHPD.Quantity,
QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL ELSE SHPH.ReceivedDate
END,
QShipQty = CASE WHEN SHPD.TransCd = 'F'
THEN NULL
WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL
ELSE SHPH.ShippingQty
END,
PROD.ProductName,
QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
THEN 0 ELSE MEND.OpeningAccrual
END
FROM dbo.tblShipmentHdr SHPH
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121)
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
Hi there, I'm trying to generate a report for an old database and I'mhaving trouble coming up with an elegant way of going about it. Usingcursors and other 'ugly' tools I could get the job done but 1) I don'twant the report to take ages to run, 2) I'm not a big fan of cursors!Basically there are tables that track history and each table tends totrack only a specific value housed within a date range. I'm trying tocombine the tables to get a snap-shot of the complete history. I'mhaving problems dealing with the Start/End Dates from the two tablesand building the dates in the final table to be broken down by 'historytype'.Here are a few sample records and the results I'm trying to achieve:Table 1:CAgyHist (ProdID,AgyID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 5, 20061 2 Jan 5, 2006 Jan 25, 20061 1 Jan 25, 2006 NULLTable 2:CInvHist (ProdID, InvID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 23, 20061 2 Jan 23, 2006 Jan 15, 20061 1 Jan 15, 2006 NULLDesired End Result:CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)1 1 1 Jan 1, 2006 Jan 5, 20061 2 1 Jan 5, 2006 Jan 15, 20061 2 2 Jan 15, 2006 Jan 23, 20061 2 1 Jan 23, 2006 Jan 25, 20061 1 1 Jan 25, 2006 NULLMy challenge thus far has been dealing with the dates as they don'tnecessarily correspond - from one table to the other.I am by no means a database expert of any level and any help would begreatly appreciated.Thanks,Frank.
View 7 Replies View RelatedI have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell
View 2 Replies View RelatedHi all,
I have the following tables
Tbl_Request
------------------------------------------------------------------------------------
RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
----------------------------------------------------------------------------------
Tbl_User
------------------------------------------------------
ID SkillCategory Experienced
-----------------------------------------------------
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0
---------------------------------------------------
Experience is a bool column.
Required Output:
---------------------------------------------------------------------------------------------------------------------------------
SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply
---------------------------------------------------------------------------------------------------------------------------------
.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
----------------------------------------------------------------------------------------------------------------------------------
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
Hi,
I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.
Does anyone have any inkling into how I would do this.
I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.
e.g. output
personID, personForename, personSurname,
1, pf, langan
***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo
***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6
Hi,
Can anyone tell me a solution how to combine 3 primary files that are existing in a particular database into one. One of the database that was running has three primary(.mdf) files. Can any one tell me whether the files created in that way has impact on the performance of the server.Solution for this problem is highly appreciated. Thanks in advance.
VS.
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View 3 Replies View RelatedDoes anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedOk say I would like to build a table for of the following questions(say 6 questions for the sake of argument):
Do I just stored the index of the radiobuttonlist. What are some resources that I could look at. Should I make a look up table.
5) If money were no object, I would live . . .
Prefer not to say
On a tropical island
In a New York penthouse
In an English castle
On a Texas ranch
In a Malibu beach house
In a mountain retreat (Selected)
On the moon
None of the above
1 --->
2 --->
3 --->
4 --->
5) --->6 This is the question we are looking at.
6 --->
How should I create the database table for the above example.