Asked  7 Months ago    Answers:  5   Viewed   46 times

We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.

Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query.

The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data.

I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset?

The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way.

I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.

 Answers

62

Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

This is a quick run through to generate the INSERT statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. Right-click on the database and go to Tasks > Generate Scripts.
  2. Select the tables (or objects) that you want to generate the script against.
  3. Go to Set scripting options tab and click on the Advanced button.
  4. In the General category, go to Type of data to script
  5. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK. SqlDataOptions

You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.

Tuesday, June 1, 2021
 
fillobotto
answered 7 Months ago
82

With Eclipse go to Preferences -> PHP -> Editor -> Templates -> New and use something like this:

/**
 * Method to get the ${PropertyName}
 *
 * @return  ${ReturnType}  return the ${PropertyName}.
 *
 * @since   __DEPLOY_VERSION__
*/
public function get${MethodName}() {
  return $$this->${PropertyName};
}

/**
 * Method to set the ${PropertyName}
 *
 * @return  boolean  True on success and false on failed case
 *
 * @since   __DEPLOY_VERSION__
*/
public function set${MethodName}($$value) {
  $$this->${PropertyName} = $$value;
}

To use the template type its name and press ctrl+space - a context menu should also automatically appear when you type the name.

Wednesday, March 31, 2021
 
huhushow
answered 9 Months ago
90

I had the same problem. I don't know what caused it, but I fixed it with a quick Registry hack.

  1. Go to HKEY_CLASSES_ROOTsqlwb.sql.9.0ShellOpenCommand
  2. Edit the (Default) value
  3. It should contain something like this: "c:Program Files (x86)Microsoft SQL Server100ToolsBinnVSShellCommon7IDEssms.exe" /dde
  4. Add "%1" to the end
  5. The final value will be something like this: "c:Program Files (x86)Microsoft SQL Server100ToolsBinnVSShellCommon7IDEssms.exe" /dde "%1"
Thursday, July 29, 2021
 
JackTheKnife
answered 5 Months ago
21

That sort of exhaustive testing (i.e., every function) is unnecessary. Not to mention that it would be hard to maintain and take a while to run.

Test for features that you know warrant a test. If you're just testing for the existence of a library, pick a commonly used function to use in your test. If you want to make sure some feature only in newer vesions is available, test using a function only found in those newer versions.

Saturday, August 7, 2021
 
dimitarvp
answered 4 Months ago
28

Press Ctrl-Shift-R to refresh the intellisense.

Sunday, August 8, 2021
 
muaaz
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share