| Author |
Topic |
|
EugeneLim11
Posting Yak Master
167 Posts |
 Posted - 2008-06-30 : 00:38:30
|
Dear Experts,Hi, a very good day to you! I need your kind assistance with two questions:Question 1: ---------------------I have a table named 'Country', which contains all the country names in a column name 'countryname'. I would like to have a script that will take out the countryname as a SQL script. i.e. I want it to read "Insert into Country (Countryname), values ('countryname') GO" for each countryname so that I can run this sql script to an online server that only allows me to enter SQL scripts. How do I do that? Question 2:--------------------Assuming that I would like to run the same script for all tables in my database:"Insert into TableName (ColumnName1, ColumnName2) values ('columnName1.values', columnName1.values)" Is there any way to return a query or store procedure that would automatically do this, instead of running the method as discussed in Question 1 manually for each table?If not, I have to do it using method 1.  which is slow as I have a lot of data in each of the tables in my development server. Once again, thank you in advance for your guidance. I learnt lots of new stuff here everytime, and i look forward to learning more. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 00:48:05
|
| Didnt understand it fully. Why do you want to insert the values from table into same table? |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-30 : 01:01:07
|
Question 1 :if you have connections to the online Server from your server then create a linked server and insert direct.insert into [online server].[db name].[dbo].Country (Countryname)select Countrynamefrom Country if not, use bcp to export the data out and copy the data over and bcp in again.Question 2 :Check out BOL on DTS or SSIS on exporting / copying data
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-06-30 : 01:44:39
|
| Dear Visakh,I have a database (in my development server). there is another MSSQL server (in the LIVE server). I want the script to get the information (content) in the development server so that I can import the data to the LIVE server. I have no problems creating tables etc. but could not get the content out. :( Khtan,I don't have connection from online server from my local server. :( If I did, I could use the import/ export function and need not do this the hard way. :( I only got access to the sql query window online. :( I will try to google your recommendation. BOL on DTS or SSIS... but frankly, I have no clue on what you are talking about. Please be patient with me as I am a beginner. Hopefully, google will let me understand this better. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-30 : 01:50:44
|
BOL is Books Online, aka SQL Server Help file"I don't have connection from online server from my local server. I only got access to the sql query window online"is your Dev Server & Live Server in anyway connected ? If you can access both the dev & live server from your PC then you should be able to create a linked server on either one of them to the other.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 02:16:07
|
quote:
Originally posted by EugeneLim11
Dear Visakh,I have a database (in my development server). there is another MSSQL server (in the LIVE server). I want the script to get the information (content) in the development server so that I can import the data to the LIVE server. I have no problems creating tables etc. but could not get the content out. :( Khtan,I don't have connection from online server from my local server. :( If I did, I could use the import/ export function and need not do this the hard way. :( I only got access to the sql query window online. :( I will try to google your recommendation. BOL on DTS or SSIS... but frankly, I have no clue on what you are talking about. Please be patient with me as I am a beginner. Hopefully, google will let me understand this better.
If you've already created tables in live server, you could use DTS export/import wizard to get your datas to live. |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-06-30 : 02:32:21
|
| the live server is running an MSSQL express edition. when I go to the database, there is no task > Import or Task> export.Dear khtan, I have tried to bcp out (which I did successfully), but could not bcp in. this problem is driving me nuts. I guess this is because I am leasing a server from a web hosting provider, and I don't have physical access to the server (just have access to their online MSSQL query module). Visakh16I created the tables by right clicking on the table, > script table as > Create to. This will automatically generate a CREATE Table SQL script. But it does not import the data in the table. :(That's why I am checking if it is possible to find some way to script the data in the server as an sql query. Then I can copy out the results to the LIVE server. :D |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-06-30 : 02:39:02
|
| I managed to resolve question 1 above by doing the following query Select 'Insert into Country (countryname) values (''' + CountryName + ''')' from Country :D yeeeeahhh!But I am at a lost for question 2, :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-30 : 02:53:32
|
quote:
Originally posted by EugeneLim11
the live server is running an MSSQL express edition. when I go to the database, there is no task > Import or Task> export.Dear khtan, I have tried to bcp out (which I did successfully), but could not bcp in. this problem is driving me nuts. I guess this is because I am leasing a server from a web hosting provider, and I don't have physical access to the server (just have access to their online MSSQL query module). Visakh16I created the tables by right clicking on the table, > script table as > Create to. This will automatically generate a CREATE Table SQL script. But it does not import the data in the table. :(That's why I am checking if it is possible to find some way to script the data in the server as an sql query. Then I can copy out the results to the LIVE server. :D
If your dev server is not express edition you can try other way around i.e exporting from dev to live using export/import. also you could install DTS wizard from sql 2005 express tool kit if you want to use export import in sql 2005 expresshttp://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/ |
|
|
|
|
How to I get the content of the table out