Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Tuesday, March 27, 2012

Export Database Structure (No Data)

Is there a transact SQL statement that can be used to only export the database structure? I'm looking to schedule a nightly job to export the structure.
DTS has a task that will do just that.
Cheers,
Greg Jackson
PDX, Oregon
|||And you can create and schedule a job that uses dtsrun.exe to run the =
DTS package whenever you want.
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:uVBZPOjUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> DTS has a task that will do just that.
>=20
>=20
> Cheers,
>=20
> Greg Jackson
> PDX, Oregon
>=20
>

Export Database Structure (No Data)

Is there a transact SQL statement that can be used to only export the databa
se structure? I'm looking to schedule a nightly job to export the structure
.DTS has a task that will do just that.
Cheers,
Greg Jackson
PDX, Oregon|||And you can create and schedule a job that uses dtsrun.exe to run the =
DTS package whenever you want.
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:uVBZPOjUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> DTS has a task that will do just that.
>=20
>=20
> Cheers,
>=20
> Greg Jackson
> PDX, Oregon
>=20
>

export database structure

Hi,
is there a way of exporting database structure not the data in it?

Thanks."Xerxes" <mfkashani@.gmail.comwrote in message
news:1171495938.996070.298470@.h3g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Hi,
is there a way of exporting database structure not the data in it?
>
Thanks.
>


Right click on DB --All Tasks --Generate SQL Script then choose
appropriate options.

Or

Restore a copy of the database, truncate all tables, make a backup of the
now empty database and use that backup as your db structure "export".

Friday, March 23, 2012

Export Cube Structure

Hi all !!
I would like to export a cube from a server to another. I don't need to
export any data, just the structure, fact table and some DTS packages. Any
idea ?
I work with SQL Server 2000 Standard Edition.
Thanks in advance. IBON
> I would like to export a cube from a server to another. I don't need to
> export any data, just the structure, fact table and some DTS packages. Any
> idea ?
> I work with SQL Server 2000 Standard Edition.
>
In Analysys Manager 2000, right-click on the database you want to move to
another server and select Copy. Copy the db on the same server (source) into
a new db. Copy option copies meta data only. Now you can backup the new db
on the source server, copy the backup and restore it on the destination
server.
DTS packages can be saved to files with Enterprise Manager. Then you can
copy them and open them on the destination server and save them to msdb of
the destination server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
|||Thanks Dejan !!
It works well !!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > escribi
en el mensaje news:O7N91Rb8GHA.568@.TK2MSFTNGP05.phx.gbl...
> In Analysys Manager 2000, right-click on the database you want to move to
> another server and select Copy. Copy the db on the same server (source)
> into a new db. Copy option copies meta data only. Now you can backup the
> new db on the source server, copy the backup and restore it on the
> destination server.
> DTS packages can be saved to files with Enterprise Manager. Then you can
> copy them and open them on the destination server and save them to msdb of
> the destination server.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
>

Export Cube Structure

Hi all !!
I would like to export a cube from a server to another. I don't need to
export any data, just the structure, fact table and some DTS packages. Any
idea '
I work with SQL Server 2000 Standard Edition.
Thanks in advance. IBON> I would like to export a cube from a server to another. I don't need to
> export any data, just the structure, fact table and some DTS packages. Any
> idea '
> I work with SQL Server 2000 Standard Edition.
>
In Analysys Manager 2000, right-click on the database you want to move to
another server and select Copy. Copy the db on the same server (source) into
a new db. Copy option copies meta data only. Now you can backup the new db
on the source server, copy the backup and restore it on the destination
server.
DTS packages can be saved to files with Enterprise Manager. Then you can
copy them and open them on the destination server and save them to msdb of
the destination server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Thanks Dejan !!
It works well !!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escribi
en el mensaje news:O7N91Rb8GHA.568@.TK2MSFTNGP05.phx.gbl...
> In Analysys Manager 2000, right-click on the database you want to move to
> another server and select Copy. Copy the db on the same server (source)
> into a new db. Copy option copies meta data only. Now you can backup the
> new db on the source server, copy the backup and restore it on the
> destination server.
> DTS packages can be saved to files with Enterprise Manager. Then you can
> copy them and open them on the destination server and save them to msdb of
> the destination server.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
>sql

Wednesday, March 21, 2012

Export a table (datas and structure)

Hi,
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
Stan
On Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain the
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.com

Export a table (datas and structure)

Hi,
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
StanOn Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain the
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.com

Export a table (datas and structure)

Hi,
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
StanOn Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain th
e
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.comsql

Wednesday, March 7, 2012

Expand table rows based on two column ranges

Hi,
I've got a table I'm working with in SQL (through a SAS interface) with the following structure: There is a primary key and for each primary key there are two columns that together constitute a range of values for the primary key. The first column is the start value, the second is the edn value. Here's a quick example:

prim_key startvalue endvalue
1 1023 1026
2 1028 1033
3 1036 1038

What I want to do is expand the table so that each value associated with a primary key is contained on its own row as shown in the table below. I've tried worked with sequences, but this doesn't appear to be the way to go. Does anyone have any suggestions on how I might accomplish this task?

prim_key value
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.

