Re: Copying table - C# Discussion Boards

18-Jul-10 13:51

I have a created a database by the name of ABC. I want to copy the employees table from the northwind database to my database. Whats the best way to do it.

Can someone direct me to a useful link or let me know how to do it dynamically.

Mycroft Holmes18-Jul-10 14:29

Mycroft Holmes

18-Jul-10 14:29

Use SQL Server Management Studio SSMS.

SQL 2008 (earlier are similar)
Click on the database (ABC)
select tasks
select import data
follow the wizard

Never underestimate the power of human stupidity
RAH

harold aptroot18-Jul-10 14:38

harold aptroot

18-Jul-10 14:38

That does not sound very C#-ish

humayunlalzad18-Jul-10 17:53

18-Jul-10 17:53

I am trying something like this, but its not very accurate

SqlDataAdapter da;
           DataSet ds = new DataSet();

           string conString = @"server = .\sqlexpress;
                               integrated security = true;
                               database = northwind";
           da = new SqlDataAdapter("select * from employees", conString);
           da.Fill(ds);
           DataTable table = ds.Tables[0];

           string conString2 = @"server = .\sqlexpress;
                               integrated security = true;
                               database = ABZ";
           string commandString = "Create table Employees ( ";
           SqlConnection con = new SqlConnection(conString2);
           DataTable tab = new DataTable("Employees");
           foreach (DataColumn  column in table.Columns )
           {

               commandString = commandString + column.ColumnName + " " + column.DataType + " " + "nvarchar (50), "  ;

           }

I can make a switch statement for the data type. But the maximum size of the data always shows up as -1

PIEBALDconsult18-Jul-10 15:56

18-Jul-10 15:56

If they're on the same server you can use a SELECT statement to create a new table with the contents you want.

Abhinav S18-Jul-10 18:25

Abhinav S

18-Jul-10 18:25

This[^] might help.

The funniest thing about this particular signature is that by the time you realise it doesn't say anything it's too late to stop reading it.

My latest tip/trick - Silverlight *.XCP files

humayunlalzad18-Jul-10 18:42

18-Jul-10 18:42

Thanks in advance, as that was the second part of my problem, to copy the data once the table has been copied. The first one was to copy the table itself, still looking for an answer.

PIEBALDconsult19-Jul-10 3:17

19-Jul-10 3:17

So you didn't look into using a SELECT to create and populate the table?

humayunlalzad19-Jul-10 12:28

19-Jul-10 12:28

I was trying something like this

SqlDataAdapter da;
           DataSet ds = new DataSet();

           string conString = @"server = .\sqlexpress;
                               integrated security = true;
                               database = northwind";
           da = new SqlDataAdapter("select * from employees", conString);
           da.Fill(ds);
           DataTable table = ds.Tables[0];

           string conString2 = @"server = .\sqlexpress;
                               integrated security = true;
                               database = ABZ";
           string commandString = "Create table Employees ( ";
           SqlConnection con = new SqlConnection(conString2);
           DataTable tab = new DataTable("Employees");
           foreach (DataColumn  column in table.Columns )
           {

               commandString = commandString + column.ColumnName +" nvarchar (50), "  ;

           }

           commandString = commandString + ")";

           SqlCommand cmd = new SqlCommand(commandString, con);
           con.Open();
           cmd.ExecuteNonQuery();
           con.Close();
           MessageBox.Show("Table Created");

But the prob was I was getting a -1 value for column.MaxLength when I was trying this

foreach (DataColumn  column in table.Columns )
            {
              
                commandString = commandString + column.ColumnName +" nvarchar (" + column.MaxLength + "), "  ;
               
            }

But how do you suggest I use the Select statement?

PIEBALDconsult19-Jul-10 15:42

19-Jul-10 15:42

A statement like SELECT * INTO JunkCopy FROM Junk.dbo.JunkName will copy a table and it contents.

To add rows thereafter, a statement like INSERT INTO JunkCopy SELECT * FROM Junk.dbo.JunkName, but probably with a WHERE CLAUSE to avoid duplicates, should do the trick.

But these will only work when the databases are on the same server or in linked servers.

I have also had to copy tables among different database systems with code similar to (but infinitely better than Big Grin | :-D

) the code you posted.

humayunlalzad24-Jul-10 5:47

24-Jul-10 5:47

Thanks a lot that did the trick. That was what I was looking for.

Select * into

PIEBALDconsult24-Jul-10 5:49