I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
I have tried the following union query which combines the two RS's then sorts the data: SELECT Top 2 ID, Song, Hits FROM Table1 UNION SELECT Top 2 ID, Song, Hits from Table2 Which would return the first two records from each then sort them like this: 2 - tb1SONG 2 - 1459 1 - tb2SONG 1 - 412 1 - tb1SONG 1 - 356 2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this: 3 - tb2SONG 3 - 2035 2 - tb1SONG 2 - 1459 4 - tb1SONG 4 - 965 5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated. Thanks
Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.
tb1 c1: 1 2 3 4 5
tb2 c2: a b c d e
if i run the query
SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2
i get tb3: c1: c2: 1 a 1 b 1 c 1 d 1 e 2 a 2 b 2 c ........
I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.
My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?
ALM, PV, AD, ACE
select * from all tbls where alm.id = pv.id and alm status = 'disabled' and pv status = 'active'
I want to include the status field from 3rd and 4th table.
I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
------------------------------------------ how to create a view that show me and combine the 2 tables all month from first day of the month until the end of the month like this ----------------------- empid basedate shift ---------------------------- 12345678 01/04/2007 1 12345678 02/04/2007 1 12345678 03/04/2007 1 12345678 04/04/2007 1 12345678 05/04/2007 1 12345678 06/04/2007 1 12345678 07/04/2007 1 12345678 08/04/2007 1 12345678 09/04/2007 1 12345678 10/04/2007 1 12345678 11/04/2007 10 12345678 12/04/2007 10 12345678 13/04/2007 10 12345678 14/04/2007 10 ................................. .................................... ...................add the missing date until the end of the month 12345678 31/04/2007 10
98765432 01/04/2007 10 .................................... ...................add the missing date from the start of the month
I need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) (SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement. i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server. </P> <P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";</P> <P> Sql Server in Cerebrum: database dals DalsData ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag 123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1 124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | | MySqlServer in Copernicus: database stat Statuslog ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%) 1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100 2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75
the output in datagrid should be: ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag 123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1 124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | | could someone help me how would i do this?
I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server. So what is the syntax to do it ?? hope it's possible...
Hi, anybody can help me.How can i synchronize 2 tables on 2 different sql servers 2000i mean TABLE1(col1, col2, col3, col4) andTABLE1(col1, col2, col3, col4, col5, col6)the first 3 colums are the same in rwo tables.Thanks--Message posted via http://www.sqlmonster.com
I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)
select @@ServerName, bs.server_Name, bs.Database_Name, bmf.physical_Device_Name, bs.backup_start_date, bs.backup_finish_date, Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status, Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success from msdb.dbo.backupset bs Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id where type = 'd' and bs.backup_set_id = (select max (bso.backup_set_id) from msdb.dbo.backupset bso where bso.server_name = bs.server_Name and bso.Database_Name = bs.Database_Name and bso.Type = 'D') order by database_name
I have 2 tables with the following structure: CREATE TABLE [dbo].[table1] ( [RID] [int] IDENTITY (1, 1) NOT NULL , [RText] [varchar] (400) NULL , [DateModified] [datetime] NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] ( [GrpRID] [int] IDENTITY (1, 1) NOT NULL , [GrpID] [varchar] (10) NOT NULL , [RID] [int] NOT NULL , [Status] [bit] NULL , [SortOrder] [int] NULL , [DateModified] [datetime] NULL ) ON [PRIMARY] GO I am transfering 2 table between 2 SQL server based on GrpRID from table2. Since RID is identity in table1 sometimes it is different text for spesific Rid in second server. Some how I need to get the match the right text from server1 to server2 and if text doesn't exists create a bew entry in table1 with the update to table2 wich should reflect correct RID.
I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before.Found this stored procedure
sp_addlinkedserver('servername').Would you just execute this and then run your query after the SP?
I have user that we just migrated his Access database to SQLServer. All went well with the migration, but then he came up withanother requirement to be able to replicate the database to a localSQL server living on the hard drive of a laptop. Before the migrationhe just copied the entire Access databse to the lap top.I tried using the Copy SQL Server Objects Task to move thenecessary tables from the production server to the laptop, but noticedit doesn't copy over the table Indexes/keys identiy fields etc. Iended up backing up the production database and restoring it to thelaptop database, but wondered if there is any way to move the tables,with their properties from one server to another? I know I can setup the backup process to run as scheduled, but the problem is the dataneeds to be moved on an irregular time table. I thought about justwriting code on the remaining Access front end to empty the localtables and then query the data from the production side to reloadthem, but I'm sure there's an easier way.Any suggestions would be appreciated.Thanks,Tom
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers TABLE A: [ServerA].[ProdDB].dbo.[Orders] TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.
I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
