0
Thank you Stefan, I appreciate yout help, but I have 2 questions:
- In the command, how to know what is the target DataBase?
- This command works in the analyzer, but it can works in C# by code? e.g.
SqlCommand MyCommand = new SqlCommand ("select 'INSERT INTO [dwdb].[dbo]. [UserTypes]([UserTypeID], [Description])
VALUES(' + Convert(varchar(20),UserTypeID) + ', ' + Description + ')' from UserTypes", MyConnection);
MyCommand.ExecuteNonQuery();
0 We're talking about MS Sql server, right?
I don't think that there is an easy way in .net 1.1. With 2.0 along comes the SqlBulkCopy that servers this purpose but by now the easiest way that I have thought of is the following:
1. In the Query Analyzer Object Browser right click the table that you want to copy and choose "Script Object to New Window as" "Insert"
the result is, for example:
INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description])
VALUES(, )
for a table with two columns: UserTypeID,int and Description,varchar(50)
2. You wrap the generated insert statement into SELECT FROM, like this:
SELECT 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description])
VALUES(, )' FROM UserTypes
3. Replace the autogenerated column name-type pairs with string concatenation, like this:
select 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description])
VALUES(' + UserTypeID + ', ' + Description + ')' from UserTypes
We're almost ready. The only thing left is convertion to varchar for the int fields, e.g. UserTypeID is to become Convert(varchar(20),UserTypeID)
The final result is:
select 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description])
VALUES(' + Convert(varchar(20),UserTypeID) + ', ' + Description + ')' from UserTypes
When you run it agianst the original database you get the script that copies the data. It's strange that this is not built in the SQL Server. This data scripting funcitionality is available even in MySQL.
I hope that you don't get somehow insulted by my detailed explanation but I have no idea of your MS SQL Server skills and also this might be useful to others. I hope it wil be of some help.