D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Tuesday, 8 December 2009

Create table code for testing purposes - SQL

To create and populate a test table from existing data for use in forums and for testing purposes:

IF OBJECT_ID('TempDB..#testtable','U') IS NOT NULL
DROP TABLE #testtable
----
CREATE TABLE.........
----

Create test data:

SELECT 'SELECT '
+ QUOTENAME(column1,'''')+','
+ QUOTENAME(column2,'''')+','
+ QUOTENAME(column3,'''')
+ ' UNION ALL' FROM existingdatatable

Run the above select statement and copy the resultant output (remove final UNION) into:

SET IDENTITY_INSERT #testtable ON
----
INSERT INTO #testtable(column1, column2, column3)
SELECT.....
----
SET IDENTITY_INSERT #testtable ON

No comments: