Copying tables from SQL Server 2005 to another machine with SQL Server 2005 Express

logideepak, please expand upon the exact menu options and tools I would use in SQL SERVER 2005 Express to import the scripts. I'm not as familiar with the Express version, having been spoiled by the Management Studio of Sql Server 2005. You also mentioned opening an instance of Express on 2005 Management studio. Can you explain this to me?

Here is a stored procedure to generate insert scripts. But it is using cursors. Also will not mind IDENTITY columns.

create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)

set @cols=''

declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table 

open colcur

fetch next from colcur into @col

while @@fetch_status=0
begin
select @cols = @cols + ', ' + @col

fetch next from colcur into @col
end

close colcur
deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

--select @cols

declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)

select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '

select @sql = @sql + 'values ('''

declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0
begin
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''


select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '

if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql = @sql + ''', '''

fetch from ccur into @colname, @coltype
end

close ccur
deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table

exec (@sql)

Select allOpen in new window

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23133

Raj

Arnold, I do not think I have the ability to link the two databases from the two machines, or do I? If both were connected via an intranet or common server, I suppose I could do that suggestion.

Raj, whichever way gets the job done. I'll look into some of the suggestions.

logideepak, Thank you for your thorough explanation, and this is the process that I would use when I have other servers connected/linked to one another, such as at my work place. However, my laptop and my desktop are not connected in this fashion and thus I do not think they could use the same 2005 Management Studio, could they?

Can I generate scripts on the laptop, save them to a thumb drive, transport them to the desktop, and then import/execute the scripts on the desktop? If so, what utility and menu options do I use in SQL Server 2005 Express?

I seem to have a bigger issue now... my desktop seems to have crashed and I can't even get into safemode after selecting that option. I'll try out the suggestions after I get the desktop fixed (or buy a new one).

Sorry for the long delay. I now have SQL Server Express 2008 along with VS 2010 running on Windows 7 on my desktop. I do not have the Management Studio with the SSE 2008, though I have it on my laptop with SQL Server 2008. I will look into obtaining the free version of Management Studio for SSE 2008. Perhaps if I have the software then I could pursue the ideas about transferring data.

I am having difficulties installing SSMSE for SSE 2008. First, I got the SSE 2008 automatically when I installed Visual Studio 2010. Second, I went to the link provided to download and install SSMSE, and it seems I have a 64-bit system, having a new computer with Windows 7. Checking one of the SSE menus leads me to belive it is 64-bit. Thus, I downloaded SQLManagementStudio_x64_ENU.exe and then ran it. I then get a compatibility message: This program has known compatibility issues. I then check for solutions online for MS SQL Server 2008 and it says:

Your version of SQL Server 2008 isn't compatible with this version of Windows.
A newer version of this software is available for download that solves this problem.

Then I clicked the link to download the lastest Service Pack for SSE at http://support.microsoft.com/kb/968382

To obtain Microsoft SQL Server 2008 Express SP2, visit the following Microsoft website:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=40acf55f-0715-4bb0-9735-d02fc2c8a7af&displaylang=en

I then downloaded SP2, it seems, but when I run it (SQLEXPR_x64_ENU.exe), it extracts and then I get a complex menu for SQL Server Installation Center with options for Planning, Installation, Maintenance, Tools, Resources, Advanced, Options.

Am I in the right area or should I be doing something else to get Management Studio?

My Mistake, referred you to a download for the newer SSMSE versus the one that matches your installed SQL server version.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5d76230d-580d-4874-8c7d-93491a29db15

Arnold, the link you just provided is for SQL Server 2005 express, but I do not have 2005 any more. With my new computer and my new software, I only have SQL Server 2008 Express.

http://www.symantec.com/connect/articles/install-and-configure-sql-server-2008-express
Ignore the references to deployment or other extraneous items.

The item has a guide with images/illustration that might be helpful in assisting you with the install of the SQL 2008 express and then the addition of the SQL 2008 management studio express.

This might be better/newer http://www.microsoft.com/express/Database/
or better still http://www.microsoft.com/express/Database/InstallOptions.aspx
Download the installer that has both items or the ones that has all the options included.

It all depends on which features you need.  You could of course update what settings you have which might be what that install is offering i.e. you already have sql 2008 express installed, and the one you are running asks whether you are installing a new instance or whether you are updating the existing one and corresponding tools.

Having installed SQL Server 2008 in the past with VS 2010, I notice that I have an option for SQL Server Import and Export Wizard. This is seen an an option when I go to All Programs --> Microsoft SQL Server 2008 --> Import and Export Data (64-bit). It seems similar to the Wizard used in the Management Studio. While I plan to try to work through my difficulties in getting/installing Management Studio for SQL Server 2008 Express, do you think this Import/Export Wizard will suffice in getting my database tables copied?

I was able to install Sql Server 2008 Management Studio. Now I have issues getting past the login for "Connect to Server". When I use Windows Authentication and select (local) as server, I get error message "Cannot connect to (local)". Additional information: A network-related or instance-specific error occurred while establishing a connection to the SQL Server, etc. If I try browsing for servers, I get nothing under "Local Servers" or "Network Servers". Any ideas from here?

Use the browse option to see the SQL instances you have on your system.
Unfortunately when you use local you are missing the instance reference local\sql2005 local\sql2008.
If they are on the same IP, did you adjust the port on which each will listen? Or you have not configured them for TCP connectivity using the microsft sql server configuration tool shared memory is enabled by default named pipes and TCP have to be enabled.  On a single IP the two sql server instances can not use the same 1433 port one has to be altered.

Although I looked into the items mentioned, I decided to re-install SSE 2008 and carefully select the default options. As a result, I was able to get SSMS 2008 to access a server name of HP-OWNER. It also is able to access (local). Additionally, I created a database by the same name as that which exists on my laptop. Now that all the pieces are in place on my desktop, I can proceed with the original advice given in this post for copying the table structures and contents from my laptop to my desktop, which are not connected to each other.

Somehow the system gave credit to the first comment as being the "best solution", though I had chosen my last comment in that place. Thanks again for everyone's help.

While everyone contributed in guiding me to a solution (in using Management Studio on the desktop and in copying to Excel), the specific strategy and need to generate Scripts for the structure and to then copy data to regular Excel files to be cut/pasted into Management Studio was mine. If the "best" solution should be placed somewhere else, I am certainly open to changing it. I also think the Bulk Copy Program (BCP) has merit, and if I was concerned with retaining the exact value of the Identity "auto-generated" number in the new database, I would have pursued the BCP more than I did.