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.








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: