Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




24 posts

Geek


# 19241 11-Feb-2008 05:06
Send private message



                Hello, guys how are you doing?


          There are steps how to GENERATE SQL SCRIPT in SQL server 2000 
  1. Right click on the temporary data and pointing all tasks
  2. Select Generate SQL Script, in the dialogue box click show all.
  3. under script all objects, check the box of all tables
  4. Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints.
  5. Click General then preview. Now Copy the Script preview, click Ok. Close then cancel.
  6. Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it.
Right now, this data is Empty and ready for Importing data from another database. Meanwhile, I have done the same thing in SQL server 2005 ( Management studio) but after Executed the Query still the data is there. but it should be gone ( tables should be Empty) inorder to import data. so, how could you Import data if your database has full of data?


                                               so, please guys help me?

                                                     thank you very much

 

 


Create new topic
95 posts

Master Geek


  # 109803 11-Feb-2008 09:32
Send private message


Generating scripts is designed to allow you to re-create a database, not destory your current one and just delete the data. For this reason the scripts generated should have IF NOT EXISTS statements to only re-generate tables etc that do not already exist.  In normal use this would prevent you from accidentally killing your source database but still run correctly in an empty database (which is what it was designed for).

Personally, just discussing it scares me but ... If you REALLY want to blow away your source database then turn OFF that option in the generate scripts tool.

An alternative if you want to just blow away just test data is to write a script to delete the data you want to get rid of:

DELETE FROM OrderDetails
DELETE FROM Orders
DELETE FROM Customers

etc.


Dale






24 posts

Geek


  # 109997 11-Feb-2008 22:22
Send private message


                     Hello, signz 



             I think it much better to move on with SQL server 2000 because it is fast, easy to use and saves time. because as i said i am new for SQL server 2005 and i have asked the Question of Generating Script more than 3 websites and i think most people have difficulties with SQL server 2005. so, i have decided to move on with SQL server 2000.
          Is there any download availble for SQL server 2000 SP3?




            THANK YOU VERY MUCH

 
 
 
 


799 posts

Ultimate Geek

Trusted

# 110000 11-Feb-2008 22:37
Send private message

addisu33:
Right now, this data is Empty and ready for Importing data from another database. Meanwhile, I have done the same thing in SQL server 2005 ( Management studio) but after Executed the Query still the data is there. but it should be gone ( tables should be Empty) inorder to import data. so, how could you Import data if your database has full of data?

 



Generate Scripts doesnt copy data from one database to another or one table to another, it is to generate the SQL scripts that you can use to create those database or tables. So, if you want to put data, either you have to backup from the source and restore at the target server. And what you mean by - "after Executed the Query still the data is there." which is being executed and what data is there?

I am really confused with what you are asking
Frown




Regards,
Chaks

Desktop : Intel Quad Core Q9400 2.66GHz - 8GB RAM - 500 GB + 500 GB HDD - NVidia GeForce 9800GT - LG246WH Flatron Display - Windows Server 2008 R2 Enterprise with Hyper-V
Virtual Machine : Powered by Hyper-V and VMWare Workstation
Laptop: HP dv7-3004TX Entertainment Notebook PC | HP Touchsmart tx2 1119au - Windows 7 Ultimate x64
Mac: iMac 21.5" Snow Leopard
Mobile : iPhone 3GS



24 posts

Geek


  # 110028 12-Feb-2008 03:38
Send private message


          Hi, i want you to look carefully for what i am doing?

    Example: - I have two databases
