CODEWITHSUNDEEP
sql-serversql-server-2008ado.net
Safeena
Safeena

Reputation: 403

Can I get a table script with its data

I am using a SQL Server 2008 database.

I have two databases namely db1 and db2. In both there is a table tblcountry. I create this on 1st database. Then how can I script with its data for create on 2nd database?

I use the code below

CREATE TABLE [dbo].[tblCountry]
(
    [record_Id] [int] IDENTITY(1,1) NOT NULL,
    [country] [nvarchar](150) NULL,
    [nationality] [nvarchar](150) NULL,
    [lsdMdfdOn] [datetime] NULL,
    [lstMdfdBy] [nvarchar](350) NULL,
    [isDeleted] [bit] NULL,
    [isEnabled] [bit] NULL,
)

Then what code will I use for the getting include the data?

Upvotes: 2

Views: 42

Answers (4)

JC Borlagdan
JC Borlagdan

Reputation: 3618

The most Easy way for this problem is for you to

  1. Right Click on the Database on the Object Explorer
  2. click Generate Scripts
  3. on the introduction click Next
  4. Select radio button on Script entire database and all database objects or you can just select specific tables or stored procedures by selecting the other radio button
  5. On the Set Scripting Options click on Advanced Select the things you want to script
  6. Then on the Query just change the database name after the query USE db2

Upvotes: 0

AIDA
AIDA

Reputation: 529

If you are on the same server or have a linked server:

CREATE TABLE tblCountry

SET IDENTITY_INSERT tblCountry ON

INSERT INTO [database2].tblCountry SELECT * FROM [database1].tblCountry

SET IDENTITY_INSERT tblCountry OFF

Upvotes: 1

Panchal jigar
Panchal jigar

Reputation: 1

Right click on database and click on tasks and export data

you can use export data option in sql server...it will give you data with table script

Upvotes: -1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

No, you cannot view the data if you are using the create query.

If you want to see the data of the table on second database then you can use this query on your second database db2

select * from [db1].[dbo].[tblCountry]

But you can not view the data and create query at the same time.

Although it may seem very wierd solution but I guess what you can do is you can copy the create query on the query analyzer window and beneath that write the select query and execute it. (But I guess this is how most of the programmers do that)

Upvotes: 1

Related Questions