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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:48 AM
Points: 44, Visits: 132
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 @ 12:34 PM
Points: 3,330, Visits: 7,185
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:48 AM
Points: 44, Visits: 132
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 @ 12:34 PM
Points: 3,330, Visits: 7,185
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:48 AM
Points: 44, Visits: 132
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:48 AM
Points: 44, Visits: 132
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 @ 12:34 PM
Points: 3,330, Visits: 7,185
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:48 AM
Points: 44, Visits: 132
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 @ 12:34 PM
Points: 3,330, Visits: 7,185
You can create it on temdb and use it from any other database by using the 3-part name (tempdb.dbo.Tally)


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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