need help with sql query to insert a new row

Question

need help with sql query to insert a new row

Farhan Jamil 421

Hi Guys

Not sure how do i proceed here . So I want to right sql query which inserts new record

If you see in the screenshot the second row is update which i can do it. No issues at all.

Regarding the third line which is insert into an existing table i am not sure how to do it.

The issue is pkdimlocationid is identity column and rest of the values are duplicate values.

So i need help in inserting record which starts with pkdimlocationid=93.

I did start the SQL code (but i lost track)which is

SELECT TOP 2 * into #temp
FROM [DimLocation]
set identity_insert[#temp] on

insert into #temp
select max(pkdimlocationid)+1 as pkdimlocationid,
code,name,type,status='Closed',L1,L2,L3,L4,L5,L6,L7,L8,L9,StDate = '2020-11-05',EdDate='2020-12-02',updatedon=cast(getdate() as date)
from DimLocation a
where code=1
group by
code,name,type,status,L1,L2,L3,L4,L5,L6,L7,L8,L9,StDate,EdDate,updatedon

set identity_insert[#temp] off

Any help would be appreciated

Screenshot:-

Farhan Jamil

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-11-04T22:36:59.573+00:00

I am afraid I don't follow here. What you are trying to achieve? You have data in DimLocation and want to insert them into #temp?

Why does #temp have the IDENTITY property when you insert explicitly values? Or the other way round: why do you want to insert explicit values in the IDENTITY column?

For this type of question, it often helps if you post CREATE TABLE statements for your tables together with INSERT statements with sample data and desired result given the sample data. This helps to clarify what you want to do and it makes it easy to copy and paste into a query window to develop a tested solution. It also helps if you briefly explain the underlying business rules so that we know why you want that solution.
Guoxiong 8,206 Reputation points

2020-11-04T23:33:11.59+00:00

Not clear to your question. It seems that you want to update the table DimLocation. But your question is about the INSERT statement. Can you please provide more details?
Farhan Jamil 421 Reputation points

2020-11-05T00:10:17.597+00:00

HI GuoxiongYuan

Apologies for my wrong sentence.basically i meant i have to do insert in my Dimlocation table . I have 700 records to insert and one of the example where an iinsert has happened in the table is described in capture-1.png.

The earlier screenshot which is capture.png is an example where I have to write insert statement(in this screenshot I have mentioned what record I have to insert) and achieve the desired output like the one you see in capture-1.png.

Regards
Farhan Jamil
Ian Bates 91 Reputation points

2020-11-05T00:26:12.467+00:00

@Farhan Jamil , is the issue mainly around populating the primary key column? If so that key looks like it is probably an auto-number and you do not need to worry about populating it as it will populate itself when you insert the new rows. You can check the table structure to see if it is an autoid.
Farhan Jamil 421 Reputation points

2020-11-05T00:36:05.98+00:00

Hi Ian

I am not sure. i am confused. If you are willing then tell me how would you write a sql statement to do insert for row number 3. see screenshot titled capture.png The only hardcode value i need to do while writing insert statement are the date columns and the status column. Rest are all duplicate values apart from pkdimlocationid which is an auto increment .

I am not sure whether i am able to explain the requirement correctly or not

Regards
Farhan Jamil

Accepted answer

1 additional answer

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-11-04T22:36:59.573+00:00

I am afraid I don't follow here. What you are trying to achieve? You have data in DimLocation and want to insert them into #temp?

Why does #temp have the IDENTITY property when you insert explicitly values? Or the other way round: why do you want to insert explicit values in the IDENTITY column?

For this type of question, it often helps if you post CREATE TABLE statements for your tables together with INSERT statements with sample data and desired result given the sample data. This helps to clarify what you want to do and it makes it easy to copy and paste into a query window to develop a tested solution. It also helps if you briefly explain the underlying business rules so that we know why you want that solution.
Guoxiong 8,206 Reputation points

2020-11-04T23:33:11.59+00:00

Not clear to your question. It seems that you want to update the table DimLocation. But your question is about the INSERT statement. Can you please provide more details?
Farhan Jamil 421 Reputation points

2020-11-05T00:10:17.597+00:00

HI GuoxiongYuan

Apologies for my wrong sentence.basically i meant i have to do insert in my Dimlocation table . I have 700 records to insert and one of the example where an iinsert has happened in the table is described in capture-1.png.

The earlier screenshot which is capture.png is an example where I have to write insert statement(in this screenshot I have mentioned what record I have to insert) and achieve the desired output like the one you see in capture-1.png.

Regards
Farhan Jamil
Ian Bates 91 Reputation points

2020-11-05T00:26:12.467+00:00

@Farhan Jamil , is the issue mainly around populating the primary key column? If so that key looks like it is probably an auto-number and you do not need to worry about populating it as it will populate itself when you insert the new rows. You can check the table structure to see if it is an autoid.
Farhan Jamil 421 Reputation points

2020-11-05T00:36:05.98+00:00

Hi Ian

I am not sure. i am confused. If you are willing then tell me how would you write a sql statement to do insert for row number 3. see screenshot titled capture.png The only hardcode value i need to do while writing insert statement are the date columns and the status column. Rest are all duplicate values apart from pkdimlocationid which is an auto increment .

I am not sure whether i am able to explain the requirement correctly or not

Regards
Farhan Jamil

Answer 1

Farhan Jamil 421

Hey Guys

I think I figured it out how to do insert here. It drived me crazy all day. But here is the solution. This is exactly what i was looking for (see capture.png)

insert into #temp
select
code
,Name
,Type
,Status='Close'
,L1
,L2
,L3
,L4
,L5
,L6
,L7
,L8
,L9
,StDate='2020-11-05'
,EdDate = '2020-12-02'
,UpdatedOn = cast(getdate() as date)
from #temp
where pkDimLocationId in(1)

Answer 2

Hi Erland

This is just a sample. I have about 700 records in the data in dimlocation which i have to update.

Inorder to make sure I am preparing my sql statement correct,I am taking the two sample into a temp table and then preparing my insert statement to make sure that it is correct before running the actual insert statement for all records.

The issue is if you can see in the screenshot , I have pkdimlocationid which is an identity column and apart from that none of the columns are unique which i can use to insert the record.

The first two records are you see in the screenshot is already present in the database. I need to insert the third one.

here is another screenshot . This is what i am trying to achieve. So i have to create a script to update 700 records something in this manner.

Henceforth I started taking all this into a temp table and then updating the record.

Share via

need help with sql query to insert a new row

1 additional answer

Your answer