Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
Sue
If you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
sqlsql

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
--
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

Converse to BulkLoad...

I've an Annotated Schema file (XSD) which describes how data is laid out in
multiple tables in SQL Server.
I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.
Question is, how do I use the same XSD to then read that data from the
database?
Thanks for your time.
Daniel.Daniel,
Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).
Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
> I've an Annotated Schema file (XSD) which describes how data is laid out
> in multiple tables in SQL Server.
> I've a .Net application that creates an instance of the BulkLoad COM
> object, and calls the Execute method, populating my tables using the XSD
> with my XML data.
> Question is, how do I use the same XSD to then read that data from the
> database?
> Thanks for your time.
> Daniel.
>|||Thanks for your prompt reply.
The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.
In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...
As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...
What do you think?
Thanks,
Dan.
"Nicholas Paldino [.NET/C# MVP]" <mvp@.spam.guard.caspershouse.com> wrote in
message news:OR0cSHxvHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> Well, you don't really need the XSD to read the data from the database.
> You can just read the data from the field when you perform a select. You
> can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
> that you use a FOR XML clause, or that you select one column, one row that
> returns text/ntext/xml data).
> Because you used an XSD to validate the data going in, you can assume
> that the field only contains data which conforms to that XSD. You don't
> have to worry about validating on the way out.
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@.spam.guard.caspershouse.com
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
>

Constraint/identity which allows duplicate null fields

hi,

I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?

Thank you.

Make your value a foreign key constraint you can take the IDENTITY property of another table make sure your table is UNION compatible, create an index on another column in the same table and add that column in it through the new feature called INDEX Column include turn on the IGNORE_DUP_KEY option and you have a duplicate proof column. Now let me explain it UNION is a SET operator that performs implicit distinct by eliminating duplicates, and by adding it to an index it can also use the physical IGNORE_DUP_KEY which also eliminates duplicates and your column can get the benefits of an index without actually being the index. Run a search for UNION,IGNORE_DUP_KEY and INDEX COLUMN include in the BOL(books online). I am assuming you know you have to spend time with Management Studio to create this, post again if you still have question. Hope this helps.|||

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

|||

ndinakar:

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

That will do what IGNORE_DUP_KEY will do but a UNION will also eliminate duplicates in a Query of more than one table, the main difference between UNION and UNION ALL. It is the reason UNION comes with more restriction than UNION ALL.

|||

I am confused why we need a UNION, foreign key constraint, INDEX COLUMN etc in this scenario. IGNORE_DUP_KEY will work if the index has already been created with that option.

|||

(My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question )

This was the original poster's need I just showed all can be achived with a combination of SET algebra and the physical. UNION will eliminate duplicates in a query with another column that will introduce duplicates in the result and it comes with compatibility requirement in the table definition, you are checking for duplicates only during insert. Index column include will let that column to be included in a Unique index which require NOT NULL by default, so use the IGNORE_DUP_KEY option and SQL Server just toss the duplicates and continue any insert because ADO.NET does more than one insert. One more thing foriegn key constraints are nullable by ANSI SQL definition.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

hi,

thanks all of you for your assistance. I still think your solution is far too complicated - there must be some easier way? Why I don't do a check before INSERT - it's because there are about 10 stored procs in my database which work with this particular field and it would be so much easier to do this by some kind of constraint (or even trigger?!)...

|||

EDIT

The quick solution is Unique Constraint because it allows NULLs and the other easy option is through the index column include in a Unique index, you just include it and use the IGNORE_DUP_KEY option, it is not complicated. UNION is complicated to set up if it was not included during the database design. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx