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

Thursday, March 29, 2012

Duplicate table structures to a new database

Hi all expert,

Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes

Many thanks for the help.

regards,

Use Enterprise Manager's Generate Script Feature, and execute the Generated SQL on your target server.

|||

you can also check Database Publishing Wizard.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Madhu

|||

I want to done this using T-SQL. So what is the script it can be?

|||

There isn't a built-in t-sql command to script However, you can use sp_oa* and smo/dmo. You can check google for archive script.

As to just creating a new table based on the old one, you can just do this:

select top 0 *

into new_table

from old_table

Note that this method does not create constraints/dependencies on the new table.

|||

You can run a trace with profiler to see what Management Studio does to create the script, is a bit more than i expected, but if you really want to you should be able to replicate that on your own.

|||Manivannan gave you the answer. Use the Generate Script feature, it will make the script for you, and then you can re-use that script.

|||

Yes I know there is a Generate Script functions. But I want to schedule it and run on every month, therefore a pre-generated script must be prepared so that it can auto run on every month.

So, can this be done? Thanks a lot for the help.

regards

sql

Thursday, March 22, 2012

duplicate entry in a primary key field

Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubid int Unchecked (primary key)
pub char(1) Unchecked
publ char(1) Unchecked
pubcode char(2) Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanksAlex - you could probably do with a brush up on relational database design & theory.
http://r937.com/relational.html

1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.|||You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.|||there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?|||so with it is it okey if i will not make both a primary key?is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

in most many-to-many relationships, this would be an error|||so what's the best way i could do it?

thanks|||create a composite primary key on both columns|||You have a composite primary key - this is one primary key comproised of 2+ columns.

i.e.
CREATE TABLE MyTable
(
pubid int
, pub char(1)
, publ char(1)
, pubcode char(2)
, CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
)Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.

Monday, March 19, 2012

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?
sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?
|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

Wednesday, February 15, 2012

DTS to import data

Hi All,
I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?I think you'll need to get the provider of the Excel file to at least include the field even if it doesn't have any data. Then, if in your destination table you can specify that columns allow NULL values, when you receive a file that doesn't have any data for a particular column in your destination table, it won't matter.
You could also set a default value on those columns. Are you working with a native Excel file or a CSV file?

Lempster