Scripting "INSERT"s in an SQL database?

Hi there,

Getting ready for deployment and I would like to be able to run an install
script that will not just generate the schema, but fill in some of the
tables with default data. Is there a tool out there that will scan user
tables in an SQL database and generate the script required in order to
create a database with this default data?

Thanks

Robin"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robin

http://vyaskn.tripod.com/code.htm#inserts

If you have a lot of default data, it might be more efficient to have the
data in flat files and load it with bcp.exe or BULK INSERT.

Simon|||Hi

Another alternative would be to ship a default data and log file that is
already populated. For upgrades then a similar container but you would need
to use a tool such as those produced by www.red-gate.com sqlcompare or
www.innovartis.co.uk/Home.aspx dbghost or your home grown application.

John

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robinsql

Script Question: Getting data from SQL Server

I am trying to fill a DataTable with values from SQL server in SSIS. I just can't get the hang of the connections inside SSIS. I tried taking the example in BOL for "Script component [Integration Services], source components" and adapting to my needs but sqlReader doesn't seem to have the ability to use fill to populate a DataTable - so I tried changing to sqlAdapter and can't get it to work. (I get error about connection not being closed or object reference not set to instance of object) Thanks in advance for all the help...

Imports System

Imports System.Data

Imports System.Math

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml

Public Class ScriptMain

Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim sqlConn As SqlConnection

Dim sqlQuery As SqlCommand

Dim sqlDataHolder As SqlDataAdapter

Dim dtLookup As DataTable = New DataTable()

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.ADOLOOKUP

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

Dim LookupSql As String

LookupSql = "SELECT "

LookupSql += "* "

LookupSql += "FROM "

LookupSql += "Table "

LookupSql += "WHERE "

LookupSql += "TypeID = " & Me.Variables.TypeID

sqlConn.Open()

sqlQuery.CommandText = LookupSql

sqlQuery.Connection = sqlConn

sqlDataHolder.SelectCommand = sqlQuery

sqlDataHolder.Fill(dtLookup)

sqlConn.Close()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Lookup stuff here

End Sub

End Class

Connections are not the problem here. Creating objects is.

You've created variables of type SqlCommand and SqlDataAdapter, but are not instantiating them.

Try something like the following and you'll get a lot closer to what you're looking for.

If Not sqlConn.State = ConnectionState.Open Then

sqlConn.Open()

End If

sqlQuery = New SqlCommand()

sqlQuery.CommandText = LookupSql

sqlQuery.Connection = sqlConn

sqlDataHolder = New SqlDataAdapter()

sqlDataHolder.SelectCommand = sqlQuery

sqlDataHolder.Fill(dtLookup)

sqlConn.Close()

By the way, is the reason for this component to overcome the fact that you can't parameterize a fully cached lookup (without a lot of schenanigans involving stage table, or views, or TVFS)?

|||

The reason is I have a large dataset (around 1 million rows) and a relatively small lookup table (depending on the variable between 3 and 1500 rows). The problem is the lookup is range based on date time (time >= lookup.starttime and time < lookup.endtime). Basically a record comes in and I have to assign it to a "timeslice" based on the lookup (the size of the timeslice is stored in the variable you see in the lookup).

If I use the standard SSIS lookup and change the sql within, its hideously slow as its making 1 million sql calls - so I am attempting to pull the whole lookup table into memory and instead lookup on that.

Thanks for the info on instantiating... that did the trick.