Clear data out of multiple tables.

I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
Thanks!
Vic(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences|||Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
Thanks,|||sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.|||Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.|||In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.

Hope that's of some help. Feel free to ask away!|||I just went back and re-read my original post and I see where I need to clarify a few points.

I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.

Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better! :o

So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.

Thanks in advance for helping.|||to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:

http://www.elsasoft.org/tools.htm

it will also script all the data out (optionally) using bcp.exe.

I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.

Choosing multiple DSV in the Cube Wizard

In the available data source views, I have 3 data source views. How do I choose more than one view from the available ones?I found a cludgy way to choose, by using the Back button and choosing tables from a different database, but, maybe there are better ways.|||

Hi Onamika,

You can not choose multiple DSVs in cube wizard. But when you have multiple DSs, you can add/remove more tables in DSV designer.

Yan

|||Precisely, I meant to say that. Using multiple sources with one DSV. also, create one cube based on one DSV.

choose max value of multiple calculations

Hi,

I need to be able to get the maximum value of different calculations.

Example :

i have 5 calculations like this :

(sum(Fields!CountTest1.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest2.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest3.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest4.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest5.Value)/sum(Fields!TestCount.Value))*100

these calculations give me a percentage from a test value against the total test values.

what function or expression can i use to get the max value of all the calculations ?

I was looking at the "choose" function but i'm not quite sure how...

anybody ? help ....

Vinnie

Hi again ...

I found a solution to my problem.

the way to do this is by using the following expression:

=math.max(value1, math.max(value2, math.max(value3, math.max(value4, value5))))

in fact it is the max function that is standard in the math expressions from rs.

greetings.

Vinnie

Chinese and Japanese characters in same colation

SQL 2000, latest SP. We currently have the need to store data from a
UTF-8 application in multiple languages in a single database.

Our findings thus far support the fact that single-byte and
double-byte characters can be held in the same DB without issue.
However, when holding two sets of DIFFERING double-byte characters
(i.e. Chinese and Japanese) there are issues.

Since Japanese has a superset of both Kanji and Katakana characters
it's our theory that the Japanese collations will hold Chinese as well
(Mandarin).

1) Has anybody tried to store multiple languages in the same db? What
collation was used?

2) Is it possible to change collation by table?

3) Which collation of Japanese should be used for best multibyte,
UTF-8 character sets? Currently we're testing with Japanese_CI_AS
(encoding MS932).

Any and all responses appreciated,

gary@.shimanoweb.comGPenn (gbpenn@.yahoo.com) writes:
> SQL 2000, latest SP. We currently have the need to store data from a
> UTF-8 application in multiple languages in a single database.

You cannot store UTF-8 data in an SQL Server database. But UTF-8 is
just an encoding form of Unicode, and in SQL Server you store Unicode
data as UTF-16.

> Since Japanese has a superset of both Kanji and Katakana characters
> it's our theory that the Japanese collations will hold Chinese as well
> (Mandarin).

Yes, Unicode unifies the Japanese and Chinese ideographs. The idea is
that if they look different, that is a font and presentation issue.

> 1) Has anybody tried to store multiple languages in the same db? What
> collation was used?
> 2) Is it possible to change collation by table?

In SQL Server you can have different collations on different columns,
so you could have

chinese_text nvarchar(23) COLLATE <some Chinese collation>
japanese_text nvarchar(23) COLLATE Japanese_xx_xx

Then whether this is a good idea, depends on your application.

> 3) Which collation of Japanese should be used for best multibyte,
> UTF-8 character sets? Currently we're testing with Japanese_CI_AS
> (encoding MS932).

That is defintely not my field of expertise, but beware that there
are also Width and Kana-sensitive variations.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Checking field within a selected record (**)

