How to create a set of records based on a SELECT? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to create a set of records based on a SELECT?

Author

Topic

Starlight
Starting Member

14 Posts

Posted - 2014-10-10 : 16:18:11

I have a set of records that will always be the same. A template if you wanna call it that.

"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"

This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.

Can someone please help me writing the SQL statement?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 16:33:38

Just replace the columns that the user changed, like this:
INSERT INTO TABLE (Field1, Field2, Field3, Field4, Field5, Field6, Field7 )
SELECT 'changed this column', Field2, Field3, Field4, Field5, 'another change', Field7
FROM TABLE
WHERE Field2 = 000

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Starlight
Starting Member

14 Posts

Posted - 2014-10-13 : 16:38:55

Thanks. I also need to be able to populate the ID field (it's not auto increment, have to do it myself) The ID fields of this 20 record set needs to start with the highest, most recent ID value.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 08:47:04

create a sequence:


CREATE SEQUENCE dbo.NextTableID
    as BIGINT -- or INT, depending on your requirements
    MINIMUM 1
    INCREMENT BY 1;
GO

Then use it in your insert:


INSERT INTO TABLE (ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 )
SELECT NEXT VALUE FOR dbo.MyTableId, 'changed this column', Field2, Field3, Field4, Field5, 'another change', Field7 
FROM TABLE 
WHERE Field2 = 000

Starlight
Starting Member

14 Posts

Posted - 2014-10-14 : 14:25:10

Thanks. Will I be able to run this SQL code in a C#/ASP.NET program?

Starlight
Starting Member

14 Posts

Posted - 2014-10-14 : 14:40:05

So this is what I have so far:

CREATE SEQUENCE dbo.NextTableID
as BIGINT
MINIMUM 1
INCREMENT BY 1;

GO

INSERT INTO Table (ID, ACCOUNT_NUM, STATE, CITY, INVOICE_NUM)
SELECT NEXT VALUE FOR dbo.MyTableId, '000', STATE, CITY, INVOICE_NUM
FROM TABLE
WHERE ACCOUNT_NUM = '000'

I keep receiving this error:

"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MINIMUM'."

To be honest, I'm not sure if I have this statement right as Im confused to how it all works.

Also what do you mean by 'changed this column' and 'another change'?

Starlight
Starting Member

14 Posts

Posted - 2014-10-14 : 17:03:19

OK, I'm pretty damn close. The following query does exactly what I want it to do, with the exception of incrementing the ID field. It gets the next highest value, but sets all 20 records to that value.

INSERT INTO Table (ID, ACCOUNT_NUM, STATE, CITY, INVOICE_NUM)

SELECT (SELECT MAX(ID) + 1 FROM Table), '000', STATE, CITY, INVOICE_NUM
FROM Table
WHERE ACCOUNT_NUM = '00000'

So for instance, if the next ID is 1407, it will set 1407 to EVERY value for ID. This needs to increment to 1408, 1409, 1410, 1411, etc

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 08:42:38

Sorry, 'MINIMUM' in CREATE SEQUENCE should be 'MINVALUE' (Btw did you look it up?)

USING a sequence should always be faster than the MAX(ID) + 1 appproach

Starlight
Starting Member

14 Posts

Posted - 2014-10-15 : 09:10:35

Yeah I figured out the MINVALUE yesterday before you responded.

I figured it out. This works, and manually populates the ID field:

INSERT INTO Table (ID, ACCOUNT_NUM, STATE, CITY, INVOICE_NUM)

SELECT (SELECT MAX(ID) FROM Table) + row_number() over (order by(select null)), '000', STATE, CITY, INVOICE_NUM
FROM Table
WHERE ACCOUNT_NUM = '000'

However when I try to fire it from my C# program, I receive an invalid syntax error. Any ideas??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources