I need to write some insert statements, 1 per table, ~100 tables, all having the approximante form:
Select Into TableA Select * From TableB
Except that I need explicit statements:
Select Into TableA Col1, Col2, Col3, ... ColN Values ...
The reason is that I need to preserve the current identity values (it's a replication setup scenario). I can set Identity_Insert On, but then it wants the explicit column names and values.
Is there a wizard or utility that will generate the statements for me? With
100 tables in the db, I'm not looking forward to writing it all :-)
TIA,
Arthur
PS. Given that it's Easter weekend, if you have an answer could you please e me directly? Thanks!
I rememeber they used to have this option to generate data script for the table in SQL 2000, but I can not find it in SQL 2005. I need to move one table from one database to another, but I need to generate SQL Insert Statements...
I'd I have a problem I'd like to post CREATE TABLE and INSERT statementsthat will create my table and insert data into the table.I can use the scripting feature in Enterprise Manager to generate CREATETABLE scripts.Is there a script I can run that will generate INSERT statements so I caninclude sample data.Thanks
How are you guys doing? Hope all is well. This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table. Is there a way for the insert statements to be merged so that I won't be executing two statements? Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn) Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)
i'm quite new with sql and i have an question about het insert statements. Is the order of the insert statements from any importance? and why is/isn't it?
does anyone know if there is a way to generate an insert script basedon the records in a table? I want to distribute the contents of acouple of tables to customers without having to use BCP or DTS.Greetings Sjaak
I am creating my companys' database and I have a small problem that must be solved.
I have a pictures table: PicturesTable ------------- ProductID int ForeignKey Picture nvarchar(30) ...
(A product can have many pictures & the ProductID is unique for any product, but not for the table of pictures).
What I want to do is to somehow do a procedure to: 1) Check if any images (for a productID) exists in the table 2) if they do not exist then add the appropriate images into the table 3) if the images exist, then update the images with the new one that I have.
What I thought was to just delete all the images from the table for the specific product:
DELETE FROM PicturesTable WHERE ProductID = '10-11'
and then add the appropriate images: INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic1.gif') INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic2.gif') INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic3.gif')
but I do not like a lot this idea because if a user tries to read the pictures for that product (at the same time I was deleting them) s/he would get nothing. Is any other way that I can do it please?
Hi Friends, I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table. I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)
I would like to get opinions about the code below and what I can do to improve it. I don't know if I am using the best techniques in this code. I am not running into any problems, but I am assuming there has to be cleaner ways of doing this.
Also I am trying to figure out why the INSERT INTO statement in the query is giving me the error: "The column prefix '#ttsku' does not match with a table name or alias name used in the query."
The purpose of the code is to select all the item records from a linked server (Non MS SQL) and bring it into a temp table. I did this because I can't create a cursor to the other DB.
Using this temp table, look if the SKU table has the item in it, if it does then update the record, otherwise I need to create the record and fill in the values from the temp table.
Finally I need to delete any records that don't exist in the temp table.
Here is the code:
SELECT pt_mstr.pt_part as part, pt_mstr.pt_desc1 as desc1, dbo.udf_GetEntry(cd_det.cd_cmmt,1,';') as custdesc, dbo.udf_GetEntry(cd_det.cd_cmmt,2,';') as caformat, dbo.udf_GetEntry(cd_det.cd_cmmt,3,';') as plformat, dbo.udf_GetEntry(cd_det.cd_cmmt,6,';') as eaformat, (pt_mstr.pt__qad24 * pt_mstr.pt__qad25) as pallqty, case when um_mstr.um_conv is not null then round((pt_net_wt / (cast(pt_drwg_loc as numeric) /um_conv)),0) else round((pt_net_wt / cast(pt_drwg_loc as numeric)),0) end as packqty, pt_mstr.pt_drwg_loc as packsize, pt_mstr.pt_drwg_size as packum, dbo.udf_GetEntry(cd_det.cd_cmmt,4,';') as dist1, dbo.udf_GetEntry(cd_det.cd_cmmt,5,';') as dist2, pt_mstr.pt_user2 as brand, pt_mstr.pt__qad24 as ti, pt_mstr.pt__qad25 as hi, pt_mstr.pt_status as status INTO #ttsku FROM mfgprod..pub.pt_mstr pt_mstr left join mfgprod..pub.cd_det cd_det on (cd_det.cd_ref = pt_mstr.pt_part and cd_det.cd_type = 'MK' and cd_det.cd_lang = 'US' and cd_det.cd_seq = 0) left join mfgprod..pub.um_mstr um_mstr on (um_mstr.um_um = pt_mstr.pt_net_wt_um and um_mstr.um_alt_um = pt_drwg_size and um_mstr.um_part = '') WHERE pt_part_type = 'FG' and (pt_status = 'A' or pt_status = 'AMTO') and (pt_drwg_loc <> '' and pt_drwg_loc <> '0')
declare c_part cursor for select * from #ttsku
open c_part
fetch next from c_part
while @@fetch_status = 0 begin
if exists (select * from sku where sku.part = #ttsku.part) BEGIN update sku set sku.Desc1 = #ttsku.desc1, sku.CustDesc = ##ttsku.custdesc where sku.part = #ttsku.part END ELSE BEGIN insert into sku (sku.part, sku.desc1) select #ttsku.part, #ttsku.desc1 END
fetch next from c_part
END -- while
close c_part deallocate c_part
DELETE FROM sku WHERE not exists (select * from #ttsku where #ttsku.part = sku.part)
Hello all. Got bit of a long winded question here...........so here we go lol.
OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:
For most records this generates a correct insert statement.........for example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');
However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');
can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.
I'm using version 3.5.5386.0 of SqlServerCompact Edition for windows mobile.
My first tests indicates that an insert statement with '?' ist 20 % faster than with '@p1' parameters:
"INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (?,?,?,?,?)" "INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (@P1,@2,@3,@P4,@P5)"
I've no explanation for that. I reported some problems with the '?' parameters in my other task http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2715685&SiteID=1 So I'm a little bit afraid to use this version.
Is there a recommandation which version to use for Inserts?
I want to prepare insert statements for the data of all tables in my database. I want this to create seed data. Just by using those scripts i can insert the data in another db. Please suggest ho wto prepare in easy way.
I have a question regarding generating SQL script from a database. If you use SQL Server Management Express, it lets you generate SQL script that can be used to recreate table structure, stored procedures, etc. But it does not include the actual data stored in the tables.
Is it possible to export data from tables to SQL insert statements? Got SQL 2005 developer, visual studio 2005, and Visio enterprise architect, if that makes any difference. I already found this tool but it costs money.
I'm using the following code to add some data to a table: Dim rand As Random = New Random Dim num As Int32 = rand.Next(10000000) Dim strConn as string = "......." Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')" Dim conn as New SQLConnection(strConn) Dim Cmd as New SQLCommand(sql, conn) Try conn.Open() Catch SQLExp as SQLException Response.Write ("An SQL Server Error Occurred: " & e.toString()) Finally cmd.ExecuteNonQuery conn.Close() End Try
As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss. If youd like to see more code let me know.... Thanks, Scott
Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated
Our existing DW's ETL was written in a very complex fashion by the previous team. They use DTS package lookups to read a row in the Source SQL Server database see if that row exists in the taget SQL Server database. If the row does not exist, they use ActiveX scripts to INSERT the row in the target SQL Server database. If it exists, they update the row on the target side. How would you do this in SSIS? Apologize if this sounds like a basic question, however, I would have done this via Stored Procedures or SQL Scripts especially since it involves SQL Servers alone. Appreciate any help.
I am looking to transfer a database to another db but I just need to copy the data (not drop the tables and recreate them). Is this possible?
I was thinking of backing up the database in a set of insert statements in a file and execute this file to get the desired result. Or alternatively, to use a data flow task with multiple tables if possible.
I have a file with about 600,000 lines of insert statement given to me by a developer. There are basically 5 inserts into different tables for each Product or Item. Each Insert MUST run in the order specified and must complete before the next insert runs,
To complicate things further, there are triggers that fire on every insert and each trigger must complete its transaction before the next insert starts.
What is the best and most efficient way to run the inserts, while ensuring that each statement completes before the next.
Tried using Serialization but appears some of the transactions overlap and generate errors. Tried disabling the triggers but run into other problems.
At this point I am tempted to run each statement manually
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.
Next to the drop down list there is a text box where user can type the some text (translation).
User can add several description
On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)
that's work fine for some language => FR, EN and so on
But for bulgarian (bulgare) and greece there are some problem...
Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?
The value encoded by the user is => Ένωσης. Η διάταξη αυτή
but the result after t sql xpath is => ???s??. ? d??ta?? a?t?
Here is it my sql code where you can find my temporary table and my xml file and my xpath query
declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500)) declare @Translations XML
set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'
-- 2) fill the temporary table with information from the xml file
INSERT INTO @tblTranslation(idDocID, languageID, value) SELECT 1 , Convert(nvarchar(max), i.query('eleKey/text()')) as colKey , Convert(nvarchar(max), i.query('eleValue/text()')) as colValue -- FROM @translations.nodes('/Items/Item') as x(i) SELECT * FROM @tblTranslation
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode, EngagementCode, EngagementDescription
)
SELECT (SELECT dbo.tarCustomer.CustID FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT (SELECT dbo.tPA00175.chrJobNumber FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
Hello!I have a developer that is playing around with some SQL statementsusing VB.NET. He has a test table in a SQL 2000 database, and he hasabout 2000 generated INSERT statements.When the 2000 INSERT statements are run in SQL query analyzer, all2000 rows are added to the table. When he tries to send the 2000statements to SQL Server through his app., a random number ofstatements do not get executed. But, SQL Profiler shows that each ofthe 2000 statements are getting sent to the server.I suggested that he add a "GO" statement at the end of the INSERTblock, but the statement fails when that is sent to the server.I know that this is not the ideal manner to insert bulk data to thesystem, but now we are all just curious as to why SQL server doesn'texecute each individual INSERT.Any thoughts?
