CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Brent Connor
Brent Connor

Reputation: 628

SQL Server query results as INSERT

I'm trying to concatenate lastName(3 letters) + firstName(2 letters) to create a new username. When I used this script, it created new records for the concatenation results.

USE [database]
INSERT INTO table (username)
SELECT SUBSTRING(lastName, 1, 3) + SUBSTRING(firstName, 1, 2)
FROM [database].[dbo].table

Could someone show me how to append the original record with the new username?

Upvotes: 1

Views: 49

Answers (4)

Edi G.
Edi G.

Reputation: 2422

Try this: 

USE [database]

UPDATE MyTable
SET USERNAME = SUBSTRING(lastName, 1, 3) + SUBSTRING(firstName, 1, 2)

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

I'm not sure whether I get your question right, but shouldn't the following work?

UPDATE table
SET username = SUBSTRING(lastName, 1, 3) + SUBSTRING(firstName, 1, 2)

This updates the username of every record in the table.

Upvotes: 2

gmiley
gmiley

Reputation: 6604

You want an update instead of an insert. The following will update all records with the new username. If you want a more specific update for just a single record, you will need a WHERE clause to identify the particular rows.

UPDATE table
SET username = SubString(lastName, 1, 3) + SubString(firstName, 1, 2);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you want to just change the user name on the existing records, use update instead of insert:

UPDATE table
   SET username = SUBSTRING(lastName, 1, 3) + SUBSTRING(firstName, 1, 2);

If you want to insert new rows for the same users with more columns, add them to the insert:

INSERT INTO table (username, col1, col2, . . ., coln)
SELECT SUBSTRING(lastName, 1, 3) + SUBSTRING(firstName, 1, 2),
       col1, col2, . . ., coln
FROM [database].[dbo].table;

Upvotes: 1

Related Questions