Solved

sql add records to an existing table from a temp table

Posted on 2011-09-27
6
204 Views
Last Modified: 2012-05-12
I have a temporary table called temptransferdetail. There are several records in this table that have a column of information that I want to add into an existing table called products. I can select these records by the length of the column that I want to copy by:

select
distinct product
from temptransferdetail
where LEN(product) > 4

Open in new window


How can I copy that into the products table. The products table is two column, ID and Product. I want to copy the text from the above select into the Product column of the Products table.

thanks
0
Comment
Question by:wiggy353
  • 3
  • 2
6 Comments
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
ID: 36711619
try this:

insert into myTable (product)
select
distinct product
from temptransferdetail
where LEN(product) > 4
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36711628
and hoping id is identity column that you do not need to populate...
0
 
LVL 1

Author Comment

by:wiggy353
ID: 36711679
id is identity column set to auto increment, but it still will not allow that insert into statement. It says that the id column cannot be null.
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 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36711694
If the ID column in the Products table has the IDENTITY property, you can simply do this:

INSERT INTO Products(Product)
SELECT DISTINCT product
FROM temptransferdetail
WHERE LEN(product) > 4

If not, do this:

Find out what the MAX ID for products is:

DECLARE @MAXID INT

SELECT @MAXID = MAX(ID) FROM Products

INSERT INTO Products(ID, Product)
SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY product) + @MAXID, product
FROM temptransferdetail
WHERE LEN(product) > 4

Greg

0
 
LVL 1

Author Closing Comment

by:wiggy353
ID: 36711875
The ID column was set to identity so the first suggestion should have worked, but for whatever reason it did not. Therefore I used the second suggestion. Thanks.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36712193
it did not because you need to set insert identity on :)

SET IDENTITY_INSERT myTable ON;

insert statements here...

SET IDENTITY_INSERT myTable ON;



0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Both in life and business – not all partnerships are created equal. Spend 30 short minutes with us to learn:   • Key questions to ask when considering a partnership to accelerate your business into the cloud • Pitfalls and mistakes other partners…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

911 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

18 Experts available now in Live!

Get 1:1 Help Now