You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:

select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;

You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:

select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?

Originally posted by andrewst
What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.

You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:

select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;

You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:

select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?please, no loops, okay?

you can generate a million integers from just the integers 0 though 9 like this:create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);

select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers uand then left join to your tables as desired|||Pipelined function may come in handy here...

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TYPE numbers_table AS TABLE OF NUMBER (10);
2 /

Type created.

SQL> CREATE OR REPLACE FUNCTION expand (
2 start_value IN NUMBER,
3 end_value IN NUMBER)
4 RETURN numbers_table PIPELINED
5 AS
6 BEGIN
7 FOR i IN start_value..end_value LOOP
8 pipe row (i);
9 END LOOP;
10 RETURN;
11 END;
12 /

Function created.

SQL> CREATE TABLE table_name (
2 prim_key NUMBER,
3 startvalue NUMBER,
4 endvalue NUMBER);

Table created.

SQL> INSERT INTO table_name VALUES (1, 1023, 1026);

1 row created.

SQL> INSERT INTO table_name VALUES (2, 1028, 1033);

1 row created.

SQL> INSERT INTO table_name VALUES (3, 1036, 1038);

1 row created.

SQL> SELECT prim_key, column_value
2 FROM table_name, TABLE (expand (startvalue, endvalue));

PRIM_KEY COLUMN_VALUE
---- ----
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038

13 rows selected.

SQL>

Sunday, February 26, 2012

EXISTS problem with Delete

I have three tables (SHADES, CHANGES, AND SHADES_SUC)
Shades primary structure: Shade_ID
Changes primary structure: Old_Shade_ID
Shades_SUC primary structure Shade_ID
A record is written to Changes when a change is made to the Shades table and
some of these changes will cause a scenario where the Shades_SUC table will
hold orphaned children to the shade_ID
My delete statement is as follows:
DELETE
FROM SHADE
WHERE EXISTS
(
SELECT *
FROM CHANGES, SHADE
WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
AND CHANGES.OLD_SHADE_ID NOT IN
(SELECT SHADE_ID FROM SHADE_SUC)
)
If I highlight simply the select portion of Select * from Changes, shade...
I receive a return of 8 records - which is correct. However when I run the
entire Delete command the procedure deletes every record in the Shade table
-
I have tried numerous things - can anyone provide any direction on what I ma
y
be doing wrong here?
ThxSorry - I typed incorrectly - To Confirm... We are looking for any shades
that do not have any children in the Shades_SUC table - so that we can delet
e
them.
"Dan" wrote:

> I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table a
nd
> some of these changes will cause a scenario where the Shades_SUC table wil
l
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes, shade..
.
> I receive a return of 8 records - which is correct. However when I run th
e
> entire Delete command the procedure deletes every record in the Shade tabl
e -
> I have tried numerous things - can anyone provide any direction on what I
may
> be doing wrong here?
> Thx|||Thats correct you have to link the outer and the inner query to do this:
DELETE
FROM SHADE S
WHERE EXISTS
(
SELECT *
FROM CHANGES C
WHERE C.OLD_SHADE_ID = S.SHADE_ID
AND C.OLD_SHADE_ID NOT IN
(SELECT SHADE_ID FROM SHADE_SUC)
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dan" <Dan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A9B07B01-E573-49D9-95C0-637F01724BCC@.microsoft.com...
>I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table
> and
> some of these changes will cause a scenario where the Shades_SUC table
> will
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes,
> shade...
> I receive a return of 8 records - which is correct. However when I run
> the
> entire Delete command the procedure deletes every record in the Shade
> table -
> I have tried numerous things - can anyone provide any direction on what I
> may
> be doing wrong here?
> Thx|||Try,
DELETE SHADE
WHERE EXISTS
(
SELECT
*
FROM
CHANGES
left join
SHADE_SUC
on CHANGES.OLD_SHADE_ID = SHADE_SUC.SHADE_ID
WHERE
CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
and SHADE_SUC.SHADE_ID is null
);
AMB
"Dan" wrote:

> I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table a
nd
> some of these changes will cause a scenario where the Shades_SUC table wil
l
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes, shade..
.
> I receive a return of 8 records - which is correct. However when I run th
e
> entire Delete command the procedure deletes every record in the Shade tabl
e -
> I have tried numerous things - can anyone provide any direction on what I
may
> be doing wrong here?
> Thx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
This soudns like you need to re-design the schema with a proper history
table and ON DELETE CASCADE actions.|||Receive the following:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'S'. -
Reveiwed but did not see anything obvious
"Jens Sü?meyer" wrote:

> Thats correct you have to link the outer and the inner query to do this:
>
> DELETE
> FROM SHADE S
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES C
> WHERE C.OLD_SHADE_ID = S.SHADE_ID
> AND C.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Dan" <Dan@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A9B07B01-E573-49D9-95C0-637F01724BCC@.microsoft.com...
>
>|||No table alias in DELETE. Use the full name for S instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:63E6DC40-4883-4479-BAD8-78DC5DAA550B@.microsoft.com...
> Receive the following:
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'S'. -
> Reveiwed but did not see anything obvious
> "Jens Smeyer" wrote:
>