>Temporary data ( ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data ( Accounting)- this is customer database
     
 There are steps how to GENERATE SQL SCRIPT in SQL server 2000
  1. Right click on the temporary data and pointing all tasks
  2. Select Generate SQL Script, in the dialogue box click show all.
  3. under script all objects, check the box of all tables
  4. Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints.
  5. Click General then preview. Now Copy the Script preview, click Ok. Close then cancel.
  6. Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it.
  7. The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data.
                 Now See the tables of your Temporary data one by one it's Empty and Ready for Importing data From your permanent data. tell me if you have more 10 different databases, Each database has around 800 tables just for  15 customers, could you Script each table one by one? I am talking about SQL server 2000 and this is my job and i took this training from my company. and when I Update databses, they told me to use this steps. 

        THANKS
      

       

95 posts

Master Geek


  # 110051 12-Feb-2008 10:12
Send private message


This sounds VERY dangerous to me. 

Personally, I script all changes to databases so that if I need to add (or modify) a table, index, field ... whatever then I just execute the script.  I have 15 clients running across 7 cities and I can assure you that the way you are tackling the problem sounds like a good way to lose data.

I agree that using a generated script should create you a copy of your development scheme which you can then import data into.  I just worry that unless the script you are using to import the data is not doing everything in the right order (due to relationships) and checking as it goes (ie. source and destination record counts on each table) then something is going to get missed out.

As for SQL 2005 Vs SQL 2000 I can't see there is any different to the logic or behavior as long as you are running your generated script on an *empty* database.


DS

799 posts

Ultimate Geek

Trusted

  # 110272 12-Feb-2008 23:20
Send private message

addisu33:
Example: - I have two databases
>Temporary data ( ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data ( Accounting)- this is customer database
There are steps how to GENERATE SQL SCRIPT in SQL server 2000


The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data.

Now See the tables of your Temporary data one by one it's Empty and Ready for Importing data From your permanent data.


So, you mean to say when you do a Backup from Temporary and then Restore them to the Permanent, you lose data in Temporary database?? This should not be the case. I do Backup and Restore here with SQL Server 2005 and I dont see this behaviour. I think there is something wrong. And when you backup fully, they are backed up along with scripts + data, so if you just say Restore From Database and give a new database name, all the tables,stored procedures will be created for you anyhow.




Regards,
Chaks

Desktop : Intel Quad Core Q9400 2.66GHz - 8GB RAM - 500 GB + 500 GB HDD - NVidia GeForce 9800GT - LG246WH Flatron Display - Windows Server 2008 R2 Enterprise with Hyper-V
Virtual Machine : Powered by Hyper-V and VMWare Workstation
Laptop: HP dv7-3004TX Entertainment Notebook PC | HP Touchsmart tx2 1119au - Windows 7 Ultimate x64
Mac: iMac 21.5" Snow Leopard
Mobile : iPhone 3GS



24 posts

Geek


  # 110301 13-Feb-2008 03:30
Send private message



       

               Hello, guys how are you doing?

 

First what I want to say is, my company has been working with this kind of steps almost 12 years and they have developers and designers…….and also have like me Network administrator and familiar with SQL server 2000.

I think it is better to try in your machine. Any how, see it now from the first step what I am doing 

               Example: - I have two databases
>Temporary data (ACCTEMP) – We use for when we change any primary key, or design tables…….etc
>Permanent data (Accounting) - this is customer database

          THIS IS how to GENERATE SQL SCRIPT

1.      Right click on the temporary data and pointing all tasks 2.      Select Generate SQL Script, in the dialogue box click show all. 3.      under script all objects, check the box of all tables 4.      Click options, under table scripting options, check the box of Script indexes, and Script primary keys, FOREGIN keys, defaults, and check constraints. 5.      Click General then preview. Now Copy the Script preview, click Ok. Close then cancel. 6.      Go to tools, SQL Query Analyzer, Paste. Now Execute Query and that's it. 

NOW EXPORT THE   PERMANENT DATA TO THE GENERATED DATA

1. Right click the permanent Data, select Tasks, and then click Export Data2. In the data transformation service, click next, next, choose the destination database then next, next, select all tables and views.3. Uncheck the views, next, next, finish. Now execute the package. And finish. The last thing what I do is, just Back up the temporary data and Restore the permanent data with the Backed up data. And that's it.
                                 

 
 
 
 




24 posts

Geek


  # 110306 13-Feb-2008 03:59
Send private message


 
                    Of course, I understand that what you are saying is true, because I am loosing data but this is the way how we are working. Some times, after I update we see some errors in the customer data then this time, they give me a SCRIPT to run in Query analyzer. 


           I think you have got my Idea



                  thank you very much

799 posts

Ultimate Geek

Trusted

  # 110311 13-Feb-2008 07:25
Send private message

addisu33:

First what I want to say is, my company has been working with this kind of steps almost 12 years and they have developers and designers…….and also have like me Network administrator and familiar with SQL server 2000.



Well, We dont see a point why you are doing this (for the past 12 years!) when you see the data is being lost in the Temporary Database and there is sure some mistake in the process. I have also done these things in Windows 2000 and the data stays there in the Temporary Database and you were asking in SQL Server 2005 and that is the topic of this thread and I told you its not the way and you can just use Backup & Restore option in SQL Server 2005 and its straight forward.

If you dont plan to find whats the problem and say you are doing the same mistake for so many years, you are free to go with it Sealed




Regards,
Chaks

Desktop : Intel Quad Core Q9400 2.66GHz - 8GB RAM - 500 GB + 500 GB HDD - NVidia GeForce 9800GT - LG246WH Flatron Display - Windows Server 2008 R2 Enterprise with Hyper-V
Virtual Machine : Powered by Hyper-V and VMWare Workstation
Laptop: HP dv7-3004TX Entertainment Notebook PC | HP Touchsmart tx2 1119au - Windows 7 Ultimate x64
Mac: iMac 21.5" Snow Leopard
Mobile : iPhone 3GS

Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Air New Zealand uses drones to inspect aircraft
Posted 17-Jun-2019 15:39


TCL Electronics launches its first-ever 8K TV
Posted 17-Jun-2019 15:18


E-scooter share scheme launches in Wellington
Posted 17-Jun-2019 12:34


Anyone can broadcast with Kordia Pop Up TV
Posted 13-Jun-2019 10:51


Volvo and Uber present production vehicle ready for self-driving
Posted 13-Jun-2019 10:47


100,000 customers connected to fibre broadband network through Enable
Posted 13-Jun-2019 10:35


5G uptake even faster than expected
Posted 12-Jun-2019 10:01


Xbox showcases 60 anticipated games
Posted 10-Jun-2019 20:24


Trend Micro Turns Public Hotspots into Secure Networks with WiFi Protection for Mobile Devices
Posted 5-Jun-2019 13:24


Bold UK spinoff for beauty software company Flossie
Posted 2-Jun-2019 14:10


Amazon Introduces Echo Show 5
Posted 1-Jun-2019 15:32


Epson launches new 4K Pro-UHD projector technology
Posted 1-Jun-2019 15:26


Lenovo and Qualcomm unveil first 5G PC called Project Limitless
Posted 28-May-2019 20:23


Intel introduces new 10th Gen Intel Core Processors and Project Athena
Posted 28-May-2019 19:28


Orcon first to trial residential 10Gbps broadband
Posted 28-May-2019 11:20



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.