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.
>