To get data from table in script from sql server 2005

Question

Hi Experts,

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.

Please help, it's urgent

Answer 1

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

 INSERT INTO newTable SELECT * FROM tblHistory

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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!