scripting data

Hi,

I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

Thanks much for any advice regarding this.On Feb 13, 6:07 am, "farseer" <fars...@.optonline.netwrote:

Quote:

Originally Posted by

Hi,
>
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
>
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
>
Thanks much for any advice regarding this.

Hi,

Yes, there is and it's called DB Ghost (www.dbghost.com). It has a
Data and Schema Scripter (which does what you need) and a host of
other tools that form a complete change management process for SQL
Server.and can all be called from the command line for full process
automation :)

Kind regards,

Malcolm|||farseer (farseer@.optonline.net) writes:

Quote:

Originally Posted by

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

SELECT 'bcp yourdb..' + name + ' out ' + name + '.bcp -n -T'
FROM sys.objects
WHERE type = 'U'

Copy result into a BAT file and run.

The to load:

SELECT 'bcp yourtargetdb..' + name + ' in ' + name + '.bcp -n -T ' +
'-S REMOTSER' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE o.object_id = c.object_id
AND c.is_identity = 1)
THEN ' -E '
ELSE ''
END
FROM sys.objects o
WHERE type = 'U'

I assume that you are not using any data types that are not available
in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||I've listed some tools here: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.
You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:

> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

Scripting creation of server alias

Does anyone know if there's a way to script creation of a server alias,
as you can do using the Client Network Utility?
allancady@.yahoo.com wrote:
> Does anyone know if there's a way to script creation of a server
> alias, as you can do using the Client Network Utility?
Do you mean from a T-SQL script? The utility just writes to the
registration database. Sniff the regdb while you perform adding a new
alias and see where it goes. Then you can use any programming tool or
just a .reg file to add the new data to everyones PC. From a domain this
is easy.
You can sniff the regdb using free tools from www.sysinternals.com
David Gugick
Imceda Software
www.imceda.com

Scripting creation of server alias

Does anyone know if there's a way to script creation of a server alias,
as you can do using the Client Network Utility?allancady@.yahoo.com wrote:
> Does anyone know if there's a way to script creation of a server
> alias, as you can do using the Client Network Utility?
Do you mean from a T-SQL script? The utility just writes to the
registration database. Sniff the regdb while you perform adding a new
alias and see where it goes. Then you can use any programming tool or
just a .reg file to add the new data to everyones PC. From a domain this
is easy.
You can sniff the regdb using free tools from www.sysinternals.com
David Gugick
Imceda Software
www.imceda.com

Scripting Compound Primary Keys

Hi all

I have a requirment to dynamically script a whole database schema. Scripting tables, constraints defaults etc. This all works well until I come up against compound primary key constraints then using Information_schema or system tables I only seem to get to the first column making up the constraint. Anyone have a snippet of script which can list the primary key and all its columns for a given table?

Currently using SQL 2000 and shortly moving to SQL 2005.

Thanks

The following query may give idea...

CREATEProc TableCreationText

(

@.TableName Nvarchar(2000)

)

as

Create Table #Text

(

Source NVarchar(4000)

)

Insert Into #Text(Source) Values ('Create Table ' + @.TableName + '(');

Insert Into #Text(Source)

Select

'[' + C.Name + '] ' +Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +

Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End

+ Case When C.Colstat & 1 = 1 Then ' Identity(' +Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') 'Else '' End

+ Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')

+ Isnull(' Default ' + defcomments.text ,'') + ','

From

Sysobjects T

Join Syscolumns C on T.id = C.Id

Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'

Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id

And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder

Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1

Left Outer Join sysobjects def On def.parent_obj = T.Id

And def.xtype= 'D' And def.Info = C.Colorder

Left Outer Join syscomments defcomments ON defcomments.id = def.id

Where

T.Type='U'

And T.Name=@.TableName

Order By

T.Name,

C.Colorder

Insert Into #Text(Source)

Select

'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End+ Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End+'(' +dbo.GetAllIndexedColumns(@.TableName, 2)+ '),'

From

sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and ind.name = object_name(tbl.id)

and xtype in ('PK', 'UQ')

Insert Into #Text(Source)

select

'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +

object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'

from

sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and fk.constid = tbl.id

and xtype in ('F')

Declare @.Source as Nvarchar(4000);

Select @.Source = Source From #Text;

Update #Text

Set

Source = Substring(Source,1,Len(Source)-1)

Where

Source = @.Source;

Insert Into #Text(Source) values (')');

Select Source From #Text

|||Hi,

Could you also provide script for dbo.GetAllIndexedColumns this function.

Zafar|||

Thanks for that

dbo.GetAllIndexedCollumns is this a hellper stored proc ? The script fails at this point when I run it on my system.

|||

oops.. I missed that.. here it is..

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)

Returns NVarchar(4000)

as

Begin

Declare @.IndexedCols as NVarchar(4000);

Declare @.I as Int;

Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + '' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End

Select @.I = @.I +1;

While index_col(@.ObjectName, @.indexid, @.I) is not null

Begin

Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + '' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End

Select @.I = @.I +1;

End

return @.IndexedCols;

End

|||

For some reason the script only reports the second of the columns involved in the compound key. I figure that the clues are in there I just need to keep plugging away at it...

Thanks

|||

Yes.. I didn't tested properly in SQL Server 2005..

here the updated query..

Code Snippet

Declare @.tablename as varchar(100)

Set @.tablename = 'YourTablename'

Select

'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, ind.indid) + '),'

From

sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and ind.name = object_name(tbl.id)

and xtype in ('PK', 'UQ')

Scripting component.

Hi Guys,

I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.

I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.

Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.

Any quick help would be really appreciated.

TA

Gemma

Gemma,

Any reason for using the script component to create the file? Can you just use the script to populate the pipeline and use a flat file destination for the file? If so, I recently described how you would go about creating a flat file with header and trailing records.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2168163&SiteID=1

|||

Hi Eric,

I'm just combinging the fields, padding the fields with zero and doing some cleaning of the data as well.

Is there any other way? I've checked your post but I don't know how to add something from the database in the header.

Yes you can enter any expressions but i need the value from the table.

Can you help me do that?

TA
Gemma

|||

Add a boolean flag at the class level and default it to True, Then in the ProcessInput check the flag. If it is True, write the header line and change the flag to false.

There's an example of that approach in the script I posted here - http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx. The example is not what you were asking about, but the script illustrates the technique.