T-SQL help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » T-SQL help

T-SQL help

Rate Topic

Display Mode

Topic Options

Author

Message

Debora

Posted Monday, September 10, 2012 5:08 PM

Valued Member

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:45 PM
Points: 60, Visits: 202

I have two identical tables. Table 1 has 12,000 rows and Table 2 has 11,000 rows. I need to insert rows from Table 2 on Table 1 that don’t exist on Table 1. What is a quick way of accomplishing this?

Post #1357060

GilaMonster

Posted Monday, September 10, 2012 5:10 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 37,734, Visits: 29,999

Insert into Table1 (<column list>)
Select <columns> from Table2
WHERE <primary key column> NOT IN (select <primary key column> FROM Table1)

or

Insert into Table1 (<column list>)
Select <columns> from Table2
EXCEPT
Select <columns> from Table1

Roughly.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1357062

CapnHector

Posted Monday, September 10, 2012 5:15 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

could also use something like:

insert into table1 (Values)
SELECT values
  FROM table2
  LEFT JOIN table1
    ON table2.somecolumn = table1.somecolumn
WHERE table1.somecolumn IS NULL

at lease i think since we dont have DDL and Sample data its hard to get tested code

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1357063

Mark Eckeard

Posted Monday, September 10, 2012 5:21 PM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 30, 2013 12:00 PM
Points: 82, Visits: 348

Look at except:

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Mark

Post #1357064

Lynn Pettis

Posted Monday, September 10, 2012 5:58 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

Mark Eckeard (9/10/2012)

Look at except:

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Mark

If you are going to post a url, at least make it usuable.

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1357071

CELKO

Posted Monday, September 10, 2012 11:29 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

MERGE statement.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1357142

« Prev Topic | Next Topic »

Permissions