combining two tables

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 3select A.col1,B.col1
FROM table_A A,table_B B

good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks

Combining or Concating seperate fields for date?

I have two tables in a SQL db.
Each has 3 separate fields used to store a date info:

lastservicemonth tinyint1
lastserviceday tinyint1
lastserviceyear smallint2
(and these fields can be nulls)

I want to compare the date info in Table A vs. Table B and find the latest date between the two.

I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
But ths far I have been unsuccessful.

Any help would be greatly appreciated!To get the latest row, use:SELECT TOP 1 *
FROM [Table A]
ORDER BY lastserviceyear, lastservicemonth, lastserviceday-PatP|||Thanks Pat - I can can see how my question was unclear. Hope this clarifies.

Say Table A and Table B both contain the same person records for when they last came in the hospital. But the tables contain different dates.

Example:
Table A - ID #123, John Doe , 11-1-2007
Table B - ID#123, John Doe, 12-3-2007

I want to update the date fields in Table A with the data in Table B, but only if the date in Table B is more recent than the date in Table A.

So, I need to compare the dates for each person in Table A to the same person in Table and determine which visit date is more recent.

Hope this makes more sense.|||Why are two tables storing such similar information?

If you give us the real problem, it might also be easier to decipher than "table A and table B..."|||While I understand the desire to "simplify" a problem for posting purposes, the process usually infuriates me... All too often critical pieces of information get "simplified" out of the example that gets posted!

Can you post at least the DDL for the tables (in other words the CREATE TABLE statements needed to recreate them), and whatever attempt you've made so far to do what you want? This would help us a lot in determining what you need.

-PatP|||I appreciate your feedback. I can see I need to clarify.
Table A and B are in different dbs connected to different apps.
The apps communicate with each other imperfectly, so the dates get out of synch.

I imported the id# and the day, month and year column from System B, Table B to system A.
Now I want to update Table A with that data.

Here's a select statement where I attempt to identify discrepancies in the two data sets. If I can correct this , I can do an update statement. The statement below adds the 3 date fields and arrives a a number rather than a date. Do the fields need to be converted from smallint and tinyint ?

SELECT
(p.lastserviceYEAR +'-'+ p.lastserviceMONTH +'-'+ p.lastserviceday) as pdate,
(e.lastserviceYEAR +'-'+ e.lastserviceMONTH +'-'+ e.lastserviceday) as edate
From patient p Inner Join empi e On (p.ID = e.ID)
where edate > pdate|||I'd use something like:Cast(1000 * lastserviceyear + 100 * lastservicemonth + lastserviceday AS INT)to get integer values that you can safely compare and sort... They aren't pretty to print, but they work well for comparisons and sorting.

-PatP|||I was going to suggest the use of DateAdd(), but I think that will be far more efficient.|||last time it was me dropping a zero, pat, this time it's you ;)

