Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL help Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 5:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:53 PM
Points: 61, Visits: 207
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
Posted Monday, September 10, 2012 5:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Monday, September 10, 2012 5:15 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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
Posted Monday, September 10, 2012 5:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:24 AM
Points: 128, Visits: 490
Look at except:

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

Mark



Post #1357064
Posted Monday, September 10, 2012 5:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 20,859, Visits: 32,880
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



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
Posted Monday, September 10, 2012 11:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 1,945, Visits: 3,180
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 »

Add to briefcase

Permissions Expand / Collapse