Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sdiwi
Starting Member
27 Posts |
 Posted - 2005-08-04 : 12:19:25
|
Hello.At the moment i wonder if there is a system stored procedure to copy/clone a table. I just browsed the web, but i did not find anything that pleased me.I do not want to read out the systables and build it my own. There has to be some kind of procedure to do this...I need a proc that either copies the table or returns the "CREATE TABLE" sql script of my table, so i can easily create a slight modification of the origin table (i wonder if my last sentence makes any sense... )Hope you can help me out,thanks, peace,sdiwi. |
|
|
highlander
Starting Member
1 Post |
Posted - 2005-08-04 : 12:32:16
|
| select * into newtable from oldtable where 1 = 2 will do the job for you. |
 |
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-05 : 03:29:46
|
| omg.first i thought you posted bullshit. i didn't even want to try your query, because i was sure it would not work. but hell. it did! and that really helps me out. thanx! i never saw anything like that, and i thought "select into" was postgre stuff... you will never stp learning...but however, constraints and stuff still have to be created manually.so it's an "almost-solution" for me.but i'm fine with it right now.thanks,peace, sdiwi. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-05 : 04:40:12
|
| so what's wrong with Query Analyzer's "script object as the create"??Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 04:48:39
|
| Or Enterprise Manager (Right click table : All tasks : Generate SQL) - that allows you to be selective about what "associated stuff" you script for the table:Create (obviously!)Drop OriginalExtended Properties (Column / Table descriptions etc.)IndexesFull text indexesTriggersPK / FK / Defaults / ConstraintsPermissionsWill just need a bit of Find&Replace to convert it to be approrpiate for the NewTable Name ...Kristen |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-05 : 05:01:06
|
| i think, you didn't get the point:i do not want to use any tool (no enterprise manager either), i want to copy, alter, move etc. tables via tsql scripts. no other tools. 100% automated.@spirit1 - sorry, but i don't understand what you mean. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 05:10:08
|
| Spirits solution is similar to Enterprise Manager, but run from Query Analyser instead."i think, you didn't get the point"Well I did, up until this point"i can easily create a slight modification of the origin table"which I read to be a change made manually, perhaps you mean making a change programatically? (a bit fraught working with a Text Output of a Create Table statement I would think, but ... how would you handle Find&Replace of Foreign Keys and the like? Object names for KFs, Indexes, Constraints? Seems fairly tricky to me!)Perhaps knowing the problem you are trying to solve would enable us to think of a possible solution.You could use SQL Profiler to "snif" what calls E.M and Q.A. are making to SQL to generate their scripts - might be easy enough to duplicate that yourself.There is a script over in the Scripts forum, but I think it will pull up short of what you want:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53007Kristen |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-05 : 08:28:22
|
a short description of my problem:i want to redesign the (pretty much) messed up database structure of my company.an important change will be, that no rows can be (physically) deleted from the tables anymore. there will be a deleteFlag or something.to make all the old tools work with the new table (because the names of the cols are changing too), i (temporarily) want to create a view that gets the old name of the table and would look something like this:CREATE VIEW old_tablename ASSELECT a,b,c FROM old_table_with_new_name WHERE deleted = 0 a very simple fix. and i dont need to reprogram 100s of tools.i need to do this with a few hundred tables, so i will do everything to avoid doing it manually.i have a sql-query that creates a backup of the source table, inserts 2 columns, and creates the view (planned to create some sps)...doing it manually will simply take too long.quote:
how would you handle Find&Replace of Foreign Keys and the like? Object names for KFs, Indexes, Constraints? Seems fairly tricky to me!
oh yes. that's definetly tricky. and that's also the reason why i asked for a system stored procedure to do this work.but i don't need it anymore, because i create a backup with the "select into "-statement. my backup table does not need any keys. if there will fail something i have to redefine the keys and constraints. but i can live with that.but actually. i should not worry anymore, because in half an hour i'm in V-A-C-A-T-I-O-N   !farewell,peace (it's more important than you might think...),sdiwi. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 08:36:30
|
| Am I missing something?Write a script to generate SQL for:1) Rename myTableName to myTableName_OLD2) ALTER TABLE myTableName_OLD to add additional columns (and maybe set initial values)3) CREATE VIEW myTableName AS SELECT * FROM myTableName_OLD WHERE deleted = 0Shouldn't need any mechanically generated "Create Table" stuff at all, should it?I think that renaming a table keeps the FKs intact.It will not rename the constraints - so physically creating a "version 2" table to replace the VIEW will give a problem with name clashes on that stuff. But thats probably not going to be a real-world problemKristen |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-05 : 09:04:32
|
| @kristenyou just repeated what i did. maybe i cannot express myself properly in english.as i said, i solved my problem. like i (and you) posted above.i just wanted to solve it in another way in the beginning.as you (and i) said, renaming preserves the keys. and cloning the table and data structure is enough for my purposes.sorry.have to go.cant talk no more...mb later?!`? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 09:20:46
|
| OK, that's cool. I thought you said what I had written, but your original solution seems to be for a different problem, so I thought I'd chime in anyway!Glad you've got it sorted, have a good vacationKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-08-05 : 09:51:59
|
quote:
Originally posted by sdiwi
a short description of my problem:
That would have been nice in your first postquote:
i want to redesign the (pretty much) messed up database structure of my company.
I would use ERWin to document the new structuresquote:
an important change will be, that no rows can be (physically) deleted from the tables anymore. there will be a deleteFlag or something.
Don't like that...better to have a second table that stores all of the deleted data..and use a trigger to move themquote:
to make all the old tools work with the new table (because the names of the cols are changing too), i (temporarily) want to create a view that gets the old name of the table and would look something like this:CREATE VIEW old_tablename ASSELECT a,b,c FROM old_table_with_new_name WHERE deleted = 0
Well if the column names are changing why wouldn't you alias the column nams?SELECT a as x, b AS y, c AS z?quote:
a very simple fix. and i dont need to reprogram 100s of tools.
A man finds his true makeup when stepping in to the abyssquote:
i need to do this with a few hundred tables, so i will do everything to avoid doing it manually.
Make sure you put audit and controls around your automation. See previous answer.quote:
i have a sql-query that creates a backup of the source table, inserts 2 columns, and creates the view (planned to create some sps)...
"Inserts 2 columns"? Are you planning on doing an ALTER of every table?http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.htmlquote:
doing it manually will simply take too long.
I'm a HUGE advocate of not doing anything manually. One thing I won't do is design a database based on an existing structure....I'd stage it, but a migration is not something to automate. MOOquote:
how would you handle Find&Replace of Foreign Keys and the like? Object names for KFs, Indexes, Constraints? Seems fairly tricky to me!
quote:
oh yes. that's definetly tricky. and that's also the reason why i asked for a system stored procedure to do this work.but i don't need it anymore, because i create a backup with the "select into "-statement. my backup table does not need any keys. if there will fail something i have to redefine the keys and constraints. but i can live with that.
If you say so.....and don't invade Russia in the winterquote:
but actually. i should not worry anymore, because in half an hour i'm in V-A-C-A-T-I-O-N !farewell,peace (it's more important than you might think...),sdiwi.
Well then what's with all the hullabalo?A vaction in Germany is what...6 weeks?Ein Prosit...(What the hell does prosit mean? Google can't even translate it)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 10:14:35
|
| Hmmm ... 1) rename all tables, create VIEWs on all the NewNames to expose them under the old names, Add a couple of columns to the NewNameTables.OK so far ....2) Go on holiday half an hour laterNot so OK any more I sure hope you put a default on the [deleted] column so new rows get a zero - otherwise they is going to be NULL :-(Kristen |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-05 : 12:37:27
|
although i don't know if i really want to talk about this topic now anymore, i feel like having to reply...vacation just feels great, kinda chilled now.quote:
Originally posted by X002548
quote:
Originally posted by sdiwi
a short description of my problem:
That would have been nice in your first post
yeah, i know. but i just wanted a ssp to clone a table; or to get it's create query...quote:
quote:
i want to redesign the (pretty much) messed up database structure of my company.
I would use ERWin to document the new structures
Is ERWin a good DB-documentation tool? Useful for DB-Developers? Really?DAMN... I thought it was some kind of SAP-database-something... Maybe you can give me a short description of it's best features. then i might consider translating "Prosit".The only documentation software i have tried was SQL Scribe. It's kinda nice. But not really satisfying...quote:
quote:
an important change will be, that no rows can be (physically) deleted from the tables anymore. there will be a deleteFlag or something.
Don't like that...better to have a second table that stores all of the deleted data..and use a trigger to move them
Hmm... Sounds good, too. I already thought about a solution with 2 tables, but i haven't considered using triggers to move them. You know, i don't really have a lot of experience in developing and databases. I just finished my apprenticeship of 2 years, but i can easily understand and solve logical problems. Therefore (and because my boss probably thinks i'm some kind of genius or sth.  ... ) i now am director of database development in my company (though i didn't really want to - don't like my job).I just start to read about all that DB stuff, because before i just used dbs to quickly store and retrieve data.What i actually wanted to say - i have to check triggers out first. Never required them.quote:
quote:
to make all the old tools work with the new table (because the names of the cols are changing too), i (temporarily) want to create a view that gets the old name of the table and would look something like this:CREATE VIEW old_tablename ASSELECT a,b,c FROM old_table_with_new_name WHERE deleted = 0
Well if the column names are changing why wouldn't you alias the column nams?SELECT a as x, b AS y, c AS z?
sure. just forgot it ... otherwise it wouldn't work.quote:
quote:
a very simple fix. and i dont need to reprogram 100s of tools.
A man finds his true makeup when stepping in to the abyss
altough i don't know if i really understand this proverb(if it is one...), i think you want to say, that i should face the problems, and solve them completely instead of a cheap workaround.if it's that, i can allay you.the whole thing with the views was just intended to be a temporary change. i want to completely redesign the table structures (you can't imagine the mess), but i have to keep the systems running - so i can't just throw the old stuff away.quote:
quote:
i need to do this with a few hundred tables, so i will do everything to avoid doing it manually.
Make sure you put audit and controls around your automation. See previous answer.
I'll try.quote:
quote:
i have a sql-query that creates a backup of the source table, inserts 2 columns, and creates the view (planned to create some sps)...
"Inserts 2 columns"? Are you planning on doing an ALTER of every table?http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
i just flew over the article and read the summary. space is no problem . and i just alter the temp table...the summary of your article said:"It can cause the size of the row to exceed the 8060 byte limit although the structure would appear much less than that."and now i know where that error comes from. occurs in several tables...good boy, article.quote:
quote:
doing it manually will simply take too long.
I'm a HUGE advocate of not doing anything manually. One thing I won't do is design a database based on an existing structure....I'd stage it, but a migration is not something to automate. MOO
as i already mentioned, the alteration of the tables is not the remodeling process. just a temp solution.quote:
quote:
how would you handle Find&Replace of Foreign Keys and the like? Object names for KFs, Indexes, Constraints? Seems fairly tricky to me!
quote:
oh yes. that's definetly tricky. and that's also the reason why i asked for a system stored procedure to do this work.but i don't need it anymore, because i create a backup with the "select into "-statement. my backup table does not need any keys. if there will fail something i have to redefine the keys and constraints. but i can live with that.
If you say so.....and don't invade Russia in the winter
hmm. now i don't know what you want to say me with that.i'm really eager to hear...quote:
quote:
but actually. i should not worry anymore, because in half an hour i'm in V-A-C-A-T-I-O-N !farewell,peace (it's more important than you might think...),sdiwi.
Well then what's with all the hullabalo?
well, i didn't really cause it. i just answered your questions. i just asked for a ssp yesterday.quote:
A vaction in Germany is what...6 weeks?
nah. if you're lucky you have 6 weeks a year (i know, it's kinda much. but in my eyes still not enough).but i got 4 weeks this year.now i'm off for 2 weeks...quote:
Ein Prosit...(What the hell does prosit mean? Google can't even translate it)
Prosit simply means "cheers!". i think it has become that famous because it's origin is in bavaria (oktoberfest-stuff). actually only tourists and old ppl still use it here .@Kristensure!i deserved my holiday *GGG*...enough words - time for real vacation!P R O S I T ! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-05 : 13:00:43
|
octoberfest.... aaaaahhhhh.... now you're talking  Go with the flow & have fun! Else fight the flow |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Copy A Table - System Stored Procedure ?