February 9, 2008 at 9:27 am
:discuss: I have installed SQL server 2005 and this is new for me ( i am not familiar with) and i tried to Generate SQL Script just to Import data between my temporay and the permanent Data Because I have a practice to do this in SQL server 2000 databases during when i update databases.
How to generate Script and export data in SQL Server 2000
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
THE FIRST STEP HOW TO GENERATE SQL SCRIPT.
1.Restoring our temporary data—and right click on it.
2.Select all tasks then 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 close. That’s it.
Now Import or Export the Data
1. Right click the permanent Data, select Tasks, and then click Export Data
2. 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.
Please guys I need help? how could I perform the same steps in SQL Server 2005 ( Management Studio) databases.
Thank you very much
February 9, 2008 at 12:50 pm
Using SSMS (SQL Server Management Studio)
Expand databases
1. Right click on database name
2. On drop down menu select Tasks
3. On next drop down menu select Generate Scripts
The Script Wizard will appear just keep selecting and clicking away.
From there I think you will find familiar wording and manage to do what you want to do. If not post again and we can see if we can help you further.
February 10, 2008 at 1:46 am
Hi, bitbucket
Still I am trying to generate Scripts and exporting databases. Right now the problem is, with "Executing Query"
As I know in SQL server 2000, after generated Script of one data, you would execute in Query then that database supposes to be Empty.
So, the problem is after I have Executed the database in new Query, still the databases are there. I mean the tables are not empty. So, how could you Import data from another data , if the database is not Empty?
I hope you have got my Idea
Thanks
February 10, 2008 at 9:04 am
What did you do when you created the database. running the script does not necessarily add data to your tables unless there were insert statements in your script.
Look through the generated script and examine it for INSERT statements.
February 10, 2008 at 10:00 am
Take a look now, I will show you what I am doing for generating Scripts in SQL server 2005
?Right click my data base, select all tasks, and then Generate Scripts
?In the well come dialogue box, then Next, selected my database and Next
?Now there are two potions, the General, Tables and Views.
?Now I have selected under Tables and Views potions ALL TRUE. Except full text indexes and script triggers are FALSE because I don't like them. Then Next.
?On the choose object types, I selected Tables only then Next, again I selected all tables then NEXT.
?On the out put option box, I chose Script to new Query window then Next
?In a script wizard summary, I selected my database, finish
?On the Generate Script progress, all tables are successfully generated. Now I have selected my database from the available database then just EXECUTE.
Now I saw an error message says "Query completed with errors" and this is the Generate Script that I know because I am using this SQL server 2005 for the first time.
Thanks
February 19, 2008 at 3:09 pm
Generate script will generate the scripts for table structure not the data. if you want the data also, use either Import/Export or SSIS for that.
Thanks
February 19, 2008 at 5:12 pm
When you generate the script, you can change the script drop to be true. Then you objects will be re created. The default is false.
February 20, 2008 at 2:41 am
Hi massawa,
After creating the Script for the particular Database, you have to create insert script for Metadata which are the tables you want.
After that, as per my understanding you have to create a new database and use this Script for creating tables,views,storedprocedures etc ....
---
February 20, 2008 at 7:11 am
Thank you very much Guys
Appreciated :satisfied:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply