Elon's Blog

Stuff that may be of use to someone…

Archive for the ‘SQL Server’ Category

Microsoft SQL Server – General Product and News related Articles

Move \ Copy Files with Date and timestamps using DOS commands

Posted by Elon B. on September 2, 2012

You can use DOS commands , possibly in a batch file (.bat)  if you want, to copy files from one location to another, adding a DATE or TIME stamp to the file name.

Open up a DOS\Command prompt window.

We can see the results of using the ECHO cammand to display the current date and time, as follows.

DOS Date and Time commands

We can then use this in our Dos COPY command as follows. [  copy *.txt *_%date:/=%_.txt  ]

Don’t forget you can use wildcards for the filenames as normal.

Copy adding DATE to filename

Copy with Date and Timestamp  [  copy *.txt *_%date:/=%_%TIME::=%.txt /Y ]

Add DATE and TIME to filename

Hope you find this useful.

Posted in Microsoft Related, SSIS, Windows 7, Windows Office - General | Tagged: , , , , | Leave a Comment »

Auto Generate INSERT statements for a SQL Server 2008 Table using SSMS

Posted by Elon B. on November 18, 2011

I was doing some SSIS development for a client and had to generate and alter some test data for some packages.

I already had a table with values in that I wanted to capture via INSERT statements, so that I could chop and change the values as needed.

There is a method in SQL Management Studio 2008 (SSMS) that allows you to create these statements right out of the box for SQL Server 2008, without using any scripts or add-ins from SSMS.

So, for example,  to create the INSERT statements for the Table: HumanResources.Department in the AdventureWorks database.          (Note: Click on images to enlarge)

Chosen TableFig 1

Right Click on the Database Name, select Tasks|Generate Scripts…

Click the Next button on the “Generate and Publish Scripts” dialog box that appears.

From the “Choose Objects” screen, select the table from drop down menu. We are doing this for a single table, but as you can see you can do this for multiple objects or the entire database.

Click Next.

On the next screen, decide where you wish to output the script to. I chose a “new query window” in my example.

Then Press the Advanced Button.

From the Advanced Scripting Options dialog, select the Drop-down options for the “Types of data to script” option.  You can choose (i) Data Only (ii) Schema and data or (iii) Schema only

Click OK, and then select the Next Button. Click the Next button again from the next screen and then the Finish button after the task has completed

In this example, the CREATE TABLE and INSERT statements appear for my chosen database table within a new query window in SSMS.

I can now extract and edit the INSERT statements, as required, to create new or different test data for my SSIS project.

Posted in SQL Server, SSMS, T-SQL | Tagged: , , , , , , | Leave a Comment »

%d bloggers like this: