combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jen
Create a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA
sqlsql

combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jenCreate a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA

combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jenCreate a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
--
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA

Combining 2 tables with date ranges

Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want 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 to
track only a specific value housed within a date range. I'm trying to
combine the tables to get a snap-shot of the complete history. I'm
having problems dealing with the Start/End Dates from the two tables
and building the dates in the final table to be broken down by 'history
type'.

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, 2006
1 2 Jan 5, 2006 Jan 25, 2006
1 1 Jan 25, 2006 NULL

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 23, 2006
1 2 Jan 23, 2006 Jan 15, 2006
1 1 Jan 15, 2006 NULL

Desired End Result:
CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
1 1 1 Jan 1, 2006 Jan 5, 2006
1 2 1 Jan 5, 2006 Jan 15, 2006
1 2 2 Jan 15, 2006 Jan 23, 2006
1 2 1 Jan 23, 2006 Jan 25, 2006
1 1 1 Jan 25, 2006 NULL

My challenge thus far has been dealing with the dates as they don't
necessarily correspond - from one table to the other.

I am by no means a database expert of any level and any help would be
greatly appreciated.

Thanks,
Frank.what do you mean by , "the dates don't correspond from 1 table to the
other"?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Frank" <mrpubnight@.hotmail.com> wrote in message
news:1151369612.360817.191930@.c74g2000cwc.googlegr oups.com...
> Hi there, I'm trying to generate a report for an old database and I'm
> having trouble coming up with an elegant way of going about it. Using
> cursors and other 'ugly' tools I could get the job done but 1) I don't
> want 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 to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> 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, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.
> I am by no means a database expert of any level and any help would be
> greatly appreciated.
> Thanks,
> Frank.|||>From your data, CInvHist has this row

CInvHist (ProdID, InvID,StartDate,EndDate)
1 2 Jan 23, 2006 Jan 15, 2006

which has StartDate *after* the EndDate. Is this what you mean?|||It looks like you want to treat the 2 tables as one so you can sort by
the start date? If so, then you can use a union query and use the order
by clause at the end of the second select statement like:
select * from table1
union
select * from table2
order by start date

Jason|||Frank (mrpubnight@.hotmail.com) writes:
> Basically there are tables that track history and each table tends to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> 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, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.

There should be a fair chance to this in a query (or possibly two
with help of some temp table). But since it's bit complex, the hour
is late, and your sample data is unclear, I prefer to ask for
clarification:

1) What are the keys of these tables?
2) What do they signify?
3) What is the combined table supposed to describe?
4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
In the latter case, can you provide an updated sample?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry everyone there was a typo and I will expand a little as well.

1) The keys are as follows (both tables have primary ID keys too but
they weren't included in the original question - see brackets below)
CAgyHist:
(CAH_ID PK)
ProdID FK
AgyID FK

CInvHist:
(CIH_ID PK)
ProdID FK
InvID FK

2) ProdID = PK from the products table.
AgyID = PK from the Agency table (i.e. Supplier)
InvID = PK from the InventoryType table (categorization for products)

3) Products in our application can move from supplier to supplier and
can also change their categorization. Each of the history tables
tracks these changes as they occur and when they occur. The start date
is obviously when the product begins with the corresponding agency or
categorization, and the end date is when it finishes (a NULL value
means that the product is still with a given agency or being
categorized in a certain manner.

The problem I want/need to solve is I need a complete historical
account for a product as it moves from agency to agency and from
categorization to categorization and I need it to be on a single report
(table) and chronological, so hence the final table which shows how the
product has moved throughout time.

4) Yes, sorry that was a typo. The CInvHist table records should have
read:

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 15, 2006
1 2 Jan 15, 2006 Jan 23, 2006
1 1 Jan 23, 2006 NULL

Sorry about all that confusion. I'm really hoping that this isn't too
tough or time consuming (from an execution point of view).

Again, any help will be appreciated.

Thanks,
Frank