Cast(10000 * ...|||Excellent. That will do the trick.
Thanks to all for your help and patience!

Combined or separate?

We are trying to decide wether to place SQL Server 2000 on the IIS box or
another separate box.
What are the pros and cons of doing this? I'm thinking something like
performance (shared memory vs. TCP),
memory/cpu consumption of the SQL server. Currently the IIS box has an
average CPU usage of ~10%
without having SQL Server 2000 installed.
CasperIf the box can handle the memory and extra proc, then physically no
problem...
However, it is generally not a good practice to put your SQL on the IIS box
because of the security risk... If someone is able to hack into your IIS
box, then they also get SQL for free...
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||In general the only time this is a good idea, is when implementing a web
farm, and the load on the SQL functionality is a bottleneck to the web
application, and the SQL funtionality (from web app) is read-only.
Say in the case of something like a searh, where the write (update)
functionality is implemented through some kind of publishing on a scheduled,
recurring basis, and the web site (IIS) only reading the local database...
Then the idea of having multiple local copies of the Database, one on each
IIS box in the web farm, can allow you to scale out to any degree
necessary...
"Casper Hornstrup" wrote:

> We are trying to decide wether to place SQL Server 2000 on the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>
>|||So, is this because using shared memory don't make a significant difference
in performance or because there are other divantages that outweigh that
performance increase?
Casper
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> In general the only time this is a good idea, is when implementing a web
> farm, and the load on the SQL functionality is a bottleneck to the web
> application, and the SQL funtionality (from web app) is read-only.
> Say in the case of something like a searh, where the write (update)
> functionality is implemented through some kind of publishing on a
scheduled,
> recurring basis, and the web site (IIS) only reading the local database...
> Then the idea of having multiple local copies of the Database, one on each
> IIS box in the web farm, can allow you to scale out to any degree
> necessary...|||I would suggest that there are many reasons to separate SQL
and IIS onto different machines. As Wayne said, there is a
security risk with putting SQL on the same machine as IIS.
There is also the issue of licenses if you have multiple web
servers (and thus multiple SQL Server instances). Then there
is the idea of isolating the bottlenecks. If IIS is the
bottleneck, you can upgrade your IIS servers with more
memory, for example, without having to touch your SQL boxes.
If SQL's drive array is the bottleneck, you can upgrade one
array (assuming one SQL Server and multiple IIS machines)
without having to update all of your IIS machines. If SQL's
memory and/or CPU are the bottleneck, you can again upgrade
the SQL box while using inexpensive servers for your IIS
servers. If you need better read scalability, then you can
create a shared-none cluster of SQL servers separately from
how you handle IIS.
In short, I would suggest that only in the simplest designs
or most extreme, esoteric reasons should anyone consider
putting SQL on the same box as IIS.
Thomas
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OVJ0i2POFHA.2384@.tk2msftngp13.phx.gbl...
> We are trying to decide wether to place SQL Server 2000 on
> the IIS box or
> another separate box.
> What are the pros and cons of doing this? I'm thinking
> something like
> performance (shared memory vs. TCP),
> memory/cpu consumption of the SQL server. Currently the
> IIS box has an
> average CPU usage of ~10%
> without having SQL Server 2000 installed.
> Casper
>|||Shared memory is not the issue, unless the database is very small, the data
will be on disk at least part of the time... The issue, is that in any
production system, there is a chance that you will need more performace as
the business grows, and f your database system proovides OLTP (OnLine
Transaction Processing) functionality, which requires large numbers of
read/write operations, then you cannot <easily> scale up the performance whe
n
the database is "Copies" in multiple places. The "Writes" become a massive
issue. Reads are no problem, because you can replicate the data to multiple
instances...
So if you design and architect your system around having IIS AND SQL On the
same box, and your company never needs morethan one IIS/SQL box to service
it's business, then you're fine... But if yu need to increase performance, a
s
soon as you need to add another IIS box, (and create a web farm) you will
need to put the SQL on it's own separate box...
Again, the exception is if the SQL functionality used by the web app is
readonly...
"Casper Hornstrup" wrote:

> So, is this because using shared memory don't make a significant differenc
e
> in performance or because there are other divantages that outweigh that
> performance increase?
> Casper
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:8395D217-1EAD-42D0-BB69-4BB617A2F5C4@.microsoft.com...
> scheduled,
>
>

combined 2 data and separate them

I have a data grid with dropdownlist.
the dropdownlist is populated with datas wth a sql statement with 2 combined data
my sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1
When i select a value from the dropdownlist, i need to separate the data, name and id into different columns
how do i do it?
Is there a way to manipulate the sql to do such a thing?you can use the split function.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
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|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine Add/Edit SP's?

Rather than having 2 separate stored procedures to add and update something
like a customer record, are there any drawbacks to having one stored proc
that does both? If the custID is passed in, then it would do the update, and
if the custID param is NULL than it would do an insert. Would this approach
have any performance implications?Personally, I like that design (which some refer to as "upsert"). An
efficient pattern you can follow is:
UPDATE Tbl
SET ...
WHERE custID = @.custID
--This means no row exists already
IF @.@.ROWCOUNT = 0
BEGIN
INSERT Tbl (...)
VALUES (...)
END
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> Rather than having 2 separate stored procedures to add and update
something
> like a customer record, are there any drawbacks to having one stored proc
> that does both? If the custID is passed in, then it would do the update,
and
> if the custID param is NULL than it would do an insert. Would this
approach
> have any performance implications?|||I Use this ALL the time, but add to it using the following "design pattern"
If @.PK Is Null
Begin
Insert (ColA, ColB, ColC, ...)
Values(@.ParameterA, @.ParameterB, @.ParameterC, ...)
Set @.PK = ScopeIdentity()
End
Else If Exists (Select * From Table
Where PK = @.PK)
Begin
-- Using IsNull allows you to NOT pass in a parameter
-- and thereby effectively NOT update it (Set Null
Default values)
Update Table Set
ColA = IsNull (@.ParameterA, ColA),
ColB = IsNull (@.ParameterB, ColB),
ColC = IsNull (@.ParameterC, ColC),
..
Where PK = @.PK
End
Else
Begin
Set Identity_Insert TableName On -- When PK Is IDentity
Insert (PK, ColA, ColB, ColC, ...)
Values(@.PK, @.ParameterA, @.ParameterB, @.ParameterC, ...)
Set Identity_Insert TableName Off -- When PK Is IDentity
End
-- And then at the end, regardless of which path was taken,
Select @.PK As PK
"Adam Machanic" wrote:

> Personally, I like that design (which some refer to as "upsert"). An
> efficient pattern you can follow is:
>
> UPDATE Tbl
> SET ...
> WHERE custID = @.custID
> --This means no row exists already
> IF @.@.ROWCOUNT = 0
> BEGIN
> INSERT Tbl (...)
> VALUES (...)
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> something
> and
> approach
>
>