Hello,
I have sql statement that is selecting multiple invoice records and doing
multiple calculations.
Now, I need to look in each invoice record for Item_detail, any direction on
how I would do that would be appreciated?
Thanks.Hi
Can you post DDL+ sample data+ expected result?
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
> on
> how I would do that would be appreciated?
> Thanks.|||is there a message board or something where we can put this as a disclaimer
:)|||That would be nice. Along with a post explaining how to return a
concatenated list of values for a single column in multiple rows, and a
"BEWARE OF --CELKO--" sign.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> is there a message board or something where we can put this as a
disclaimer :)|||Oh come on... We all love Celko... LOL
Grant
Who gives a {censored} if I am wrong.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> That would be nice. Along with a post explaining how to return a
> concatenated list of values for a single column in multiple rows, and a
> "BEWARE OF --CELKO--" sign.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> disclaimer :)
>|||of course we love him as long as we are not in the firing end :)
--
"Grant" wrote:

> Oh come on... We all love Celko... LOL
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>|||Just to explain what is meant by DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
on
> how I would do that would be appreciated?
> Thanks.|||Did I ask for something peculiar?
I thought this was a helping board.
"Omnibuzz" wrote:
> of course we love him as long as we are not in the firing end :)
> --
>
>
> "Grant" wrote:
>|||Sorry, we got a little off topic, it happens sometimes. Nothign at all odd
for what you asked for, only that it was very vague, and we could give a
hundred answers, most of which would be completely unrelated to what you are
trying to do. You did not include enough information for us to give you a
good answer. Please see my other post and respond with the needed
information.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:5B83C9D2-7A2C-46DD-B0DA-39D1B6483548@.microsoft.com...
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
and a|||Oops.. sorry.. we thought we'll keep the thread alive till you get back with
the ddls and sample data.
--
"ITDUDE27" wrote:
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>

Checking datatypes of a field accoss multiple tables

I have a tables called subsid that I need to change the datatype from
text to int.

I think I got them all but is there a query I can run that will check
all fields call subsid accross all tables that are of type text.Try:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'subsid' AND
DATA_TYPE = 'text'

--
Hope this helps.

Dan Guzman
SQL Server MVP

<tdmailbox@.yahoo.com> wrote in message
news:1113780531.744940.153260@.l41g2000cwc.googlegr oups.com...
>I have a tables called subsid that I need to change the datatype from
> text to int.
> I think I got them all but is there a query I can run that will check
> all fields call subsid accross all tables that are of type text.

checkbox and listbox for report parameters

How can I use checkbox and listbox (with multiple selection) for report
paramters? Do I need to go into the coding to do this, or I can do this from
the designing tool? Currently I can only use either a textbox (for single
value) or a pulldown box (for list of pre-defined values). I want to have a
listbox where the user can select multiple values and a checkbox to indicate
a Yes/No filter. Thanks.There have been lots of posts about this topic. Check the archives, which
you can find here...
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs?hl=en&lr=
... and search for multi-value parameters.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:9EA971CF-8606-43E2-A09F-223008E2076A@.microsoft.com...
> How can I use checkbox and listbox (with multiple selection) for report
> paramters? Do I need to go into the coding to do this, or I can do this
> from
> the designing tool? Currently I can only use either a textbox (for single
> value) or a pulldown box (for list of pre-defined values). I want to have
> a
> listbox where the user can select multiple values and a checkbox to
> indicate
> a Yes/No filter. Thanks.|||Thanks for the info.
"Jeff A. Stucker" wrote:
> There have been lots of posts about this topic. Check the archives, which
> you can find here...
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs?hl=en&lr=
> ... and search for multi-value parameters.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:9EA971CF-8606-43E2-A09F-223008E2076A@.microsoft.com...
> > How can I use checkbox and listbox (with multiple selection) for report
> > paramters? Do I need to go into the coding to do this, or I can do this
> > from
> > the designing tool? Currently I can only use either a textbox (for single
> > value) or a pulldown box (for list of pre-defined values). I want to have
> > a
> > listbox where the user can select multiple values and a checkbox to
> > indicate
> > a Yes/No filter. Thanks.
>
>