How to insert data from a table to an another? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to insert data from a table to an another?

Author

Topic

Aristona
Starting Member

10 Posts

Posted - 2010-05-26 : 15:43:33

Hi,

I have two tables for my personal details. Let's say A and B. These tables have the same coloumns. I want to copy B value into A, but A values will be kept. Values in B table will be inserted without deleting A.

How can I do it? Is it possible with queries?

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 15:52:30

INSERT INTO TableA
SELECT * FROM TableB

EDIT : And, no..it wont delete the existing data in Table A

Aristona
Starting Member

10 Posts

Posted - 2010-05-26 : 20:47:36

Oh, I thought about it but didn't know sql supports it like this.
Thanks alot.

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-26 : 21:49:06

quote:
Originally posted by Aristona

Oh, I thought about it but didn't know sql supports it like this.
Thanks alot.

Can I get your job?

Aristona
Starting Member

10 Posts

Posted - 2010-05-27 : 05:18:11

quote:
Originally posted by Nikhil1home

quote:
Originally posted by Aristona

Oh, I thought about it but didn't know sql supports it like this.
Thanks alot.

Can I get your job?

What do you mean?

Asken
Starting Member

38 Posts

Posted - 2010-05-27 : 05:41:43

Maybe he was referring to the fact that you have time to think? ;-)

Reporting & Analysis Specialist
Helping others helps me get better...

Aristona
Starting Member

10 Posts

Posted - 2010-05-27 : 08:20:46

One more question, what do I need to do if I don't want same values in same coloumn and add (1) to the end of names?

Like,

There is someone called as Aristona in Name coloumn in A table. However, there is someone called Aristona in B table too.

How can I check it and make the value I am getting from table B as Aristona1?

Do I have to use triggers/procedures?

Thanks alot.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-27 : 08:32:39

insert into tableb
select a.* from tablea a
left join tableb on a.commonfield1 = b.commonfield1
where b.commonfield1 is not null
union all
select a.nearlyall*, a.commonfield1 & '1' from tablea a
inner join tableb on a.commonfield1 = b.commonfield1

Aristona
Starting Member

10 Posts

Posted - 2010-05-30 : 09:47:47

quote:
Originally posted by AndrewMurphy

insert into tableb
select a.* from tablea a
left join tableb on a.commonfield1 = b.commonfield1
where b.commonfield1 is not null
union all
select a.nearlyall*, a.commonfield1 & '1' from tablea a
inner join tableb on a.commonfield1 = b.commonfield1

I cannot get it to work. I am trying to change everything myself but it doesn't work. Are you sure this is correct without any bugs?

Aristona
Starting Member

10 Posts

Posted - 2010-05-30 : 09:53:06

quote:
Originally posted by AndrewMurphy

insert into tableb
select a.* from tablea a
left join tableb on a.commonfield1 = b.commonfield1
where b.commonfield1 is not null
union all
select a.nearlyall*, a.commonfield1 & '1' from tablea a
inner join tableb on a.commonfield1 = b.commonfield1

I am having this error;

USE KN_Online
go

The code you posted here.

Errors;
Msg 402, Level 16, State 1, Line 7
The data types varchar and varchar are incompatible in the '&' operator.
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Both tables have same only 1 coloumn called commonfield1 and "Aristona" value.

Kristen
Test

22859 Posts

Posted - 2010-05-30 : 10:16:54

Andrew's brain confusing multiple programming languages I expect

Use "+" instead of "&" to concatenate strings in MS SQL (and "&" to concatenate strings in Basic and a number of other languages)

"The code you posted here"

Andrew's code is only giving you an outline of what to do ... IMHO not wise to use code suggestions here unless you understand them clearly ...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-31 : 04:53:37

I get sloppy when rushing.....(God Bless compilers and Spell Checkers!)...thanks Kirsten.

Aristona
Starting Member

10 Posts

Posted - 2010-06-03 : 12:01:09

Thank you both, however I am still getting this error.

The code I use:

insert into tableb
select a.* from tablea as a
left join tableb on a.coloumn1 = TABLEB.coloumn1
where TABLEB.coloumn1 is not null
union all
select a.*, a.coloumn1 + '1' from tablea a
inner join tableb on a.coloumn1 = TABLEB.coloumn1

The error I get:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Both tables has only one coloumn called "Coloumn1".

What am I doing wrong?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-03 : 12:09:11

Did you notice the a.nearlyall* from Andrew's reply.
If you have just one column, remove a.* and just use the name of column

insert into tableb
select a.coloumn1 from tablea as a
left join tableb on a.coloumn1 = TABLEB.coloumn1
where TABLEB.coloumn1 is not null
union all
select a.coloumn1 + '1' from tablea a
inner join tableb on a.coloumn1 = TABLEB.coloumn1

Aristona
Starting Member

10 Posts

Posted - 2010-06-03 : 20:08:24

Hello, thanks for the replies again, but the script isn't like I wanted!

I am trying to do;

All values (except existing ones) in a table will be copied to other table. If there are same values, it will add 1 and insert that.
Let's say an user registration system. I don't want 2x same named accounts like "Aristona" when I combine both tables, so one value has to be Aristona1.

Like;

TABLE A
-AAA
-BBB

TABLE B
-AAA
-CCC

When these tables combine, it has to be; (They are not coloumns, just values.)

TABLE B
-AAA
-AAA1
-BBB
-CCC

Current script works like,

If "Aristona" does NOT exist in TABLE B (We are going to get datas from TABLE A and insert into TABLE B), query doesn't do anything. If "Aristona" exists in TABLE B, it copies one "Aristona" and one "Aristona1" value.

Like,
-Aristona (Table B already has it.)
-Aristona (Gets from TABLE A)
-Aristona1 (Gets from TABLE A)

If Table B doesn't have "Aristona" value, it doesn't get anything from TABLE A.

Hope I could explain.

Thanks for your efforts guys!

Aristona
Starting Member

10 Posts

Posted - 2010-06-04 : 05:21:39

Replaced is not null with is null and it was fixed.

Thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources