Re: Copy over a single table in sql server 2008 - Database Discussion Boards

Copy over a single table in sql server 2008

vkEE10-Oct-13 3:00

10-Oct-13 3:00

Hi,

We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments.

I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form

2013-10-01 10:28:35.560

. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information.

What would be the best way to copy over the table?

Thanks so much!

Re: Copy over a single table in sql server 2008

Corporal Agarn10-Oct-13 3:13

Corporal Agarn

10-Oct-13 3:13

Is this a one time or daily.

If daily think about replication.

Can the two systems talk to each other? If you must export to a table one option would be .CSV which would not loose the data like Excel will.

Re: Copy over a single table in sql server 2008

vkEE10-Oct-13 3:19

vkEE

10-Oct-13 3:19

it is a one time deal only.

And the two systems don't talk to each other.

Thanks!!

Re: Copy over a single table in sql server 2008

Richard MacCutchan10-Oct-13 3:36

Richard MacCutchan

10-Oct-13 3:36

Why not use the SQL Server Import and Export Wizard[^]?

Veni, vidi, abiit domum

Re: Copy over a single table in sql server 2008

vkEE10-Oct-13 3:44

vkEE

10-Oct-13 3:44

The environments don't talk to each other - the servers are physically located in different states.

And if i export to excel, I am worried about losing some of the data, as I have specified above.

Thanks.

Re: Copy over a single table in sql server 2008

Richard MacCutchan10-Oct-13 4:11

Richard MacCutchan

10-Oct-13 4:11

vkEmerson wrote:
The environments don't talk to each other - the servers are physically located in different states.

Which is why I suggested the Export/Import Wizard.

vkEmerson wrote:
And if i export to excel, I am worried about losing some of the data, as I have specified above.

Why do you want to use Excel? And if you are storing dates and times in your database as strings you are heasding for trouble.

Veni, vidi, abiit domum

Re: Copy over a single table in sql server 2008

Eddy Vluggen10-Oct-13 5:17

Eddy Vluggen

10-Oct-13 5:17

vkEmerson wrote:
What would be the best way to copy over the table?

Create a backup of the database on server A.

Restore the backup on server B under a new name. Do a "SELECT INTO" from the new database into the target-table.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Copy over a single table in sql server 2008

vkEE10-Oct-13 5:38

vkEE

10-Oct-13 5:38

You will be surprised at to how many restrictions smaller size companies have Smile | :)

The database size is huge, and no FTP services set up. Not allowed. If I try to copy the file from production server, with a simple copy and paste onto my server, it takes for ever and sometimes just disconnects. And to top this, we cannot do many things on the production server, since they don't want to slow down the website it feeds to Frown | :(

Re: Copy over a single table in sql server 2008

Richard Deeming10-Oct-13 6:21

Richard Deeming

10-Oct-13 6:21

How about reversing the process first? On the source server, create a new empty database and use SELECT INTO to copy just the table(s) you want. Back up the (much smaller) database, copy the backup to the destination server, restore, and SELECT INTO the destination table.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Copy over a single table in sql server 2008

Eddy Vluggen10-Oct-13 8:41

Eddy Vluggen

10-Oct-13 8:41

vkEmerson wrote:
You will be surprised at to how many restrictions smaller size companies have

If the restriction makes the request technically impossible, it's usually the restriction that's changed - not the request.

vkEmerson wrote:
If I try to copy the file from production server, with a simple copy and paste onto my server

How about writing your own BITS client[^]?

vkEmerson wrote:
they don't want to slow down the website it feeds to

Then why are they allowing copy/paste? One will "have to" write files on the server if the server is to be updated Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Copy over a single table in sql server 2008

Simon_Whale10-Oct-13 5:45

Simon_Whale

10-Oct-13 5:45

Have you thought about using a linked server connection?

Creating Linked Servers (SQL Server Database Engine)[^]

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: Copy over a single table in sql server 2008

RedDk11-Oct-13 10:49

RedDk

11-Oct-13 10:49

Save the .mdf and .ldf from the origin, and then ATTACH them to the target. No connections. No interface required. Other than the ATTACH script. See BOL for ATTACH details. While there, see other methods.

Re: Copy over a single table in sql server 2008

coded00717-Oct-13 21:39

coded007

17-Oct-13 21:39

you can directly use DTS package. I Ensures you not to lose data. Smile | :)