Erland Sommarskog wrote:
> Frank (mrpubnight@.hotmail.com) writes:
> > Basically there are tables that track history and each table tends to
> > track only a specific value housed within a date range. I'm trying to
> > combine the tables to get a snap-shot of the complete history. I'm
> > having problems dealing with the Start/End Dates from the two tables
> > and building the dates in the final table to be broken down by 'history
> > type'.
> > 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, 2006
> > 1 2 Jan 5, 2006 Jan 25, 2006
> > 1 1 Jan 25, 2006 NULL
> > Table 2:
> > CInvHist (ProdID, InvID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 23, 2006
> > 1 2 Jan 23, 2006 Jan 15, 2006
> > 1 1 Jan 15, 2006 NULL
> > Desired End Result:
> > CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> > 1 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 1 Jan 5, 2006 Jan 15, 2006
> > 1 2 2 Jan 15, 2006 Jan 23, 2006
> > 1 2 1 Jan 23, 2006 Jan 25, 2006
> > 1 1 1 Jan 25, 2006 NULL
> > My challenge thus far has been dealing with the dates as they don't
> > necessarily correspond - from one table to the other.
> There should be a fair chance to this in a query (or possibly two
> with help of some temp table). But since it's bit complex, the hour
> is late, and your sample data is unclear, I prefer to ask for
> clarification:
> 1) What are the keys of these tables?
> 2) What do they signify?
> 3) What is the combined table supposed to describe?
> 4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
> In the latter case, can you provide an updated sample?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your sample data is a mess, but the usual way is to build a calendar
and join these improperly designed tables together with BETWEEN
predicates, something like:

SELECT C.cal_date, T1.a, T2.b, ..
FROM Calendar AS C, T1, T2
WHERE C.cal_date BETWEEN T1.start_date AND T1.end_date
AND C.cal_date BETWEEN T2.start_date AND T2.end_date
AND .. ;

MIssing or reversed data will not be shown in this query.|||Frank (mrpubnight@.hotmail.com) writes:
> 1) The keys are as follows (both tables have primary ID keys too but
> they weren't included in the original question - see brackets below)
> CAgyHist:
> (CAH_ID PK)
> ProdID FK
> AgyID FK
> CInvHist:
> (CIH_ID PK)
> ProdID FK
> InvID FK

That's a bit problematic. It s not clear whether I can trust whether
ProdID, StartDate can be unique, or whether there can be more entries for
the same day and product. In my solution below, I have assumed they are
unique. Then again, if they were there is no reason for that CAH_ID.

Here is a query that works with your sample data. I will have to admit
that I'm not fully certain on how it works, and I would recommend you
to test further. I would also suggest that you check out
http://groups.google.com/group/comp...48dda4c48fb808b
for a similar problem.

CREATE TABLE CAgyHist (ProdID int NOT NULL,
AgyID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

CREATE TABLE CInvHist (ProdID int NOT NULL,
InvID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

INSERT CAgyHist(ProdID,AgyID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 5, 2006'
UNION
SELECT 1, 2, 'Jan 5, 2006', 'Jan 25, 2006'
UNION
SELECT 1, 1, 'Jan 25, 2006', NULL

INSERT CInvHist (ProdID, InvID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 15, 2006'
UNION
SELECT 1, 2, 'Jan 15, 2006', 'Jan 23, 2006'
UNION
SELECT 1, 1, 'Jan 23, 2006', NULL

SELECT ProdID, AgyID, InvID, StartDate, EndDate
FROM (SELECT a.ProdID, a.AgyID, i.InvID,
CASE WHEN a.StartDate > i.StartDate
THEN a.StartDate
ELSE i.StartDate
END AS StartDate,
CASE WHEN coalesce(a.EndDate, '99991231') <
coalesce(i.EndDate ,'99991231')
THEN a.EndDate
ELSE i.EndDate
END AS EndDate
FROM CAgyHist a
JOIN CInvHist i ON a.ProdID = i.ProdID) AS x
WHERE StartDate < coalesce(EndDate, '99991231')
ORDER BY StartDate, EndDate
go
DROP TABLE CAgyHist
DROP TABLE CInvHist

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Combine multiple sql calls into 1

I have an old app that I'm trying to recode and improve performance.

From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.

Here is a quick example of the script

Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE

Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE

Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE

INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'

These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-ScottCheck out the INSERT ... EXECUTE(' ') syntax in BOL.