SQL Server - Insert Into

Hi,

I'm trying to insert data from:
DataTable

To:
InsertTable


I've created the following example:


create table dbo.DataTable
(
      id int,
      date datetime,
      class varchar(50),
      value int
)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Apples', 1)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Oranges', 2)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Bananas', 3)



create table dbo.InsertTable
(
      id int,
      date datetime,
      apples int,
      oranges int,
      bananas int
)

insert into dbo.InsertTable(id, date)
select id, date
from dbo.DataTable
where id = 50

-- This does not work :-(

insert into dbo.InsertTable(id, date, apples)
(select id, value
from dbo.DataTable
where id = 50 and class = 'Apples')
where id = 50

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

Am I doing this wrong, or is it just a simple syntax mistake?

Thank you !! :-)
EndelmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sm394Commented:

Number of parameters are not correct
something like this
insert into dbo.InsertTable(id,  apples)
(select id, value
0
EndelmAuthor Commented:
Sorry, I corrected the syntax:

drop table dbo.InsertTable
drop table dbo.DataTable

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


create table dbo.DataTable
(
      id int,
      date datetime,
      class varchar(50),
      value int
)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Apples', 1)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Oranges', 2)

insert into dbo.DataTable(id,date, class,value)
values(50, '2007-01-01', 'Bananas', 3)



create table dbo.InsertTable
(
      id int,
      date datetime,
      apples int,
      oranges int,
      bananas int
)

insert into dbo.InsertTable(id, date)
select distinct id, date
from dbo.DataTable
where id = 50

-- This does not work :-(

insert into dbo.InsertTable(id, date, apples)
(select id, value
from dbo.DataTable
where id = 50 and class = 'Apples')
where id = 50


select *
from dbo.InsertTable

select *
from dbo.DataTable

0
chapmandewCommented:
insert into dbo.InsertTable(id, date, apples)
select id, getdate(), value
from dbo.DataTable
where id = 50 and class = 'Apples'
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

sm394Commented:
or
insert into dbo.InsertTable(id, date, apples)
(select id, date ,value
from dbo.DataTable
where id = 50 and class = 'Apples')
where id = 50
0
chapmandewCommented:
or if a field:

insert into dbo.InsertTable(id, date, apples)
select id, date, value
from dbo.DataTable
where id = 50 and class = 'Apples'
0
Kevin CrossChief Technology OfficerCommented:
Are the 3 fields you left out of the insert statement set to allow NULLs?  

In other words, you are inserting id and date but leaving out values for apples, oranges, and bananas.  If you have these set to NOT be null and you have not specified default value in table definition, you will have to pass values here.

What error are you getting when you say "this does not work".
0
Kevin CrossChief Technology OfficerCommented:
Think I understand, you are wanting to update count.
insert into dbo.InsertTable(id, date)
select distinct id, date
from dbo.DataTable
where id = 50
 
-- This does not work :-(
 
UPDATE i
SET i.apples = CASE d.class WHEN 'Apples' THEN d.value END
, i.oranges = CASE d.class WHEN 'Oranges' THEN d.value END
, i.bananas = CASE d.class WHEN 'Bananas' THEN d.value END
FROM dbo.InsertTable i
INNER JOIN dbo.DataTable d
ON d.id = i.id AND d.date = i.date

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
You could also do in one statement like this:
INSERT INTO dbo.InsertTable(id, date, apples, oranges, bananas)
SELECT id, date
, SUM(CASE class WHEN 'Apples' THEN value ELSE 0 END)
, SUM(CASE class WHEN 'Oranges' THEN value ELSE 0 END)
, SUM(CASE class WHEN 'Bananas' THEN value ELSE 0 END)
FROM dbo.DataTable
WHERE id = 50 /* can remove this and do entire table at once per grouping */
GROUP BY id, date

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EndelmAuthor Commented:
InsertTable:

id          date                    apples      oranges     bananas
----------- ----------------------- ----------- ----------- -----------
50          2007-01-01 00:00:00.000 NULL        NULL        NULL


DataTable:

id          date                    class                                              value
----------- ----------------------- -------------------------------------------------- -----------
50          2007-01-01 00:00:00.000 Apples                                             1
50          2007-01-01 00:00:00.000 Oranges                                            2
50          2007-01-01 00:00:00.000 Bananas                                            3


 

How can you insert data from InsertTable to DataTable?

Thank you.

0
EndelmAuthor Commented:
Sorry,

I mean, How can you insert data from DataTable to InsertTable ?

Thank you.
0
Kevin CrossChief Technology OfficerCommented:
See this http:#22885954.
0
EndelmAuthor Commented:
Oops. I just need to update the table, not insert :-)

Thanks you guys!
0
Mark WillsTopic AdvisorCommented:
Cannot insert into an existing table - either update it, or, do not create it to begin with. Take out the "into" and match the column names if you are creating it first...
0
Kevin CrossChief Technology OfficerCommented:
I showed update here already -- http:#22885922.
0
Mark WillsTopic AdvisorCommented:
Sorry bout that - didn't do any lookup of links, just saw the first lot and thought I would jump to the end and point out the obvious...
0
Kevin CrossChief Technology OfficerCommented:
No sweat, Mark.  I was actually responding to Endelm's comment that wanted to do update instead of insert.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.