Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
George
Assuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
--
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining text data rows

I am working with a database derived from text documents.One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column.I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result).How do I combine the rows with the same DocumentID into a single row of text data?This will put the entire text content of each document in its own row.

TEXT001 table as it is

DocumentID

SectionNo

SectionText

1

1

Paragraph 1 of Document 1

1

2

Paragraph 2 of Document 1

1

3

Paragraph 3 of Document 1

2

1

Paragraph 1 of Document 2

2

2

Paragraph 2 of Document 2

New TEXT table

DocumentID

SectionText

1

Entire text of Document 1

2

Entire text of Document 2

I realize that I can use “union” to combine tables with the same data type, but that is not what I am trying to do.Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command.If anyone can tell how to do this, I would appreciate your help.

More modestly, I tried to use the “Group By” clause to combine the SectionText data using this query:

SELECT DocumentID, SectionText FROM TEXT001

GROUP BY DocumentID

And got this error message:

Msg 8120, Level 16, State 1, Line 5

Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be “summed”, so I tried including it in the GROUP BY clause:

SELECT DocumentID, SectionText FROM TEXT001

GROUP BY DocumentID, SectionText

And got his error message:

Msg 306, Level 16, State 2, Line 5

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?

Hi moonshadow, the following will create a stored procedure that will fullfill the requested task. run the sript, it will have only one constraint, is that i supposed that the maximum section length is hundreds of characters i.e: it will be great if you can use nvarchar instead of ntext, but if more characters are needed to be stored then comment and we will work around it.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myProc' AND type = 'P')
DROP PROCEDURE myProc
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewText' AND type = 'U')
DROP table NewText
CREATE TABLE NewText
( DocumentID int,
Paragraph1 ntext,
)
GO
CREATE PROCEDURE myProc
AS
declare @.a int
declare @.b nvarchar(3000)
declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a
update NewText set Paragraph1 = cast(Paragraph1 as nvarchar) + @.b where DocumentID = @.a
else
insert into NewText(DocumentID,Paragraph1) values (@.a,@.b)
set @.c=@.a
FETCH NEXT FROM myCursor into @.a,@.b
END
CLOSE myCursor
DEALLOCATE myCursor
select * from NewText
go
--ToCall your procedure:
execute myProc

|||

Hi Mario. Thanks for script. This is exactly what I am looking for.

I created a new query, pasted in your script, and clicked Execute.

A new table ("NewText") was created with the appropriate columns.

However the data from "Text001" was not copied to "NewText". When I open the "NewText" table, the content of the rows is "null".

As a Newbie to SQL Server, I am wondering if I should be doing something else to call the "myProc" procedure. Do I need to do another step to combine the rows from "Text001" and copy the combined data to "NewText"?

|||

yes moonshadow,

first it is great that you copied the script and started its excecution.

i am sure that it will work, but look what you will have to do:

EITHER: change the data type of SectionText from ntext to nvarchar, and then test. if you are urged to use ntext, then we can figure it out... but as a first step, just for your test, do not use ntext or at least do not use large text in your records under SectionText.

OR change the datatype of DocumentID to int.

look, the Table Text001 is like the following (i created this table upon your specifications):

