Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert into question Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 9:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 3:06 AM
Points: 50, Visits: 138
Hello
I need to insert records into a table, like this

TABLE BC
REF | Design | code
001 | teste |0001
001 | teste |0002
001 |teste |00xx , until it reaches 9999

with insert into command i only can insert a record of a time, is there another like in a cicle until code=9999

Thanks in advance
Post #1525065
Posted Friday, December 20, 2013 9:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 3,805, Visits: 8,556
Hi,
There's no need for a loop. You just need to use a Tally Table. To know what it is and how it replaces a loop, read the following: http://www.sqlservercentral.com/articles/T-SQL/62867/

With your tally table, your code would end like this:

INSERT INTO BC
SELECT '001',
'teste',
RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1525071
Posted Friday, December 20, 2013 10:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 3:06 AM
Points: 50, Visits: 138
hi

After creating the tally table, used this, but gives me a error on the select

INSERT INTO BC (ref,design,codigo)
values ("001","teste", SELECT '001',
'teste',
RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999)


thanks for you reply
Post #1525090
Posted Friday, December 20, 2013 10:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 3,805, Visits: 8,556
That's because you're still using the VALUES clause when you should only use the SELECT.
For more information on INSERT INTO: http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx Check the part Inserting data from other tables



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1525093
Posted Friday, December 20, 2013 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 3:06 AM
Points: 50, Visits: 138
i put
INSERT INTO BC
SELECT '001','teste', RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999

and returns this error msg

Erro: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'BC'.

table bc exists
Post #1525104
Posted Friday, December 20, 2013 10:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 3:06 AM
Points: 50, Visits: 138
if i do

select * from tally

returns all the numbers ok 1,2,....11000
Post #1525107
Posted Friday, December 20, 2013 10:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 3,805, Visits: 8,556
You need to be sure you're in the correct instance.
To be sure you're inserting on the right table, you could use a 3 part name: [db_name].[schema_name].[table_name]



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1525108
Posted Friday, December 20, 2013 11:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 3:06 AM
Points: 50, Visits: 138
ok many thanks, works fine, e needed to create table tally on my database not in tempdb like in the example


Post #1525112
Posted Friday, December 20, 2013 11:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 3,805, Visits: 8,556
You can create it on temdb and use it from any other database by using the 3-part name (tempdb.dbo.Tally)


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1525116
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse