CODEWITHSUNDEEP
sqlsql-server-2005
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

To get data from table in script from sql server 2005

I am using sql server 2005

I have a table [say tblHistory] and this table contains 100 rows. I have created the same table at the server, but the table doesn't have the data, I want data from tblHistory to convert into

INSERT INTO tblHistory ------

so that I could run the script on the server to fill the database.

Upvotes: 0

Views: 962

Answers (5)

marc_s
marc_s

Reputation: 754268

You should check out the SSMS Tools Pack - one of its feature is the ability to generate those INSERT scripts you're looking for!

Get the SSMS Tool Pack from this site - it's an add-in for SQL Server Management Studio - highly recommended!

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

If the new table doesn't already exist, you can use SELECT INTO:

SELECT *
  INTO new_table
  FROM tblHistory

But that's the caveat - it has to be a new table, no data already in there.

Otherwise, use:

INSERT INTO new_table
SELECT x.* --preferrable to actually define the column list than use *
  FROM OLD_TABLE x

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134943

you need to create a linked server between the two servers and then you do something like this

INSERT INTO tblHistory
select * from LinkedServerNAme.DatabaseName.SchemaName.tblHistory

To add a linked server read this http://msdn.microsoft.com/en-us/library/ms190479.aspx

BTW you can also use OPENROWSET, OPENDATASOURCE, SSIS or bcp out and bcp in

Upvotes: 0

Paul Kearney - pk
Paul Kearney - pk

Reputation: 5533

To generate all the INSERT INTO statements you need based on table data, take a look at this project: http://www.codeproject.com/KB/database/sqlinsertupdategenerator.aspx

Upvotes: 1

froadie
froadie

Reputation: 83033

Not sure I understand the question... you just want to copy the contents of one table into another? 

 INSERT INTO newTable SELECT * FROM tblHistory

Upvotes: 0

Related Questions