Combining two tables from different databases

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?not without creating all new PK values.

if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)

in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.

the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.

DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

sqlsql

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

Combining 3 datasource controls with t-sql

Hello everyone,

I'm trying to get a count of 3 different types on the same field. For Example, let's use Gender as the field with these options: Male, Female, Not Given. What I'm wanting to do is retrieve a count for each type. What I have so far is: SELECT COUNT(Gender) WHERE Gender = 'Male' and I have to duplicate this in 3 different data controls. I would like, however, to have one datasource control with a statement along the lines of:

SELECT ( SELECT COUNT(Gender) FROM Users WHERE Gender='Male), SELECT COUNT(Gender) FROM Users WHERE Gender='Female', SELECT COUNT(Gender) WHERE Gender='NotGiven' )

From Users

Or something to that effect. Any suggestions?

Thank you greatly for your help,

Mark

SELECTSUM(CASEWHEN Gender='Male'THEN 1ELSE 0END)as MaleCount,

SUM(CASEWHEN Gender='Female'THEN 1ELSE 0END)as FemaleCount,

SUM(CASEWHEN Gender='NotGiven'Or GenderISNULLTHEN 1ELSE 0END)as NotGivenCount

FROM Users

|||

This ended up working for me: SELECT Count_1 = (SELECT COUNT(Gender) FROM User WHERE Gender='male'), Count_2 = (SELECT COUNT(Gender) FROM Users WHERE Gender='female') and then access Count_1 and Count_2

Thanks for your help.. I will mark yours as the answer because it looks like it would work too.

sqlsql

Combined result...

hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.well, daimous, it seems like you did not understand why i moved your previous thread to the microsoft SQL Server forum

so here is the SQL answer --select street||City||postal_code as address
from yourtableif you find that this doesn't work in SQL Server, i trust it will bring to your attention that SQL Server questions should be posted in the SQL Server forum and not the SQL forum

:)|||Try this

Select [street]+', '+[city]+' '+[postal_code] as Address
from YourTable

This assumes that you have [postal_code] defined as a varchar, and not an integer or numeric field. I put in some spaces and a comma, so your output would be something like this:

Street, City Postal_Code|||If you have NULL values in your table and are using default SQL Server settings, you may need to use this:

Select coalesce([street]+', ', '')+Coalesce([city]+' ', '')+Coalesce([postal_code], '') as Address
from YourTable

Now, go open up Books Online and read about concatenation and the COALESCE function.

Combine fields

I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thankscould you provide us some info as outlined in the hint sticky that's on top of the board?|||Select DocNoc, DocDesc from DocTable (take these to fields and insert it into one field in the same table.
Insert into DocNumDesc

How would I go about creating this syntax. Thanks|||SELECT DocNoc+DocDesc

??|||Okay is the correct syntax:

INSERT INTO DocNumDesc
SELECT DocNoc +DocDesc
FROM DocTable


Thanks

Columns in database

Hi Group,
This might sound silly, but how can I find the tablenames in MSSQL2000 with which contain a certain columnname?
For example I want to get all the tablenames in a database which contain the column ProjectID.
Thanks for your helpselect * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'|||Originally posted by Satya
select * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'

Your the BEST!!
Thanks a lot.

columns count

Please any one can tell me.
How to defined Number of columns for tables?
For example I have one table in my database called “my_table” it has number of columns
What the functions or any thing I can use to get the number of columns for that table and assigned to local variable?
Declare @.P_count_int
Select @. P_count_int = ??
If this table (my_table have 5 columns )
The result should be if I print this variable 5
SELECT @.p_count_int = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='my_table'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mmsjed" <anonymous@.discussions.microsoft.com> wrote in message
news:D1CFBD6D-9872-4B36-89A7-944CB9A9B396@.microsoft.com...
> Please any one can tell me.
> How to defined Number of columns for tables?
> For example I have one table in my database called my_table it has
> number of columns
> What the functions or any thing I can use to get the number of columns for
> that table and assigned to local variable?
> Declare @.P_count_int
> Select @. P_count_int = ??
> If this table (my_table have 5 columns )
> The result should be if I print this variable 5
>

columns count

Please any one can tell me.
How to defined Number of columns for tables?
For example I have one table in my database called “my_table” it has num
ber of columns
What the functions or any thing I can use to get the number of columns for t
hat table and assigned to local variable?
Declare @.P_count_int
Select @. P_count_int = '?
If this table (my_table have 5 columns )
The result should be if I print this variable 5SELECT @.p_count_int = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='my_table'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mmsjed" <anonymous@.discussions.microsoft.com> wrote in message
news:D1CFBD6D-9872-4B36-89A7-944CB9A9B396@.microsoft.com...
> Please any one can tell me.
> How to defined Number of columns for tables?
> For example I have one table in my database called my_table it has
> number of columns
> What the functions or any thing I can use to get the number of columns for
> that table and assigned to local variable?
> Declare @.P_count_int
> Select @. P_count_int = '?
> If this table (my_table have 5 columns )
> The result should be if I print this variable 5
>

column type

I am putting together a SQL table which will hold a latitude and
longitude for each record. An example of a latitude is: 47 05 36.5
Which column type would best represent this and retain the spaces
between degrees, minutes, seconds? Text, varchar, char?

Thanks
Jeff

jeff-godfrey@.wa.nacdnet.orgDon't use Text. Text is only useful for large texts and needs special
treatment.

Since the variability in value length will be small I would opt for the
char datatype. Other than that varchar is a fine choice as well. If you
choose char, make sure you don't overdimension it (you would waste
space).

Gert-Jan

Jeff Godfrey wrote:
> I am putting together a SQL table which will hold a latitude and
> longitude for each record. An example of a latitude is: 47 05 36.5
> Which column type would best represent this and retain the spaces
> between degrees, minutes, seconds? Text, varchar, char?
> Thanks
> Jeff
> jeff-godfrey@.wa.nacdnet.org|||Unless you are doing any computations on the values in this column, you can
use CHAR datatype. If each portion of this data has operational significance
or need any computations, you may be better off storing them as separate
numeric columns.

--
- Anith
( Please reply to newsgroups only )|||>> a latitude and longitude ... Which column type would best
represent this and retain the spaces between degrees, minutes,
seconds? Text, varchar, char? <<

If you have to compute with them, you might want to use FLOAT and
convert things to radians, and display them with a UDF.

Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.

Column name as the result of a query?

Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a' as @.that

LOL

I just tried another way as i was going to post

declare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a' as "@.that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordandeclare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a'= @.that

Colors in CR ...

Hi.

Is there a way ( most likely, but I'm unaware of ) to change the color of just a part of the text in Cr ?

For example: I have a filed which contains the word : Excellent ..

How can I tell Cr to print/display the word in black, except for the last two letters nt, which should be printed/displayed in, say, red ?

Thnx.If it's just a plain text field then yes, it's quite easy - just highlight the letters of the field you want in a different colour, and select Text Formatting.

However, I'm assuming you want it to be one of the dynamic database fields. There doesn't appear to be any functions in Formula Editor that are capable of changing text colour, so I don't think it is possible.

(One idea I had was to split the field into two separate formulas, but it doesn't appear that I can join the result of two text formulas back together. Perhaps you can build on this idea to come up with a solution).

colors

Hello is there any way in a text box to make the text different colors?
For example,
if you have "Hello World"
I'd like "Hello" to be red and "World" to be green.
Thanks,No, the format applies to the whole control.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--