| Author |
Topic |
|
EugeneLim11
Posting Yak Master
167 Posts |
 Posted - 2008-10-30 : 03:41:47
|
| Hi all, I have some data in a database in my local server.What I want to do is generate an sql script for the DBA (offshore company) to insert these data into the database, if the data is not already there. i.e. the offshore company has the same tables structures and some outdated data. is there any way to export the data as sql scripts that will append each row for every table in the database? e.g. I want to export as "Insert into TableName (columnName1, columnName2) values ('value1', 'value2') GO"There are primary key constraints that will be violated (and the insert sql will fail automatically) if it is old data, so I am not worried about getting sql scripts for the older data. Alternatively, if there are better ways of exporting the data and importing the data to offShore database (I have no physical access nor do I have the database username and password for this foreign client), kindly let me know or guide me to any url where I can find resources on how to do this. your help is very much appreciated. Thank youRegards,Eugene Limcheck out my blog at http://www.aqauriumlore.blogspot.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 03:52:07
|
| make use of catalog views INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS to create the sql script. You need to loop through each records to get column info of table. The values need also be fetched from table. |
 |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-10-30 : 04:20:04
|
| Visakh16, That is a good suggestion. However, I am struck. how do I get the value of the table ? I seemed to be struck below and cannot seem to progress. //1. Create a cursorCreate cursor_name1 Cursor as Select Table_name from Information_Schema.Tables //2. Loop through each table. Declare @tablename as varchar (500)@tablename = Fetch Next from cursor_name1 //3. Loop through each columnCreate cursor_name2 Cursor as Select Declare @columnName as varchar(500)@columnName = Fetch Next from cursor_name2 Select 'Insert into ' + @tablename + '(''' + @columnName) + ''') values ('" + ??Help is appreciated. Thank you.Regards,Eugene Lim check out my blog at http://www.aqauriumlore.blogspot.com |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-10-30 : 04:34:43
|
| Thinking of doing a backup of my database and sending the .bak over. The problem is I need to drop the database on the client side when I restore the backup and any data on the client side is lost. can bcp work to transfer the information quicker?check out my blog at http://www.aquariumlore.blogspot.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-30 : 05:45:41
|
| The script idea sounds like it could be a nightmare. (you'll have to make sure that you insert data in the right order (to satisfy your foreign key constraints).I don't know anything about this but this sounds like a problem begging for some sort of replication operation?I'm sure some of the more DBA focused individuals here will be able to help.-------------Charlie |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-10-30 : 05:52:18
|
| I hope so. there are over 300 tables in there, and close to 3000 columns. I hope there is a way to get the Sql script easily .. :( Feeling very frustrated that you can create scripts of table, etc. but not the data in MS SQL 2005 . Else, I will just script all data. :(check out my blog at http://www.aquariumlore.blogspot.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-30 : 05:58:38
|
| This looks like exactly what you need. I have no idea how to set it up however!http://technet.microsoft.com/en-gb/library/ms152485(SQL.90).aspx-------------Charlie |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-10-30 : 06:11:01
|
| Hi Charlie,thanks for the link. Unfortunately, it is not what I am looking for. Thank you anyway. :) Regards,Eugenecheck out my blog at http://www.aquariumlore.blogspot.com |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-10-30 : 06:44:33
|
For SMALL tables i am using SP InsertGenerator which I have adapted for my requirements to consider identity-columns, handling schemas in tablenames etc...Maybe it's an approach for you're problem...Here ist the link to the originalhttp://www.codeproject.com/KB/database/InsertGeneratorPack.aspxWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-30 : 07:40:50
|
| Well if you don't have access to offshore company,then how are you going export data .How big is your database? |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-10-30 : 21:27:17
|
| SoDeep,Normally, I get the SQL scripts out as insert statements to give to the Database Administrator in the offshore company. The problem is this time, the data is very big, over 300 tables in there, and close to 2900+ columns.WebFred, thanks you! You are a gem! This is exactly what I am looking for a script to automatically generate insert statements. ;) While it does not work for images, it is ok with me. only a few fields are images. Regards,Eugenecheck out my blog at http://www.aquariumlore.blogspot.com |
|
|
|
|
How do I get the content as SQL script?