Generating a SQL Server population routine

From: Martin_Hurst(at)dom(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Generating a SQL Server population routine
Date: 2003-10-06 17:06:14
Message-ID: OFE369ACF5.987F094F-ON85256DB7.005D9505@pa.dominionnet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Has some one come up with a similar type script that could be used in a
Postgresql database?
The script below was created for a SQLServer database.
Thx,
-Martin

++++++++++++++++++++++++++++++++++++++
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci913717,00.html

In the early stages of application design DBA or a developer creates a data
model. Unfortunately many models work very well with a handful of rows but
fail miserably when the application grows by leaps and bounds. This is why
it is important to populate your data model with data and stress test it
prior to making it available for users. Test data doesn't have to be
perfect; indeed, you can duplicate the same record, or a few different
records, to test the performance of your queries.

This article offers a script for generating INSERT statements for every
table in your database. The script is fairly simple -- it relies on three
system tables: sysusers, sysobjects and syscolumns. It generates an INSERT
statement duplicating the top row in your table. However, it can be easily
altered to fit your needs.

SET NOCOUNT ON
DECLARE @table VARCHAR(200),
@owner VARCHAR(100),
@sql VARCHAR(2000),
@sql1 VARCHAR(2000)

DECLARE @schema TABLE (
table_name VARCHAR(200),
column_name VARCHAR(200))

INSERT @schema
SELECT c.name + '.' + a.name, b.name FROM sysobjects a INNER JOIN
syscolumns b ON b.id = a.id
AND a.type = 'u'
AND a.name <> 'dtproperties'
INNER JOIN sysusers c ON c.uid = a.uid
ORDER BY a.name, b.colid

DECLARE table_cursor CURSOR FOR

SELECT DISTINCT table_name FROM @schema

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql1 = ''
SELECT @sql1 = @sql1 + ', '+ column_name FROM @schema
WHERE table_name = @table

SELECT @sql1 = SUBSTRING(@sql1, 3, LEN(@sql1)-2)

SELECT @sql = 'INSERT ' + @table + '( ' + @sql1 + ' ) ' + CHAR(10) + '
SELECT TOP 1 ' + @sql1 + ' FROM ' +
@table

SELECT @sql
FETCH NEXT FROM table_cursor INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor

In the pubs database, the output will be similar to the following:

INSERT dbo.authors( au_id, au_lname, au_fname, phone, address, city, state,
zip, contract )
SELECT TOP 1 au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM dbo.authors

INSERT dbo.discounts( discounttype, stor_id, lowqty, highqty, discount )
SELECT TOP 1 discounttype, stor_id, lowqty, highqty, discount FROM
dbo.discounts

INSERT dbo.employee( emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date )
SELECT TOP 1 emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date FROM dbo.employee

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-10-06 18:01:34 Re: Adding Indexes to Functions
Previous Message Lamar Owen 2003-10-06 15:59:10 Re: PostgreSQL Beta4 Tag'd and Bundle'd ...