SQL Insert Data from one table to other table

Question

Post reply

SQL Insert Data from one table to other table

User7766

SSCommitted

Points: 1969
More actions
September 28, 2011 at 12:49 am

#244592

Hi All,
I'm having two databases, I need to run a query in my aspx pageload that checks for any new data from Table A in Database A which is not in Table B in Database B and inserts new data from Table A. Here is my query, But when i use WHERE condition it is not executing properly.
I want to insert only new records from table A which are not in table B.
INSERT INTO ReDB.dbo.Countries (CountryID, Serial, CountryDesc, DateAdded)
SELECT NEWID(), Serial, CountryName, GETDATE()
FROM v8.dbo.Country
WHERE Serial not in (select Serial from ReDB.dbo.Countries)
Can anyone please help me out with correct one.
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sqluserr Old Hand Points: 377 More actions · Answer 1

use this syntax

insert into table_name

select * from tableA

except

select * from tableB

User7766 SSCommitted Points: 1969 More actions · Answer 2

Any solution for this?

The above query is also allowing to insert duplicate records..

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Please will you show us the DDL for both tables (including constraints), and some sample data.

Thanks

John

HowardW One Orange Chip Points: 29109 More actions · Answer 4

I don't see what's wrong with your original query (although my personal weapon of choice is NOT EXISTS)

Do you have duplication within v8.dbo.Country? E.g the same Serial twice? If so, it would try to insert two rows for the same serial and presumably violate your PK?

baabhu SSCertifiable Points: 6205 More actions · Answer 5

The code seems to be good. This will insert all the serials which are not in target database.

Unless if your serial column is not the primary key in both the tables. Then you may have problem.

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 6

Hi,

Try the following logic (NOT EXISTS)

Here EMPID is the Primary key.

INSERT INTO TBL1

SELECT * FROM TBL2 A

WHERE NOT EXISTS (SELECT * FROM TBL1 B

WHERE A.EMPID = B.EMPID)

regards

Palash Gorai

R.P.Rozema SSChampion Points: 12317 More actions · Answer 7

Even if the serial values are unique within each table (for example by a key constraint), there may still be duplicates when trying to insert them. This is due to possible differences in the collation used on each of the tables or the default database collation in either of the databases.

i.e. 'a' = 'A' is true when using a case insensitive collation, but it is not true when using a case sensitive collation. The same thing for accents: 'ë' = 'e' is true if an accent insensitive collation is used, but it is false for a accent sensitive collation.

Current database's default collation can be retrieved using SELECT DATABASEPROPERTYEX(db_name(), 'Collation')

Please run this to see if you databases have the same default collation?

CI = case insensitive, CS = case sensitive

AI = accent insensitive, AS = accent sensitive

Then if you script the table's definition's from SSMS, if there is a non-default collation used on either of the table's columns, a collation clause will be generated for that column. So please provide the DDL for the both tables so we can see if this causes your problem. For yourself, also sp_help on the table will list their columns' collations under 'Collation'.

edit: typing error fixed

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

maruthipuligandla (9/28/2011)
WHERE Serial not in (select Serial from ReDB.dbo.Countries)

The problem might be due to improper aliases.

Try this:

INSERT INTO ReDB.dbo.Countries (CountryID, Serial, CountryDesc, DateAdded)

SELECT NEWID(), Serial, CountryName, GETDATE()

FROM v8.dbo.Country v8_contry

WHERE v8_contry.Serial not in (select ReDB_Country.Serial from ReDB.dbo.Countries ReDB_Country )

Also you could try LEFT JOIN.

INSERT INTO ReDB.dbo.Countries (CountryID, Serial, CountryDesc, DateAdded)

SELECT NEWID(), Serial, CountryName, GETDATE()

FROM v8.dbo.Country v8_contry

LEFT JOIN ReDB.dbo.Countries ReDB_Country

ON v8_contry.Serial = ReDB_Country.Serial

WHERE v8_contry.Serial IS NULL

{Edit: Added left join example and removed extra content from Quote}