Solved

T-SQL Best Practice Insert Statement

Posted on 2010-11-16
6
305 Views
Last Modified: 2012-05-10
I'm looking for the BEST WAY based on performance to reinsert all records from one table to another table only changing the values of one column.. My initial thought is to create a temp table and put the results of my query in that and then update the column and then insert the data back into the original table.. Would this be the best way?
0
Comment
Question by:cheryl9063
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 34148805
Here's an epic article on the INSERT statement:  http://www.sqlservernation.com/home/sql-101-the-insert-statement.html

basically, you can just use a SELECT statemetn to do the insert so you don't have to do the insert into temp table and update.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34148865
You have a confused question

...to reinsert all records from one table to another table...

... insert the data back into the original table..

So does it end up in the original, or another table??

If it ends up in the original table, isn't that just UPDATE?

update tbl set col = 'X'

If it is to another table

insert target (col, col2, colC) select scol, 'X', scolC from source

(here 'X' is substituted for the value of col2)
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 34148935
That article does not address my issue and it's not about how to do an insert... I don't think I explained myself well which is always my problem.. Below is the code to create the table I'm lookin at.. Below that is what I need it to look like.. This is just test data in reality it's much much bigger..
INSERT INTO [CORE].[tblMatrix]

([nProjectID], [nSiteID], [nRoleID], [nTeamID], [nIssueID], [nActionID], [nEditModeID], [dSLANumHours], [bBundleAction], [nPriorityID])



SELECT 3, 1, 6, 1, 282, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 433, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 433, 3, 0.00, 0, 2





-----------------------------------------------------------------



INSERT INTO [CORE].[tblMatrix]

([nProjectID], [nSiteID], [nRoleID], [nTeamID], [nIssueID], [nActionID], [nEditModeID], [dSLANumHours], [bBundleAction], [nPriorityID])

SELECT 3, 1, 6, 1, 282, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 282, 433, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 3, 1, 6, 1, 287, 433, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 282, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 282, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 282, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 282, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 282, 433, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 287, 4, 1, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 287, 5, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 287, 8, 1, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 287, 39, 3, 0.00, 0, 2 UNION ALL

SELECT 5, 1, 6, 1, 287, 433, 3, 0.00, 0, 2

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:cheryl9063
ID: 34149101
It goes into the same table.. It's not an update becuase I need the old data in the same table AND the new data.. There are about 500 rows.. I just need to do an insert BUT I want to use the existing rows and only change 1 column value from 3 to 5 for all 500 rows.. I would then have 1000 rows that look alike except for 1 column..
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34149175
All you want is

select 5, [nSiteID], [nRoleID], [nTeamID], [nIssueID], [nActionID], [nEditModeID], [dSLANumHours], [bBundleAction], [nPriorityID]
from tblMatrix
where [nProjectID] = 3
0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 34149254
That was easy.. Thanks!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dimension table indexes 8 13
sql Total query 2 17
1 FROM DUAL wont work with additional columns ?? 4 25
SQL Server merge records in one table 2 12
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now