Mani Prabhakar

Re: Copy over a single table in sql server 2008

rashin ghodratzade18-Oct-13 18:55

rashin ghodratzade

18-Oct-13 18:55

you can Save the .mdf and .ldf from the origin, and then ATTACH them to the target

insert rows into single table from two table which has different columns

abu.bdt8-Oct-13 23:00

abu.bdt

8-Oct-13 23:00

i have two tables a and b.a has 7 different columns and b has 20 different columns. i want to insert rows from a to b.how to insert.
so plz provide solution

Re: insert rows into single table from two table which has different columns

Richard MacCutchan8-Oct-13 23:27

Richard MacCutchan

8-Oct-13 23:27

Read records from table a and insert them into table b, adding the detail for the extra columns.

Veni, vidi, abiit domum

Re: insert rows into single table from two table which has different columns

Simon_Whale9-Oct-13 0:55

Simon_Whale

9-Oct-13 0:55

Here is a sample. have done this using SQL Server but it shouldn't be hard to move it too one of the other databases

SQL

create table #tableOne
(
  id int,
  name varchar(50),
  email varchar(100)
  )

create table #tableNames
(
  id int,
  name varchar(50)
  )

insert into #tableOne values (1, 'simon','simon@email.com')
insert into #tableOne values (2, 'someone','someone@email.com')

insert into #tableNames values (3, 'fred')


insert into #tableOne 
select id, name,''
from #tablenames


select *
from #tableOne 


drop table #tableNames 
drop table #tableOne

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: insert rows into single table from two table which has different columns

coded00717-Oct-13 21:40

coded007

17-Oct-13 21:40

Are they any non null-able columns in table b ?

IF they are non null-able columns then follow below code

SQL

INSERT INTO b(<<Column_Names>>)
SELECT <<Column_Names>> from a [where <<Condition(if any)>>]

Mani Prabhakar

Re: insert rows into single table from two table which has different columns

rashin ghodratzade28-Oct-13 7:02

rashin ghodratzade

28-Oct-13 7:02

INSERT INTO b

(field list b)

SELECT

field list a

FROM

a

Re: insert rows into single table from two table which has different columns

Anudeep Jaiswal - MCA24-Nov-13 19:22

Anudeep Jaiswal - MCA

24-Nov-13 19:22

Specify Columns Of Table B.

Like

Insert Into B (Col1,Col2,Col3)
Select Col9,Col8,Col7 From A.

Please Note That Data Type of Column Should Be Same.

how to use this code in SQL

jojoba207-Oct-13 20:52

jojoba20

7-Oct-13 20:52

how to use fuzzy object oriented database sql?
have to install any other software behind SQL Management Server 2008 ?
this is the query :

SQL

Select  t.*, t.muAge('mid')asMu_Age, t.muHeight('tall')asMu_Height, t.muWeight('heavy')asMu_Weight,  CalcTreshold(&wAge,&tAge,&wHeight, &tHeight,&wWeight,&tWeight) asTre From TBLPersons t  Where  ((((t.muAge('mid')*&wAge)+   (t.muHeight('tall')*&wHeight)+   (t.muWeight('heavy')*&wWeight) ) / (&wAge+&wHeight+&wWeight) )
>= calctre (&wAge,&tAge,&wHeight, &tHeight,&wWeight,&tWeight )  )

modified 8-Oct-13 3:52am.

Re: how to use this code in SQL

Eddy Vluggen8-Oct-13 0:30

Eddy Vluggen

8-Oct-13 0:30

jojoba20 wrote:
how to use fuzzy object oriented database sql?

Ask the vendor; the people that supply the "fuzzy object oriented database" software should know how to install it.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: how to use this code in SQL

jojoba208-Oct-13 1:08

jojoba20

8-Oct-13 1:08

i dont known any one.
I wanna to get help from Code Project world!

I dont known that does need to install any application or ...

Thanks in advanced!

Re: how to use this code in SQL

Eddy Vluggen8-Oct-13 5:00

Eddy Vluggen

8-Oct-13 5:00

jojoba20 wrote:
i dont known any one.

Then how did you deduce that it must be a fuzzy object-oriented query? Where did you find this code?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: how to use this code in SQL

jojoba208-Oct-13 5:26

jojoba20

8-Oct-13 5:26

for my collage !
my sir asked me to do that !
and also he doesnt help any!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.