row copy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

row copy

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 04:54:13

How to copy a entire row from one table to another table in same/diff DB

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 04:58:07

INSERT INTO Destination_Database_Name.dbo.tbl_Destination
SELECT S.Column1,S.Column2,S.Column3.....
FROM Source_Database_Name.dbo.tbl_Source AS S
WHERE S.Column1 = Something .....

Make sure that you take care of the columns which don't allow NULLS and proper Column Datatype matching.

Thanks
Rohit

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 04:59:08

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 05:01:46

quote:
Originally posted by jafrywilson

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

insert into tbl_product ("Bottomline_Price")
select ("Bottomline_Price" from tbl_merchant

Remove those Quotes and then try again. From your query it looks like you are trying to insert values of a single column(multiple rows) Bottomline_Price of tbl_merchant into tbl_product. Is it so?

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 05:03:13

quote:
Originally posted by jafrywilson

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

Why are you putting qoutes against the column name in the select statement?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:04:34

quote:
Originally posted by jafrywilson

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

Try

insert into tbl_product (Bottomline_Price)
select Bottomline_Price from tbl_merchant

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:07:29

quote:
Originally posted by Idera

quote:
Originally posted by jafrywilson

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

Why are you putting qoutes against the column name in the select statement?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

May be the effect of mysql?

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 05:08:26

Without quotes i tried .. But i didn't get the answer

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 05:16:10

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

quote:
Originally posted by jafrywilson

I tried this

insert into tbl_product ("Bottomline_Price")
select "Bottomline_Price" from tbl_merchant

column values did not copied

Why are you putting qoutes against the column name in the select statement?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

May be the effect of mysql?

Madhivanan

Failing to plan is Planning to fail

Is it really that way in MySql?
Then how to insert constant values with an Insert statement?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 05:18:07

quote:
Originally posted by jafrywilson

Without quotes i tried .. But i didn't get the answer

My worst case Guess would is there any data in tbl_merchant?
What does

select count(*) from tbl_merchant

return?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 05:19:04

I got it ... Tnx for all...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 05:20:58

Small doubt two tables have a common column based on that values only i need to get the bottomline_price ..How to do this

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 05:22:31

(5563 row(s) affected)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 05:32:34

insert into tbl_product ("Bottomline_Price")
SELECT Bottomline_Price
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.common_column_name = P.common_column_name
WHERE put your conditions here if any

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 05:45:00

insert into tbl_product ("Bottomline_Price")
SELECT Bottomline_Price
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.mobid = P.mobid

executing this i got this error
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Bottomline_Price'.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 05:59:08

quote:
Originally posted by jafrywilson

insert into tbl_product ("Bottomline_Price")
SELECT Bottomline_Price
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.mobid = P.mobid

executing this i got this error
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Bottomline_Price'.

INSERT INTO tbl_product ("Bottomline_Price")
SELECT M.Bottomline_Price -- you can use either M or P depending upon whether you need data from which table
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.mobid = P.mobid

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 06:43:01

It affects 3871 row(s) only 1094 rows are present in tbl_product..Then how it is possible

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 06:53:46

quote:
Originally posted by jafrywilson

It affects 3871 row(s) only 1094 rows are present in tbl_product..Then how it is possible

That means the mobid thing is repeated in tbl_merchants. You need to filter your records using WHERE condition at the end of query.

INSERT INTO tbl_product ("Bottomline_Price")
SELECT M.Bottomline_Price -- you can use either M or P depending upon whether you need data from which table
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.mobid = P.mobid
WHERE M.somecolumn_tofilterdata = your condition here

OR

INSERT INTO tbl_product ("Bottomline_Price")
SELECT DISTINCT M.Bottomline_Price -- you can use either M or P depending upon whether you need data from which table
FROM tbl_merchant M
INNER JOIN tbl_product P
ON M.mobid = P.mobid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources