Insert into table

Question

Insert into table

wikus

SSC Veteran

Points: 256
More actions
November 14, 2016 at 1:33 am

#322984

Hi,
I would appreciate some help please.
I want to do a select distinct only on Col2 and add rows for those.
INSERT INTO Table_1(Col1, Col2, Col3,Col4, Col5)
SELECT DISTINCT Col1, Col2, 'ZZZ', 1, Col5
FROM Table_1
The code above adds records for all rows and not distinct for Col2.
Thanks
Wikus

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

wikus (11/14/2016)
Hi,
I would appreciate some help please.
I want to do a select distinct only on Col2 and add rows for those.
INSERT INTO Table_1(Col1, Col2, Col3,Col4, Col5)
SELECT DISTINCT Col1, Col2, 'ZZZ', 1, Col5
FROM Table_1
The code above adds records for all rows and not distinct for Col2.
Thanks
Wikus

Can you please post the DDL (create table) script, sample data as an insert statement and the expected results.

😎

Your query will bring back the distinct set of values in columns Col1, Col2 and Col5.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Distinct applies to the entire row, not individual columns.

If there are multiple values of Col1 and Col5 for a single (repeated) value in Col2, what do you want inserted?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

wikus SSC Veteran Points: 256 More actions · Answer 3

CREATE TABLE [dbo].[Table_1](

[Col1] [nchar](10) NULL,

[Col2] [nchar](10) NULL,

[Col3] [nchar](10) NULL,

[Col4] [nchar](10) NULL,

[Col5] [nchar](10) NULL

) ON [PRIMARY]

INSERT INTO Table_1

VALUES (1,'AAA','xyq',345,'eyrt'),(2,'CCC','frt',456,'kjhg'),(3,'AAA','kkj',367,'uytr'),(4,'DDD','hhh',543,'fdds'),(5,'CCC','xyt',457,'ewqq'),(6,'AAA','iuy',872,'lkji')

The reult should be:

Col1Col2Col3Col4Col5

1 AAA xyq 345 eyrt

2 CCC frt 456 kjhg

3 AAA kkj 367 uytr

4 DDD hhh 543 fdds

5 CCC xyt 457 ewqq

6 AAA iuy 872 lkji

1 AAA ZZZ 1 eyrt

2 CCC ZZZ 1 kjhg

4 DDD ZZZ 1 fdds

wikus SSC Veteran Points: 256 More actions · Answer 4

Thanks for the reply. My second post should clarify what I am trying to do.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

wikus (11/14/2016)
CREATE TABLE [dbo].[Table_1](
[Col1] [nchar](10) NULL,
[Col2] [nchar](10) NULL,
[Col3] [nchar](10) NULL,
[Col4] [nchar](10) NULL,
[Col5] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO Table_1
VALUES (1,'AAA','xyq',345,'eyrt'),(2,'CCC','frt',456,'kjhg'),(3,'AAA','kkj',367,'uytr'),(4,'DDD','hhh',543,'fdds'),(5,'CCC','xyt',457,'ewqq'),(6,'AAA','iuy',872,'lkji')
The reult should be:
Col1Col2Col3Col4Col5
1 AAA xyq 345 eyrt
2 CCC frt 456 kjhg
3 AAA kkj 367 uytr
4 DDD hhh 543 fdds
5 CCC xyt 457 ewqq
6 AAA iuy 872 lkji
1 AAA ZZZ 1 eyrt
2 CCC ZZZ 1 kjhg
4 DDD ZZZ 1 fdds

I think this is kind of what you want, although it is not 100% like your output. There is no default function to get the "first" occurrance of a column value, so I used the MIN function to get the minimum value instead.

SELECT Col1

, Col2

, Col3

, Col4

, Col5

FROM Table_1

ORDER BY Col1

SELECT MIN(col1) as Col1

, Col2

, 'ZZZ' as Col3

, 1 as Col4

, MIN(Col5) as Col5

FROM Table_1

GROUP BY Col2

ORDER BY Col1

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

Here is a suggestion towards a solution

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Table_1') IS NOT NULL DROP TABLE dbo.Table_1;

CREATE TABLE [dbo].[Table_1](

[Col1] [nchar](10) NULL,

[Col2] [nchar](10) NULL,

[Col3] [nchar](10) NULL,

[Col4] [nchar](10) NULL,

[Col5] [nchar](10) NULL

) ON [PRIMARY]

INSERT INTO Table_1

VALUES (1,'AAA','xyq',345,'eyrt'),(2,'CCC','frt',456,'kjhg'),(3,'AAA','kkj',367,'uytr'),(4,'DDD','hhh',543,'fdds'),(5,'CCC','xyt',457,'ewqq'),(6,'AAA','iuy',872,'lkji')

;WITH BASE_DATA AS

(

SELECT

T1.Col1

,T1.Col2

,ROW_NUMBER() OVER

(

PARTITION BY T1.Col2

ORDER BY T1.Col1 ASC

) AS BD_RID

,T1.Col5

FROM dbo.Table_1 T1

)

INSERT INTO dbo.Table_1(Col1,Col2,Col3,Col4,Col5)

SELECT

BD.Col1

,BD.Col2

,'ZZZ' AS Col3

,1 AS Col4

,BD.Col5

FROM BASE_DATA BD

WHERE BD.BD_RID = 1;

SELECT

T1.Col1

,T1.Col2

,T1.Col3

,T1.Col4

,T1.Col5

FROM dbo.Table_1 T1;

Output

Col1 Col2 Col3 Col4 Col5

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

1 AAA xyq 345 eyrt

2 CCC frt 456 kjhg

3 AAA kkj 367 uytr

4 DDD hhh 543 fdds

5 CCC xyt 457 ewqq

6 AAA iuy 872 lkji

1 AAA ZZZ 1 eyrt

2 CCC ZZZ 1 kjhg

4 DDD ZZZ 1 fdds

wikus SSC Veteran Points: 256 More actions · Answer 7

wikus

SSC Veteran

Points: 256

November 14, 2016 at 3:12 am

#1912193

Thank you very much for all the replies.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 8

wikus (11/14/2016)
Thank you very much for all the replies.

You are very welcome.

😎

MMartin1 One Orange Chip Points: 27764 More actions · Answer 9

Just to note, for the answer that you marked as correct ... the second instance where col1=2 has col5 = 'ewqq' and not 'kjhg' as you specified. Eirikur's solution however gives the output that you are looking for. Also from your data, is col1 a key field? If so I would not insert the results back into your table for you would violate the key field.

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