CREATE TABLE [Text001] (
[DocumentID] [int] NULL ,
[SectionText] [ntext] COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thanks for your patience Mario. Here is what I tried:

1. I tried to change the data type of SectionText from ntext to nvarchar but it would only allow nvarchar(50) or nvarchar(max). I chose nvarchar(max) since the SectionText contents are likely be much more than 50 characters. NewText table was created but still empty.

2. I changed the DocumentID to int with the same result as before.

What next?

|||

Mario:To simplify and make it more concrete for working with your query, I created a new database called “Practice”

In that database I ran this query based on your example to create a table called “Text001”:

CREATE TABLE [Text001] (

[DocumentID] [int] NULL ,

[SectionNo] [int] NULL ,

[SectionText] [ntext]

COLLATE SQL_1xCompat_CP850_CI_AS NULL )

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I put data into five rows of the table Text001 as follows:

DocumentID

SectionNo

SectionText

1

1

Blue

1

2

Red

1

3

Green

2

1

White

2

2

Black

I ran the initial query that you provided which was “executed successfully”

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myProc' AND type = 'P')
DROP PROCEDURE myProc
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewText' AND type = 'U')
DROP table NewText
CREATE TABLE NewText
( DocumentID int,
Paragraph1 ntext,
)
GO
CREATE PROCEDURE myProc
AS
declare @.a int
declare @.b nvarchar(3000)
declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a
update NewText set Paragraph1 = cast(Paragraph1 as nvarchar) + @.b where DocumentID = @.a
else
insert into NewText(DocumentID,Paragraph1) values (@.a,@.b)
set @.c=@.a
FETCH NEXT FROM myCursor into @.a,@.b
END
CLOSE myCursor
DEALLOCATE myCursor
select * from NewText
go

The NewText table that was created looked like this:

DocumentID

Paragraph1

Null

Null

I think the NewText Table should have looked like this:

DocumentID

Paragraph1

1

Blue

Red

Green

2

White

Black

I am learning alot from working with this and am thankful for your help.

|||

moonshadow!can you try this please:

add the following, IN RED,

...

declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a

...

test it, cos it seems it is not entering the loop, if there was no error, try to update the following line:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

also, replace all field datatypes in tables Text001, and NewText to nvarchar (i.e: do not use ntext)

...CREATE TABLE NewText
( DocumentID int,
Paragraph1 nvarchar(3000), or max
)...

|||

Mario:

1. I changed all "ntext" datatypes in tables Text001, and NewText to "nvarchar(max)"

2. I copied and pasted this

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

as you suggested and got this error message:

Msg 128, Level 15, State 1, Procedure myProc, Line 14 The name "ERROR!" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

3. I also tried to update the following line as you suggested:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

but got the same error message.

|||ok moon shadow, instead of "ERROR!" just put anything, an insert statement, or a Print 1, just to check if the fetching is occuring with/without errors, just further troubleshooting|||

Mario: I tried numerous things without luck until I typed in a constant expression without the quotations:

IF @.@.FETCH_STATUS <> 0

PRINT 10

The command completed successfully but the content of the "NewText" table was still Null.

I figured 10 (or any number) is a constant expression as per the error message and should be appropriate but may not have served your purpose to see if the fetching is occurring. Any other thoughts on how to proceed?

|||

try to insert a record instead of PRINT, like the following:

IF @.@.FETCH_STATUS <> 0

insert into NewText(DocumentID,Paragraph1) values (1,'Error')

and check the NewText table

good luck

|||

Mario:

I used your new script and the "query executed successfully" but the content of the NewText table was still "null"

|||

moonshadow! run the following:

execute myProc

and then check the result in NewTable

|||

Mario: Yes!! The NewText table is now filled with the combined data. I told you I was a Newbie.

Perhaps you can help me with one refinement. The data in the NewNext table SectionText column is run together (ie., "blueredgreen") which will not work too well with the paragraphs in my original database. Is there a way to automatically format the new data fields (in NewText) so that each row of the original table (Text001) remains on its own line with a space between paragraphs when it is read in an application? Such as this:

blue

red

green

Thanks for your patience and knowledge in getting this far.

|||

Great MoonShadow!!!

now you've got the concept, you can expand it as you like...

regarding your concern, you can add a carriage return while filling each paragraph, i.e: add what is in red to the sql script:

...

update NewText set Paragraph1 = Paragraph1 + char(13)+char(10) + @.b where DocumentID = @.a
...

good luck

Combining tables from different databases

Hi.

I'm currently working on a project which involves the creation of a
web page which reports selected data to customers from two back-end
systems. I use ASP script language on IIS, the server is MS SQL 2000.
Now I'm struggling with combining two tables from the different
databases. I'm sure it's simple enough, but I'm a little short on the
SQL expertise.

I've got two databases, db1 and db2, and then two tables, db1.t1 and
db2.t2. I need to combine these two tables (both tables have a
matching key field) to make a list of all items from db1.t1, and those
who correspond from db2.t2.

I can list all items from db1.t1, but I can't seem to get the db2.t2
joined in.
Can anybody help me with the syntax for this, please ? Help !

Answers, hints & tips greatly appreciated.
Thanks in advance !
KennethHi

You will need three part naming to do this

Use DB1 -- Connected to DB1!

SELECT t.Fld, s.Fld
FROM t1 t JOIN db2..t2 s ON t.Fld = s.Fld

John

"Kenneth Fosse" <kennethfosse@.hotmail.com> wrote in message
news:a4092994.0310110658.42c8abc7@.posting.google.c om...
> Hi.
> I'm currently working on a project which involves the creation of a
> web page which reports selected data to customers from two back-end
> systems. I use ASP script language on IIS, the server is MS SQL 2000.
> Now I'm struggling with combining two tables from the different
> databases. I'm sure it's simple enough, but I'm a little short on the
> SQL expertise.
> I've got two databases, db1 and db2, and then two tables, db1.t1 and
> db2.t2. I need to combine these two tables (both tables have a
> matching key field) to make a list of all items from db1.t1, and those
> who correspond from db2.t2.
> I can list all items from db1.t1, but I can't seem to get the db2.t2
> joined in.
> Can anybody help me with the syntax for this, please ? Help !
> Answers, hints & tips greatly appreciated.
> Thanks in advance !
> Kenneth|||You need to add it as a linked server then use the fully quaklified name
server.database.ownername.tablename

Look up sp_addlinkedserver in BOL

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining 3 SQL statements

Hey all. Ive got a big problem with an sql statement Im working on.

There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.

Therefore, after the query is executed, you should have a result set
something like this

Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------

etc.

Hope this makes sense. ThanksTry this one here:

Select
header_id,
header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers
INNER JOIN
line
ON line.header_id = header.header_id

HTH, jens Suessmeyer.|||Jens (Jens@.sqlserver2005.de) writes:

> Try this one here:
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...

--
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|||You are right, thats not even better that was missing in my example.|||Is there a way to say

SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using

SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),

SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)

THanks for the help guys :)

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613
In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613
|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John
|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.
|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.
sqlsql

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?
'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.

There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.

-Dave

Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
>
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
>
If not, what is the best way to accomplish this?
>
Thanks,
Rodjk #613
>

--
-Dave Markle

http://www.markleconsulting.com/blog|||Dave Markle wrote:

Quote:

Originally Posted by

In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
>
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
>
-Dave
>
Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613

>
>
--
-Dave Markle
>
http://www.markleconsulting.com/blog

Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.

My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.

I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Thanks
Rodjk #613|||Rodjk #613 wrote:

Quote:

Originally Posted by

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Yes.

Imagine database A has order no 1, being shipped to customer no 1.
Database B also has an order no 1, being shipped to customer no 1.

But in database A customer no 1 is Mr. Black, in B it is Mr. White.

/jim|||There are tools to compare the data in the two databases (Like Red

Quote:

Originally Posted by

Gate's SQL Data Compare), but AFAIK nothing to combine the data.

There is a tool to *compare* data - SQL Server Comparison Tool.

Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.

If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
> > Hello,
> > I am working with a database that is in use in several locations.
> > The structure of the databases are identical, but the data is
> > different.
> > I am tasked with combining the data into one large database.
> > (It is SQL 2000)
> >
> > I have .bak files for each location.
> > Is there any way to restore a backup into a database, combining the
> > data?
> >
> > If not, what is the best way to accomplish this?
> >
> > Thanks,
> > Rodjk #613
> >
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

combine OLTP with Star design

Hi,

We are working with AS for some time. We are using OWC as front end. I would like to display in the pivottable attributes of the dimension that are coming from the OLTP. For instance. I have a pivottable sales, customer by time. Next to the customer name I would like to have a column with his phone number and another with his address. Is something like that possible? [I am not concerned about the roll up - the information should only be displayed at leaf level]

To complicate the scenario, I would like to have an additional column called projection. Where the user can edit/add yearly projections for that customer. Any idea how to implement something like that?

Raphael

There are two problems you should recognize in here.

One is how to define your Analysis Services cube to include addtional information about customer phone nuber and address. For this, take a look at AdventureWorks sample project installed as part of SQL Server setup. Take a look at the Customer dimension and see how Phone and other attributes defined there.

Second, is the problem on how to display customer information in your application. BI Dev studio is using OWC as well. You can drag Customer dimension on Rows in cube browser. Drill down to the lowest level (Customer). Now right click on any member on the lowest level and select "Show properties in the report->Select All properties" and you will see all of the Customer's properties.

To be able to modify AS data directly in your application, it should support "writeback" feature. Basically it needs to know how to submit changes back to Analysis Serivices. Take a look if OWC supports writing data back to Analysis Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

COM DLL containing UDFs works fine with as2k but gives "function does not exist" error

Hi,

I have a legacy COM dll that contains some UDFs that we want to use with our 2005 cubes. Against an as2k cube I can get this working using the Use Library statement, but as this has been deprecated in 2005 I can't use this method. I have added the dll as an assembly in both the server and the database but I cannot access the functions within it. I just recieve a "function does not exist error".

Does anyone have any ideas?

regards

Colin

I remember COM UDF's are turned off by default.

Start SQL Server Surface Area Configuration by going through Start menu ->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.

Click on the Sufrace Area Configuration for Features. Under Analysis Services the last node is User-Defined Functions. Change that option and see if your UDF works. (You might need to restart the server.)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Unfortunately it doesn't appear to have made a difference., it still cannot see the function. I guess I will just need to rewrite in .NET.

regards

Colin

columns_updated compatibility between sql2000 and sql 2005

Hi,
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Color Property Formatting not working correctly

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

I wish I could help you, however I cannot.

I can however state with confidence that posting 7 times in roughly 1 hour about the same thing is not the best way to get the attention of those who can.

Just my

|||Yea I can't help you either. . .|||

I agree with the sentiment in the above posts - spamming a forum is not the best way to get an answer.

What would also help is if you posted the expression you are using - without it we are just guessing.

Color Property Formatting not working

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

Thanks in advance

If I am not wrong, matrix conditional formatting is applied by default at the details. You may need to do scope it at the right level, e.g.; =Iif(SUM(<field name, "group name")>0, "Red", "Blue"). Also, download your deployed report definition from the Report Manager (General report properties) and make sure the expression is there.