Solved

sql add records to an existing table from a temp table

Posted on 2011-09-27
6
211 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL NULL vs Blank 26 35
ms sql + get number in list out of total 7 26
sql 2008 how to table join 2 11
While in ##Table - Help 4 10
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 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

8 Experts available now in Live!

Get 1:1 Help Now